Optimizing SQL queries for listing

[bernd.neukamp]

Hi all,

we have got a serious performance problem when accessing the main "Contacts" page without any search applied on it. We found out that the SQL query submitted to the MySQL-server is far away from being optimized. Beyond is the original query string from SugarCRM:

SELECT count(*) c FROM contacts
LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c
LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0
LEFT JOIN users jt1 ON jt1.id= contacts.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where contacts.deleted=0

Executing this query takes about 2 minutes although the overall performance of Sugar is good! According to the MySQL Reference Manual the syntax could be optimized by adding a simple "contacts_cstm.id_c IS NOT NULL" to the mentioned query. So if it is possible to change the according query creation process without any side effects there could be a great performance enhancement - at least for people like us who have quite a large customization table!

Can anybody help us finding out if there are any side effects when changing the above statement. Where do we have to start? Is there some kind of help for this query creation process which we can use as a basis?

Any help would be very much appreciated! Thanks in advance!

Bernd Neukamp

[elRey]

I may be wrong, but I don't believe that addition will help.
You will always have a matching record in the cstm tbl for every contact
in the main tbl. So adding IS NOT NULL will not decrease the work the db has to do or the number of records it will return.

Again, I may be wrong. I'm no expert by any stretch of the imagination.

[kpit]

any time you do a count(*) you will force a table scan of the records that match the where clause. The count(*) is your time consumer and gets worse with high record counts.

[bernd.neukamp]

Hi elRey,

if you believe it or not: there is a significant performance improvement based on adding "AND contacts_cstm.id_c IS NOT NULL" to the posted query! To give you an idea the SugarCRM query takes 2 minutes, as I wrote, whereas the modified query takes around 5 seconds to perform! (Which would be an acceptable time to open the "Contacts" page without any search string - unlike the 2 minute period with the standard query!)

So if you or anybody could help me imlement the addition this could be a great help for everybody using SugarCRM with a larger number of customized fields.

Best regards,
Bernd

[bernd.neukamp]

Hi umeco.

Well, thats why I tried to search for a solution - which might be the mentinoned query addition. My problem is that I am not that familiar with Sugar's query creation process and I am hoping that someone in this forum can help me finding out whether it is possible to modify some files to create a modified SQL query...

[elRey]

I have ver 4.0 so this might not match your's...

search for function "create_list_count_query" in the Contact.php file. Append you statement there.

Rey

edit ver 4.5 => Same function name, but in the SugarBean.php file in the data dir.

[kpit]

You should file a bug on this in the support area with SugarCRM. They will take a look at this as this is a core change to the SugarBean and potentially all beans subclassed. This is not a small change as it invoves every potential module.

[elRey]

edit ver 4.5 => Same function name, but in the SugarBean.php file in the data dir.

maybe something like:

 //
 
$optimizedSql = implode('WHERE '.$module->tbl_name.'_cstm.id_c IS NOT NULL AND ', explode('WHERE', $query));
 
// 
  

above wouldn't work if there are subqueries..... just an example

[bernd.neukamp]

Hi umeco,

thanks for the suggestion. I filed a bug with the description of the problem and a link to this thread. Hopefully this will get into one of the next releases!

[bernd.neukamp]

Hi elRey,

thank you very much for your hint! Unfortunatelly I am far away from being an PHP-crack. I tried to modify the function as you suggested and in some other ways but to no avail. Trying this there is no performance improvement - trying that the modification leads to a never ending query....hopefully the bug I filed will result in an improvement very soon.

If you have any idea what might help to push the performance I would be very thankful!

Best regards,
Bernd