Indexing Custom Tables in Sugar

As CRM implementation matures we often need to revise some of the architecture decisions made in the beginning of the project. While many pitfalls can be avoided though prior proper planning in the beginning, sometimes requirements change or your business scales at an unexpected rate.

The following is a sample scenario based on a real life example we ran into at Highland.

The Scenario

An external system pushes data into modules in the CRM. The modules are linked via a many-to-many relationship — one record can be related to many records in the other module, and vice versa. These can be related using an identification number generated by an external system on both modules. This means you can know which ones relate to one another programmatically and can easily update records by searching for that ID number. After years of use, performance started to decrease and the system took much longer to load each row.

Our task was to investigate the performance issues and implement a solution.

Initial Investigation Findings

Using the MySQL logging feature in SugarCRM (Administration->System Settings->Advanced), we were able to see that querying one of the modules was taking several seconds when the external system was applying the Filter API. Investigating further, we realized this module had approximately 1,600,000 records and the related module had around 42,000.

It was taking on average 45–60 seconds to create and link a record from the external system depending on the number of records needing linking.

Resolution Step 1: Reduce the number of requests

The first step was to reduce the number of requests from the external system by performing the linking within Sugar using a Logic Hook on each module. That way, when a record is created in either module the relationships would be added.

The actual code in the Logic Hook would depend on the module and relationship in question, so that isn’t included here. If you are interested in learning more about Logic Hooks here is the SugarCRM documentation.

We further improved performance by controlling when the code runs. Instead of running it on every save, we only ran it when specific changes happened to a record.

Once we made those changes, it took 40% less time to process each row.

Resolution Step 2: Add indexing

In Step ,1 we were able to reduce the number of requests from external system to one per row which had a significant performance improvement. However, more logic was running on the saving of a new record so record creation was slower than it was previously. Again, referring back to the MySQL logging, we were still seeing that querying the large table was slow.

Since we were querying specific fields for the linking, we decided to add indexes. Indexes can speed up database SELECT queries by making the sorting much more efficient. This is particularly true for tables with large amounts of data. Indexes can be on 1 or more columns per table but should be used wisely because they add the overall storage the database uses. Additionally, INSERT and UPDATE queries can take more time. In our case, the performance improvements of repeatedly querying the same column in our large table far outweighed the performance setbacks which were not noticeable in our example.

The SugarCRM documentation has good instructions on how to do this.

Below is an example for a field called ‘externalidc’ on the Contacts module:

Please note the database query at the end that is produced after running a Repair and Rebuild. The query is trying to add an index on the contacts table but as a custom field the ‘externalidc’ field is on the ‘contacts_cstm’ table.

If we tried to run this query we would get an error:

‘Key column ‘externalidc’ doesn’t exist in table’

This leaves us at a bit of a loss in using the Extension Framework. However, we can utilize built in functionality within the DBManager class executed in an install package.

One of the primary methods for making code level changes for SugarCRM (and the only method for On-Demand customers) is through Module Loader. When creating the package we can include a scripts directory with a file called post_install.php to execute code at the time of install.

The example above will create the new index on the contacts_cstm table. Also included but commented out are samples of removing the index and creating a unique index for the field in question.

After re-linking the existing records we saw significant performance improvements in the integration. After step 1 above we were averaging 20–30 seconds per row on the integration processing but after adding the indexing this time went down to an average of 3-5 seconds per row which includes the networking delay between the two systems.

This case study highlights how solutions are rarely single-threaded. To get the maximum benefit, we had to look at the performance within Sugar and reduce the number of requests made through the integration. This is a great example of how taking a global view of a given issue can yield the best results.