| Subject | Re: [firebird-support] Why is SQL slow? |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2015-07-02T20:57:49Z |
>Could someone give me some pointers to improve the speed of this SQL please. It's taking
Well, David, I wonder why the optimizer chooses to use indexes for both company_id and timestamp_added, I'd rather choose the timestamp_added index, then use the index for the first field in each of your JOINs. If you have an index for c.CONTACT_ID (hopefully, this is considerably more selective than C.COMPANY_ID and only a small fraction of their contacts were contacted in June), then I'd recommend you to try:>about 10 minutes to execute on a fast PC using Firebird v1.56.
>
>I am counting the number of messages sent by different methods, for a particular company.>A company has a number of contacts and they receive a number of calls during the month.>These calls can generate a number of messages to be sent out.
>
>This has been working fine for many years, but it appears to execute VERY slowly when a>Company has many contacts ~800
>select m.MSG_TYPE, count (distinct(m.Message_ID)) as MsgCount
>from MESSAGES m
>join MESSAGE_LINK ml
> on ml.message_id = m.message_id
>join CALLS a
> on a.Call_id = ml.call_id
>join CONTACTS c
> on c.CONTACT_ID = a.CONTACT_ID
>where c.company_id=240 AND
> (m.TIMESTAMP_ADDED >= '2015.06.01' AND m.TIMESTAMP_ADDED < '2015.06.30' ) AND
> m.TX_Code='O' AND
>group by m.MSG_TYPE
>Here's the plan:
>PLAN SORT (JOIN (C INDEX (I_CONTACTS_A),M INDEX (MESSAGES_TIMESTAMP_ADDED_A),>ML INDEX (MESSAGE_LINK_MESSAGE_ID_A),A INDEX (PK_CALLS)))
select m.MSG_TYPE, count (distinct(m.Message_ID)) as MsgCount
from MESSAGES m
join MESSAGE_LINK ml
on ml.message_id = m.message_id
join CALLS a
on a.Call_id = ml.call_id
join CONTACTS c
on c.CONTACT_ID = a.CONTACT_ID
where c.company_id+0=240 AND
m.TIMESTAMP_ADDED between '2015.06.01' and '2015.06.30' AND
from MESSAGES m
join MESSAGE_LINK ml
on ml.message_id = m.message_id
join CALLS a
on a.Call_id = ml.call_id
join CONTACTS c
on c.CONTACT_ID = a.CONTACT_ID
where c.company_id+0=240 AND
m.TIMESTAMP_ADDED between '2015.06.01' and '2015.06.30' AND
m.TIMESTAMP_ADDED < '2015.06.30' AND
m.TX_Code='O' AND
group by m.MSG_TYPE
m.TX_Code='O' AND
group by m.MSG_TYPE
Particularly note that I added +0, that is a trick to force the optimizer choose a different plan, hopefully the correct one. The addition of BETWEEN probably doesn't matter at all - it's just a long time since
I used 1.5 and don't remember whether it understood that the combination of '>=' and '<' is similar to BETWEEN or if the index is only used for '>=' and then everything that's '>=' are compared to '<' without using the index.
Please report back whether or not +0 improves the performance in your case,
Set