Increasing Digital-Clay Performance

• Limit the maximum results in lists/analysis as well as in drop-downs. See Limiting Results for details.

• Do not add unnecessary Linked Fields. These add joins that may slow down queries, especially in the case of MDB databases. Use Lookup Lists or Text fields instead of Linked Fields whenever possible.

• Many-to-many fields may cause heavy performance losses in some cases due to the fact that they add extra joins with intermediate tables, and require several additional queries to update and retrieve values. Only use a many-to-many field when absolutely necessary. Use many-to-one fields (linked fields in the linked table) instead of many-to-many fields when appropriate.

• Do NOT use many-to-many fields for thousands of values. This can cause the system to spend sometimes up to a minute on each record. If you intend to link more than a couple of hundred records, use an intermediate Custom Table instead, and display the values in a list query.

• Delete all unused fields. When updating or transferring records, these fields are adding unnecessary time, space and bandwidth.

• Add Indexes to fields that are often used in filters or for sorting. The most important fields in this case are the ones used as filters in Automation rules. Indexing is even more important in tables with thousands, or millions of records. Indexes must be added through the 'Custom Fields' tool.

• See the Maximum Threads Setting.

• Use the System Statistics tool to identify peak usage, users that abuse the system, and other possible causes of slowdowns.

• In Automation, do not leave the 'Add verbose information to log' setting on (ClayCentral Settings/Automation). This slows down Automation handling significantly and may add thousands of records to the database, and should therefore only be used temporarily for debugging problems.

• Do not over-use the Automation User Generated Events. Keep in mind that every rule creates another ClayCentral query every time the appropriate field is changed or a form is opened, so use this feature judiciously.

• For Item Add/Change/Delete events, always add as many 'Changed Field Requirements' and 'Filters after Changes' as possible. These can help skip actions quickly without running database queries. Exceptions to this rule are:
  • When a filter is based on a field in a linked table, or makes use of a custom function, in which case the filters will run an additional query before running the automation action. A simple filter is still usually preferable to an unneeded update action however, which usually requires several database queries.
  • In the case of 'User-Generated', or 'Before Change', or 'After Delete' events, the filter must run on a temporary record which means several additional queries must be run against the database in order to check the filters. In these cases, if the filter is in a linked table, or uses a custom function, it is preferable to not use the filter if possible.
  • 'Filters Before Changes' has a similar performance issue as the above in the case of normal Item Add/Change events: Only use 'Filters Before Changes' when absolutely required.

• See Bulk Actions for a much faster way to change massive amounts of records (with caveats).

• When you must change several fields in a single record, it is recommended to use the 'Call Other Handlers/Group Update Fields' action. This sets multiple field values before saving the record, instead of saving the record for each Set Field action, thus reducing a lot of overhead.

• In Set Field Query/Set Field Linked Query/Set Field Analysis actions, keep in mind that these actions run inner nested queries and these inner queries must be kept as simple as possible for performance. Reduce the amount of columns and sorting columns, try not to add filters on many-to-many links, limit the amount of results to a single record whenever possible, and try to map the filters to simpler field values instead of linked fields or many-to-many linked fields whenever possible.

• Inactivate or delete all Automation Event Handlers that are no longer required. Leftover automation rules from experiments or old projects may be slowing down the system unnecessarily.

Queries and layouts that are part of a user's profile may sometimes be a critical factor when it comes to performance, for the simple reason that the more queries and requests ClayCentral gets, and the more complex and heavy these requests, the slower ClayCentral will respond to other requests, thus making a system appear sluggish intermittently or during peak usage.

• Login Performance: Disable offline use in ClayStations if it's not needed (Login/Configure). This disables synchronization and therefore greatly speeds up the startup of every Digital-Clay client.

• Login Performance: Minimize the homepage dashboards on all clients - on a system with many users where they all login at the same time in the morning, this could result in hundreds of concurrent queries:
  1. If they don't need a homepage, don't load it at startup (ClayStation Options) or empty the default dashboard in ClayStudio.
  2. Only add homepage items they need to look at every day. Anything else should go into the Toolbar (you can add multiple dashboards to the Toolbar)

• Login Performance: Unchecking the 'Cache All Items on Startup' setting can reduce the time it takes to login, but the first time the user loads a list of values in a form, or show filter values, will take longer.

• Minimize the amount of columns in lists/analysis tables. Trim the columns before adding a list to a Toolbar. By simply removing columns you can speed up a list query by 500% or more.

• Try to avoid using many-to-one or many-to-many links in queries as much as possible. This includes filters. For example In many cases, these can cause a DISTINCT to be added to the SQL query, which can make the query run 20 times slower.

• Add as many filters as possible to lists/analysis/dashboards to limit results. For example, don't just add a full Customer list to the Toolbar; If possible, add a dynamic filter on the Customer name so they can search for the specific Customer they need.

• The same goes for the Advanced Search feature that opens a list of items for finding a specific record. By default this feature pops-up the filters dialog to minimize unnecessary large queries. Do not cancel the filters screen or switch this feature off if possible.

• Do not add fields of type Custom Function to form tabs unless they are absolutely necessary. Each Custom Function field that is loaded in a tab means an extra query sent to Central. Note that this only applies to data forms; in lists, custom functions do not result in additional queries.

