Slow Queries

Got a problem with the Opacus SugarCRM Activities Sync? Please post it here
Post Reply
dalli
Posts: 11
Joined: Fri Mar 18, 2011 2:27 pm

Slow Queries

Post by dalli » Wed May 25, 2011 12:46 pm

Hello

we noticed that there is a mysql query causing a bunch of slow queries:

(SELECT
UNIX_TIMESTAMP(
date_modified)
AS ctag_number
FROM calls
WHERE (assigned_user_id = '<uuid-here>')
OR (id IN (SELECT call_id FROM calls_users WHERE user_id = '<uuid-here>')))
ORDER BY ctag_number DESC LIMIT 1;

opacus.jon
Posts: 342
Joined: Mon Jan 03, 2011 6:32 pm

Re: Slow Queries

Post by opacus.jon » Wed May 25, 2011 4:42 pm

Hi,

How slow are the queries? Do you have a large amount of calls/meetings/tasks in the system?

Thanks,

Jonathan

dalli
Posts: 11
Joined: Fri Mar 18, 2011 2:27 pm

Re: Slow Queries

Post by dalli » Thu May 26, 2011 6:53 am

Hi

query time is between 0.5 to 1sec

EXPLAIN (SELECT ...
1 PRIMARY calls ALL NULL NULL NULL NULL 29238 Using where; Using filesort
2 DEPENDENT SUBQUERY calls_users ref idx_usr_call_call,idx_usr_call_usr,idx_call_users idx_usr_call_usr 111 const 1 Using where

We have:
calls ~ 30,000
calls_users ~ 40000
(meetings, tasks ~2000)

Engine is MyISAM

Regards
Deniz

dalli
Posts: 11
Joined: Fri Mar 18, 2011 2:27 pm

Re: Slow Queries

Post by dalli » Tue Jul 12, 2011 8:55 am

Hi,

we still have that problem from time to time.

Are there any improvements planned?

Deniz

opacus.jon
Posts: 342
Joined: Mon Jan 03, 2011 6:32 pm

Re: Slow Queries

Post by opacus.jon » Tue Jul 12, 2011 4:49 pm

Hi Deniz,

We are trialling an optimisation to the query you mention and hope to release version 1.5.9 shortly. Thank you for your patience.

Jon

dalli
Posts: 11
Joined: Fri Mar 18, 2011 2:27 pm

Re: Slow Queries

Post by dalli » Wed Aug 24, 2011 3:09 pm

Hello,

has there been some improvement meanwhile?

Unfortunately slow queries on the calls table have serious impact on our business. Whenever there is a sync between the crm and the caldav client it is not possible to save / edit / ... calls for a while, i.e. our sales department cannot work properly. In the end we have to switch off the sync.

We might switch the engine to INNODB, but this is not what we absolutely want to do

Regards
Deniz

rhamnett
Posts: 1
Joined: Wed Aug 07, 2013 3:57 pm

Re: Slow Queries

Post by rhamnett » Wed Aug 07, 2013 4:02 pm

The queries are abysmal.

I have quickly rewritten them to make use of proper joins, it's much faster. I have also made it such that it does not get all records back over all time, just the last 6 months. It is rather ludicrous to populate the calendar back many years and is a big drain on resource.

I can't even attach the patch won't let me upload a .diff or .txt so here is a link http://pastebin.com/FEr00yPQ

Richard Hamnett

Martin
Posts: 14
Joined: Tue Jan 10, 2012 8:09 am

Re: Slow Queries

Post by Martin » Wed Feb 19, 2014 9:51 am

rhamnett wrote:The queries are abysmal.

I have quickly rewritten them to make use of proper joins, it's much faster. I have also made it such that it does not get all records back over all time, just the last 6 months. It is rather ludicrous to populate the calendar back many years and is a big drain on resource.

I can't even attach the patch won't let me upload a .diff or .txt so here is a link http://pastebin.com/FEr00yPQ

Richard Hamnett
hi Richard, do you happen to know if your contribution has been incorporated into one of the later releases?
Or is that reconstruction of the queries still necessary?

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest