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
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
have you runned the Database Tunning Advisor?
Please post the showplan.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.