• Minimize the amount of fields per tab in data form layouts. Whenever possible:
  1. Split up the fields over several tabs
  2. Keep the first tab as small as possible. Adding 50-100 fields on the first tab slows down the loading of a form every single time by up to 5 seconds.

• For Outlook slow-down issues, see Outlook Performance Issues.

• Do not set Auto-Expand on in Charts and Gantts unless necessary and the chart is filtered. In a large Chart this can result in hundreds of queries.

• For imports using the Integrator, if no duplicate checking and handling is necessary (i.e. no skipping or merging of records) then for each table being imported:
  1. Select “Add - items that already exist in Digital-Clay”
  2. Uncheck “Skip duplicates in source”

• For imports, when merging or skipping duplicate records, add Indexes to the fields defined as 'Identifying Fields' before you run a large import, or an import on large tables.

• See Bulk Actions for a much faster way to import (add) massive amounts of records.

• SQL Server vs MDB: MDB is the default database for Central. Advantages when upgrading to SQL Server:
  • Faster read-only queries - this includes lists/analysis/reports/etc.
  • In cases where there are 100,000s of records, or 10,000s of records with complex joins and over 15 linked fields, MDB may freeze up on some queries and take several minutes to complete them.
  • Much better handling of concurrent queries - a system with over 20 users, or with frequent concurrent changes to the database should move to SQL Server.
  • More advanced Digital-Clay features are available with SQL Server. Examples: Inverse queries, Bulk Actions with nested Set Field Query actions, custom functions with the 'Replace' function, etc.
  • SQL Server can be tweaked depending on the usage (large results vs many queries, the same repetitive reports means caching can be adjusted, etc).
    
    Disadvantages:
    
  • Slower write speed. This will only be felt during bulk edits or imports though.
  • Unless the limited Express/Desktop version of SQL Server is used: Price

• SQL Server 2005 is faster than 2000 with some typical Clay queries; 2008 is only marginally faster than 2005.

• The free Express editions are not limited in terms of users and usage as far as Digital-Clay is concerned, but SQL Server Standard/Enterprise/Workgroup editions can be faster than the free Express editions, due to limitations in the Express edition involving maximum RAM usage (and therefore less efficient caching in some cases), single vs. multiple CPU usage, and maximum database size restrictions. As of SQL 2008 R2 and up, databases are limited to 10GB per database, it uses a maximum of 1 physical CPU or four cores, and RAM is limited to only 1GB (but this is only for the buffer/cache - it may use more RAM for other reasons). In addition, the Express editions do not come with the SQL Agent for automating tasks, and Enterprise editions also support fail-over configurations. In summary: When queries are slow either due to too many users and SQL Server not using all available CPUs, or if queries are slow on tables with large amounts of data, then upgrading to Standard edition should help.

• Compacting or re-indexing a database can greatly improve performance, especially with large databases that have been in use for a year or more without maintenance. If you get timeouts with complex queries, this can also solve this issue. See Database Maintenance for details.

• To greatly help database writes, store the (SQL Server) database log file and data file on two different physical hard drives. Since database writes involve both the log file and data file using two different mechanisms (data file writing is buffered), by making sure that these files are on separate hard drives, one ensures that the writes don't interfere with each other when they use the same bottleneck (the physical drive).

• Use the SQL Server 'Simple' Recovery Model whenever possible rather than the more expensive other models.

• Obviously, the faster the hardware on the Central machine, the better:
  • A dedicated machine for ClayCentral is strongly recommended when there are many concurrent users (20+) or many heavy requests.
  • Due to the large amounts of data being processed, the bus and memory speed are critical and may make a very significant difference in performance, even more so than with faster CPUs.
  • Note that faster RAM is sometimes much better than more RAM depending on the bus speed. More RAM helps SQL Server cache more data however so this is also important with huge databases.

• Memory and caching can only help so much. With large databases, many attachments, and for write-intensive systems, the hard drive becomes the primary bottleneck. Faster hard drives (SSD) or even RAID systems are important in this case. For performance:
  • RAID0 is recommended for systems that don't need mission-critical fault-tolerance.
  • For fault-tolerance, multiple RAID1 pairs with the SQL Server database split over multiple volumes is recommended for write-intensive systems and the SQL Server tempdb.
  • System databases and Clay attachments should be stored on RAID10 or RAID5 arrays.

• Make sure write-caching is enabled on hard drives and RAID controllers for very significant differences in performance. This makes it more dangerous in case of failures and crashes, but a redundant power-failover system can solve this and the performance boost is extreme.

• Digital-Clay Clients can also run heavy tasks such as loading heavy forms/tabs/dashboards, or displaying complex analysis results. Better client hardware can provide a significant performance boost for these tasks.

• On fast networks (i.e. fast network cards, cables and switches) with many concurrent users (50+), higher performance can often be achieved by separating ClayCentral and SQL Server onto two machines. The load is usually shared between ClayCentral and SQL Server with a ratio of roughly 40/60 so by giving ClayCentral its own CPU and memory resources, more requests can be handled concurrently/faster.

• Similarly (see previous Database section), with SQL Server, store the database log and data files on separate physical hard drives.

• See Failover & Load Balancing Clusters.