By default, the Scientia Syllabus Plus ERDB is not really optimised for high performance. Most SQL indexes are missing from the database. Also, older ERDB versions require some fixes and additions to overcome bugs and missing data. Eveoh provides various T-SQL scripts to improve the ERDB. The latest version of these scripts is always available in a public Git repository, which can be found at https://github.com/eveoh/syllabus-erdb/. In the GitHub web interface, choose to view or download the raw version of the script for easy import to SQL Server Management Studio or
The indexing scripts should have no negative impact on other applications. Several indexes are added to improve read performance. Write performance will be lower, but usually the
ScheduledExtract.exe process is the only process to write to the ERDB.
Scheduled Extract settings
Please make sure the scheduled extract is running in transactional mode (
/T command-line switch). This should be the case for full and incremental extracts (even though Scientia documentation says incremental extracts run in a transaction by default, which is not true).
Please make sure the SQL Server database is running in read committed snapshot mode, by running the following command on the database:
This prevents blocking of reads during a full extract.
The 3.1 ERDB requires a fix to include the last activity instance of the last scheduled activity. Also, to support jointly taught activities and variant activities, the activity parent<>child information has to be included in the reporting database. To improve performance, an indexing script is available. These scripts can be found in the Git repository at https://github.com/eveoh/syllabus-erdb/tree/master/3.1.
Another issue with ERDB versions prior to version 3.2.0 is the refresh process. The ERDB will be emptied on each refresh and will not be available during the refresh. Eveoh has several merge scripts available to provide an always-online ERDB. Please contact our support department for more information on these scripts.
Performance of the ERDB 3.2.0 suffers from the use of several complicated views, and realtime joins with the
SD_TRANSACTION table. Especially the
V_ACTIVITY_DATETIME view is affected, since this view is now completely dynamically generated, using a T-SQL function and a join of 4 tables. We have a set of indexes available that improves ERDB read performance by 200 to 500 percent, but the performance is not (yet) at the level of earlier ERDB versions. The last version of the indexing script can be found at https://raw.github.com/eveoh/syllabus-erdb/master/3.2/erdb32_indexes.sql.
Execution of the script will cause the following warning:
Warning! The maximum key length is 900 bytes. The index 'group_name_grouptype' has maximum length of 1022 bytes. For some combinations of large values, the insert/update operation will fail.
This warning can be safely ignored, since the group type column has a defined length of 255 characters but will never contain more than 20 characters.
We are currently working on additional improvements to the ERDB 3.2.0 performance and will update this page when more information is available. In case you believe you are suffering from ERDB performance issues, please contact our support department for further investigation.
ERDB version 3.2.2 is a minor update of the 3.2.0 ERDB. The 3.2.2 indexing script equals the 3.2.0 script, only two table names have been changed. The script can be found at https://raw.github.com/eveoh/syllabus-erdb/master/3.2.2/erdb322_indexes.sql.
Next to this, there is a bugfix available to correct a problem with activities ending at midnight: https://raw.github.com/eveoh/syllabus-erdb/master/3.2.2/fix_endtime-before-starttime.sql.
Because of quite severe issues with incremental updates - updates may get lost if a timetabler writes them during a ScheduledExtract run - we do not recommend using ERDB version 3.2.2.
Please use the indexing script from version 3.2.2 for this ERDB version, which can be found at https://raw.github.com/eveoh/syllabus-erdb/master/3.2.2/erdb322_indexes.sql. No further changes are necessary.
Please use the indexing script from version 3.2.2 for this ERDB version, which can be found at https://raw.github.com/eveoh/syllabus-erdb/master/3.2.2/erdb322_indexes.sql. If you are experiencing issues with RDB updates after updating a usertext field of one of your departments, please apply the following script to your SDB (Syllabus source database): https://raw.githubusercontent.com/eveoh/syllabus-erdb/master/3.11/fix-departments-view.sql. This script reverts some (apparently unintended) changes to the
sdintegrator.SDVC_DEPARTMENT back to the 3.10 version.
If you are showing Web Room Booking timetables to your users, please index the appropriate column, containing the username, in the
ACTIVITY table. By default, this would be usertext2. An indexing script can be found here: https://raw.githubusercontent.com/eveoh/syllabus-erdb/master/3.2.2/erdb322_wrb_index.sql