Hello Svein Thanks for your reply. That made a big difference, down from 10 minutes to 0.5 seconds :-)
c.Contact_ID is the PK for the contacts table, so I assume that's automatically indexed. c.Company_ID also has an index. Many thanks David On Thu, Jul 2, 2015 at 9:57 PM, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [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 > > >
