On Thu, Jul 2, 2015 at 9:57 PM, Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

>Could someone give me some pointers to improve the speed of this SQL please.  It's taking 

>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)))

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:

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 

      m.TIMESTAMP_ADDED < '2015.06.30'  AND
      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