index question 1

[dswitzer]

SQL Server 2000.
I have a table ~8 million records. I have a clustered index on the two fields that I use for querying parameters (idNum,qtrdt) -- and the queries are nice and fast.

I have a maintenance process where I need to delete all records in the table with qtrdt='yyyy-mm-dd'.

My question is -- the delete seems to be slow -- would indexing just on qtrdt help that speed? Or is that redundant based on my clustered index?

Thanks.

 

[MDXer]

What is the column order of your index? Also have you used the execution plan to see if it is doing an index seek or scan? My guess is a scan.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dswitzer]

I did not specify a sort order for the clustered index (I assumed it was asc by default).

When I ran the delete query - the events:
Clustered Index Scan (86%).
Top (selecting first few rows based on sort order -- 5%)
Clustered Index delete (9%)
Delete (0%)

I need to read up on indexing - since I had no idea order was important or what the difference between a scan and a seek are - clearly, I have some more learning to do. In the meantime, if you can point me in the right direction or I can provide more info -- please let me know.

Thanks.

 

[MDXer]

I was reffering to the Column order in the Index, as in idNum,qtrdt or qtrdt,idNum. This will have a major impact on performance and weather you are doing index seeks or index scans.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dswitzer]

I understand.

I just switched the clustered index order from (idNum,qtrdt) to (qtrdt,idNum) and the execution plan was:
Clustered Index delete (100%)
Delete (0%)

Seemed to be way faster -- saved that table scan!

Thanks.