General Tips to improve custom list performance in Sage CRM

2 minute read time.

This article has been prompted by recent enquiries about how the performance of specific list screens in a customer's system can be improved.

Custom extensions of the interface can be made using either the .NET or Classic ASP APIs.

These are some general tips to improve the performance of lists in a customized implementation of Sage CRM.

If you are experiencing errors shown in the logs as a database timeout then you can adjust the timeout settings for Sage CRM. You can set the timeout for the database connection in Sage CRM's database configuration. Increasing the timeout can give the database more time to respond.

See the setting "Query timeout (sec)" - The maximum amount of time that a query is allowed to run on the database server before a timeout error is displayed in Sage CRM.

You will need to check the general performance of your database server. You should ensure it has sufficient resources (CPU, memory, and disk) to handle the workload. Consider optimizing the database server's configuration for your specific needs.

Ensure that your database queries are as efficient as possible. Use appropriate indexes, limit the number of records fetched, and avoid complex joins or subqueries that could slow down the database. It is important to avoid using "SELECT *" in your view. You should explicitly specify the columns you need to prevent unnecessary data retrieval. This also makes your code more maintainable.

Also, ensure that your JOINs are efficient. Use INNER JOINs when you only need matching rows, LEFT JOINs when you need all rows from one table and matching rows from the other, and so on. The choice of JOIN type can affect performance.

Sage CRM has a feature that can be very helpful for custom entities called "Optimizing custom entity list for faster loading". By default, Sage CRM looks for data related to a custom entity list in all database columns. You can configure Sage CRM to look for data only in those database columns that are added to your custom entity list. As a result, your list will load faster.
For system entities, you can make sure the underlying view is used correctly.

If you are working with a partner or ISV-developed extension then this may be something that you need to talk to the ISV about - so that they can consider changing the views which are used as the data source for the screen.

See also this article "Creating a View/Edit Screen based on a View using the .NET API".  This article explains how the FindRecord can use a custom view.

And you may want to consider how pagination takes place. Instead of fetching all records at once, the Sage CRM system screen retrieves data in small chunks or pages (by default 10 records at a time), reducing the load on the database.

I hope these are useful.