Optimizing the query

[sugarhoney]

For a table having 200K+ records, viewing the Contacts or Accounts page is slow. It seems that, sugarcrm will queried all 200K records just to display 20 records at a time. That's why it seems slow.

What file(s) do I look up to see where the query is taking place and optimize it?

[julian]

Hello sugarhoney,

I'd first turn on debug logging/slow query logging to capture the exact query being executed.

http://www.sugarcrm.com/wiki/index.p...ery_logging%3F

The queries will be dumped to sugarcrm.log. From there, you can begin diagnosing why the query is taking so long (by running something like EXPLAIN SELECT ... etc. etc.). As I understand it, the ListView queries are built generically in ./include/ListView/ListView.php.

[clint]

I would also suggest reviewing these Wiki articles on configuring your Sugar system for best performance.

Also, we do not query all 200k records. We put a LIMIT clause on the List View query that limits the return set of the query to be just the records you are viewing on the screen.

[sugarhoney]

Ok, I logged the queries when viewing the Contacts page. My "contacts" table has over 273K+ rows. Thus, it takes at least 5 minutes to load. I'd like to optimize the query, but where in ListView.php do I optimize it? According to my log file below, the query is bold takes awhile to load. Even though there's a "LIMIT 0, 21", it seems like it's querying all the rows i.e. "SELECT * FROM table"


04/08/08 12:34:52,320 [3708] FATAL SugarCRM - Slow Query (time:0.185225
SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name
from contacts
left join accounts_contacts a_c on a_c.contact_id = '' and a_c.deleted=0
left join accounts acc on a_c.account_id = acc.id and acc.deleted=0
left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id
where contacts.id = ''

04/08/08 13:03:11,337 [3708] FATAL SugarCRM - Slow Query (time:1698.366348
SELECT contacts.id , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, contacts.assigned_user_id FROM contacts 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 ORDER BY name ASC LIMIT 0,21

04/08/08 13:18:13,556 [3708] FATAL SugarCRM - Slow Query (time:901.72832
SELECT count(*) c FROM contacts 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

[clint]

That query should not take so long to return. It makes me wonder if your indexes are in place.

Go to Admin->Repair->Repair Database. This is will ensure that all of the indexes we ship for the schema are built.

Also, tell us more about the database server. How much RAM? Are there any other applications running on that machine? Is the web server running on the same machine? With such a large data set, I would absolutely recommend a stand-alone db server.

How is MySQL configured on your server. What version are you running? What are the values in your my.ini file?

From one of our engineers...

• Are they using MyISAM or InnoDB? If they're high concurrency, they'll be better off using InnoDB.
  
  • If they're using InnoDB on a separate server, set your innodb_buffer_pool_size to roughly 75% of your total system memory.
  
  
  • If they're not using a separate server, I'd say go for 50%, but you're going to be dealing with resource contention issues.
• query_cache_size is an important setting. 256M should be enough here to handle most data sets.
  
  • Also, set query_cache_limit to 2M.
  
  
  • You should have a good ratio for Qcache_hits vs Qcache_not_cached.

[sugarhoney]

Hello clint,

Yes, I did the repair table thing. How do I alter the query above? Or, is it hard to change? Are those query optimize as its most?

On my development localhost it takes 40 mins just to load the Accounts/Contacts page (no kidding!), but on my dedicated production webserver it's much quicker (30 seconds). I don't want to do most of my sugarcrm testing on the production server all the time, but I believe it could be more faster on my local machine if the query is optimized. Or, is it?

[clint]

Change the query how? It is the right query and I don't see any obvious way to optimize it.

Have you read the Performance Tweaks for Large Systems Wiki article I pointed you to? It lists several good configuration changes that reduce the number of queries used for rendering List Views and Detail Views. Be sure to try that path.

Tell us more about your development box where things are going so slow. How much RAM and how many processors are you running? What versions of web server, PHP, db server and Sugar are your running?

If you are running the web server and db server on the same machine, you should have at least 2GB of RAM otherwise MySQL will be swapping constantly to disk.

[sawan]

Hello,


O/S:Windows 2000
SugarCRM Community edition 5.0.0b(upgraded from 5.0.0a)
IIS 6
MySQL - 4.1.18-nt
MySQL client version: 5.0.27
PHP version 5.1.2

My Email module is currently very slow.
Can you please guide me what settings or updations should i do to increase speed or enhance processing.
I have started a thread on this:URL http://www.sugarcrm.com/forums/showt...180#post108180

I will explain my problem in short.

I have tried to install APC (php_apc.dll) and tried to register it using regsvr32 but an error came which saif it could not be registred.
How can i get APC for version 5.1.2? i have searched a lot but could not find it.may be some extension confusion.
Please guide me.