Link to home
Start Free TrialLog in
Avatar of dthansen
dthansen

asked on

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?

Thanks


      
 
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

Avatar of x-men
x-men
Flag of Portugal image

have you runned the Database Tunning Advisor?
Please post the showplan.
SOLUTION
Avatar of jagssidurala
jagssidurala
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dthansen
dthansen

ASKER

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?

Thanks.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?

Thanks.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial