Administrator's Help

Supporting Large Databases within EfficiencySpring

The EfficiencySpring platform has been designed to support a range of database sizes, from databases that contain only a few thousand records to databases that contain millions of records. There are extra considerations, however, when planning for, designing, and implementing EfficiencySpring installations that contain millions of records. This guide provides information on how to create an EfficiencySpring installation that can scale upward.

Indexing the Tracking Tables

EfficiencySpring contains a couple central tables that track online activity. These tables, particularly Obj_History, can become large quickly if the EfficiencySpring installation contains Data Manager sections that map to large database tables. Because these tables are also queried frequently, it is recommended that indexes be created for them. The list below contains these centralized tables, and the recommended columns to create non-clustered indexes for:

• Obj_History
  • Obj_Type
  • Obj_Id
  • Action
  • History_Date
  • User_Name
• Obj_Link
  • From_Section_Id
  • From_Obj_Id
  • To_Section_Id
  • To_Obj_Id
  • Relationship_Id
• Topic_Links
  • Section_Id
  • Object_Id
  • Topic_Id
  • Link_Weight
  • Link_Date
  • Active
• Record_Owner
  • Section_Id
  • Group_Id
  • User_Id
  • Keyfld_Value
• sec_log (if you are expecting massive amounts of logins)
  • User_Name
  • Status
  • Sec_Log_Date

For Large Database Tables, Know Which Columns to Index

If you are creating a Data Manager section that maps to a large database table, creating non-clustered indexes for certain columns within that database table can significantly boost performance, particularly in the area of search and reporting. A column should be considered for indexing if:

• It is configured as a search or quicksearch field
• It is listed on the results page as a sortable field
• It is configured as a duplicate check field
• It is part of a large lookup table and configured as a display, value, or order by field for a structure element
• It is a child field of a section that maps to another Data Manager section

Carefully Consider Which Sections and Fields Should be Configured For QuickSearch

Unlike conducting specific field searches, EfficiencySpring's QuickSearch functionality will automatically search every section in a top section's configured section tree that has Include in QuickSearch turned on. For each of those sections, it will include every field marked as a Quick Search field. For section tree structures that have millions of records (particularly at each data level), this can result in very expensive queries. It is worth planning carefully which tables and section tree branches should be enabled for QuickSearch. Scaling back the number of fields and tree levels can significantly improve performance.

Understand and Plan for the Price of Indexing

For each indexed column, SQL Server creates an ascending or descending index of the column that greatly helps with lookup speed. This translates into significant performance increases for the WHERE and ORDER BY components of queries.

Indexes however require storage space and maintenance. It is not uncommon for a database table with 40 MB of data to have an index storage size of 80+ MB. Use of indexes can increase the storage space requirements of a database by 300%+. This should be taken into account when planning hardware purchases.

Every time an INSERT or UPDATE is conducted against a database table, the performance of those operations is affected due to the indexes needing to reorganize due to the new data. For databases that have modest amounts of data additions and updates, the performance gains for read-only queries make up for the perfomance loss of INSERTs and UPDATEs. For database tables that are rapidly added to and updated, however, it is often a good idea to limit the search features for those tables, and later export the data from them to a "Data Mart" table structure, that will not be rapidly accessed for write purposes and are thus good candidates for Indexing.

Indexes also should be rebuilt often to insure they remain effective as the data they are based on changes. The SQL Server maintenance plans that manage the upkeep of your databases should incorporate a step for rebuilding indexes; this step is often bundled into maintenance plans that handle database backups.

Reduce the Joins In Your Database Design

It is often preferred among database designers to structure databases in a very normalized format, where any field or set of fields that could be viewed in the context of a subrecord be placed in a relational subtable. This is often the case with subtables that centrally store addresses, email addresses, and phone numbers.

For databases that contain millions of records however, compromises should be considered for reducing the number of real-time JOINs that queries need to perform. One compromise is for a primary database table itself to store a singular "primary" subrecord (which is likely to be displayed and queried often), where a subtable could be utilized for storing alternate subrecords. A variant of this compromise would be to also have a corresponding subrecord with data that matches the embedded subrecord in the primary database table.

JOINs are expensive when done between tables that contain millions of records and should be reduced in number when possible.

Use the Maximum Search Return Size and Sort Limit settings to limit ORDER BY Liability

The Maximum Search Return Size section setting, located in the Database tab within the Section Admin tool, can be utilized to impose a hard limit on the amount of records SQL Server will be allowed to apply an ORDER BY to, at least with regard to queries run from the Data Manager Search interfaces. This setting can be utilized to prevent a query that would return millions of records from being run. Maximum Search Return Size is particularly useful for sections that have lookup fields displayed on a section's results screen. When these fields are used for sorting, they can be a performance liability, as they are not natively indexable. Limiting the amount of records the lookup field would need to apply an ORDER BY for can prevent an overly expensive query from being run.

The Sort Limit structure element setting, located in the Database tab within the Section Structure add/edit/view screens, allows you to specify the maximum search result size allowed for the structure element to be considered sortable. For result sizes that exceed the maximum amount, the structure element will not appear as a sortable field on the results screens.

Performance Test Early On

Before implementing reports and writing custom code that integrates into your database, it is wise to conduct a performance test on the database utilizing hardware comparable to what the Production environment will provide, with a # of records comparable to what will be stored in the Production database. Catching performance issues early on can greatly reduce the need to redo reports, configurations, and customizations later on.