Another Performance Boost For SugarCRM. Almost.
We continue our SugarCRM performance series ( codenamed project SucralosCRM) with an analysis of another suggestion from the SugarCRM official performance tweaks. If you read the last post in this series, you know that we were able to improve SugarCRM performance by 300%, simply by implementing one of their suggested configuration tweaks. We thought we would find another low hanging fruit by continuing to implement other suggestions.
Sugar has the following suggestion in their performance guide:
Collapse Unused Sub-Panels
- Greatly decreases processing required to render the page.
- We don’t retrieve information for hidden sub-panels. Users can expand them as they desire.
The important part of the suggestion is “we don’t retrieve information for hidden sub panels”. We did not try to implement this specific tweak, but it made us head to a new direction in our search for parameters influencing the performance of SugarCRM search. After all, scanning 100,000 contact records and fetching the names of each contact which matches the search query should take very little time. After all, we do use indices (the MySQL contact table has indices on both first name and last name, which are the fields we used for the search). There must be another problem. A problem with some additional information displayed in the result page.
Contacts search result page screenshot - multiple fields returned
By default, for the contact object, the search result page displays not only first and last names, but also the related account, email address and more. These additional fields are not part of the contact table. That means that sugar has to join together several tables to retrieve these values. And joins are expansive. It is likely that more time is spent on retrieving these additional pieces of information than on the actual search in the contacts table for records matching the search query. I will check these execution times later and provide you with exact figures.
Trying An Easy Fix
My next obvious step was to go to the SugarCRM studio and remove the extra fields from the search result page. SugarCRM provides amazing simplicity as far as user interface configuration is concerned. Removing those fields (and later restring them) took me no more than a minute. amazing!.
I thought I was on my way to another ‘improve SugarCRM performance by 300%’ post, but then something strange happened. I checked the search query performance with a result page displaying only a single field from the contacts table (Name), but the performance was identical to that of displaying multiple fields from multiple tables. I rechecked, rebooted the SugarCRM server, the client, everything. Nada. Same search performance. Something’s wrong here.
SugarCRM - MySQL Queries Examined
I configured the MySQL server log to output sql queries, then compared the sql queries used for both scenarios - a single field returned vs. multiple fields from multiple tables returned. And the queries were identical. Both had tons of join statements to the email_addresses and the contact tables.
I plan to report this as a bug to SugarCRM. I’m not even sure if this is a bug, but it’s the wrong behavior. I believe that if they fix this, the performance boost for searches will be incredible. I also believe the same behaviour (same sql query regardless of the fields that are actually displayed in the result page) is common to searches of all type of objects (accounts, leads etc.) in SugarCRM.
I will keep you posted on the status of the bug fix I open. Register to our RSS feed - like all good things in life, it’s fun and it’s free!
Comments
Leave a Reply
-
RSS Feed
