Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

SQL Index

I have the following query which runs against a table with 300,000,000 records. It takes about 2 hours to run which is much too long. The DTH_CallRecordMaster table does already have a non-unique, non-clustered index on CustomerID and the execution plan shows it it is the index seek on the CustomerID index is 95% of the work.

What are my options to improve performance of this query?


select CustomerID, count(CustomerID), sum(CallCost) from DTH_CallRecordMaster (nolock)
		where CallType in (5, 7, 10, 11, 12, 13, 22, 23) and cast(StartTime as date) between '2011-08-01' and '2011-08-31' 
		  and CustomerID in (select CustomerID from DTH_CustomerMaster (nolock) where CycleID = 'MONTHLY-15')
			group by CustomerID

Open in new window

5 Solutions
x-menIT super heroCommented:
have you runned the Database Tunning Advisor?
Racim BOUDJAKDJICommented:
Please post the showplan.
you can write the above query like below.

select       A.CustomerID,
from       DTH_CallRecordMaster as A WITH(nolock)
Join      DTH_CustomerMaster as B On B.CustomerID  =  A.CustomerID
where       A.CallType in (5, 7, 10, 11, 12, 13, 22, 23)
and       cast(A.StartTime as date) between '2011-08-01' and '2011-08-31'
and       B.CycleID = 'MONTHLY-15'
group by A.CustomerID

and also create index with following columns calltype, starttime, CustomerId, CallCost for table A abd
Customerid,Cycleid for table B
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

dthansenAuthor Commented:
The lookup to the DTH_CustomerMaster for cycleID is 0% if the execution plan. I'm not worried about that.

Should I create a new index with those columns or simply add those columns to the existing index that is on CustomerID?

Anthony PerkinsCommented:
Make sure you have the following indexes and try is as below:
DTH_CallRecordMaster: CustomerID, CallType, StartTime
DTH_CustomerMaster: CustomerID, CycleID

If that does not make any difference, buy a faster server.
SELECT  r.CustomerID,
FROM    DTH_CallRecordMaster r (NOLOCK)
        INNER JOIN DTH_CustomerMaster c (NOLOCK) ON r.CustomerID = c.CustomerID
WHERE   r.CallType IN (5, 7, 10, 11, 12, 13, 22, 23)
        AND r.StartTime >= '2011-08-01'
        AND r.StartTime < '2011-08-31'
        AND c.CycleID = 'MONTHLY-15'
GROUP BY r.CustomerID

Open in new window

dthansenAuthor Commented:
1. The CustomerMaster has only 40 rows while the CallRecordMaster has 300,000,000. Isn't joining those tables more expensive than a simple IN against CustomerMaster?

2. Should I add CallType, StartTime to the existing index on CustomerID or create a new index.

3. Why not have 'CallCost' in the index as an include so it is a covering index?

4. Why did you choose to replace the 'between' with a >= and <. Is that more efficient?

Anthony PerkinsCommented:
1. What does the Execution Plan tell you in each case?  Why don't you test it?  Use the one that best fits your requirements.
2. I would define them as Keys in a single index.  But test it out.  Inspect the Execution Plan.
3. Yes.
4. I suspect cast(A.StartTime as date) between '2011-08-01' and '2011-08-31' is not a SARGable function, so I replaced it with something that would take advantage on an index on StartTime.   If you want to leave it then don't bother with adding an index on StartTime, it may not use it.  Hence lousy performance.  Again test and see for yourself.
Make sure that you have proper indexes as keys
CustomerID, CallType,StartTime Include(CallCost)  on DTH_CallRecordMaster
CustomerID, CycleID  On DTH_CustomerMaster

Also, check your indexes are properly defragmented and the statistics are updated.

1. I assume, joining between smaller and larger table use Hash Join, so both IN and Inner join will use Hash Join.
2. CustomerID, CycleID  On DTH_CustomerMaster
3. Including CallCost makes it covered index and remove lookup.
4. SQL Server usually convert between to >= and <=, this will not make any difference. As @Acperkins suggested these arguments may be non sargable so, check your data type of StartTime matches both column and variable.
Anthony PerkinsCommented:
>>SQL Server usually convert between to >= and <=, this will not make any difference.<<
Which reminds me that the condition I posted is not correct:
Instead of:
cast(StartTime as date) between '2011-08-01' and '2011-08-31'
AND r.StartTime >= '2011-08-01'
AND r.StartTime < '2011-09-01'

That is assuming that the following is not SARGable:
cast(StartTime as date) between '2011-08-01' and '2011-08-31'


Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now