• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • 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 BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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.
AnujSQL Server DBACommented:
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'

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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