Speed up delete with SQL Server

I have to delete rows on a table that has about 200 000 rows WHERE usager = "XXXXX" .  There are 2 non-clustered indexes on this table but SQL Server doesn't always use, it depends on how many rows there is to delete.  The delete execution takes over 3 minutes!!!  How can I speed that up?
LVL 1
Flower11Asked:
Who is Participating?
 
NetminderConnect With a Mentor Commented:
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If you add/modify a clustered index on the usager column, you will probably get a better deletion time.
CHeers
0
 
kelfinkCommented:
If sql server is making the choice depending on the count, then it sounds like it's probably making good decisions based on the count.  

If this table is the parent of foreign keys, then remember that it must guarrantee that child records don't exist, or it would have to throw an error.

Could it be referring to huge tables?  For instance,
a "small" table might be the products in a store, where a larger table would be the orders in the store.  If you delete a product, sql server would check the orders table to make sure no orphan records would be left behind.  Make certain that any reference to the target table (in this case, product) in the child table (orders) requires a table scan.  

If your child table has column 'prod_id' referring to 'id' in the prod table, make sure 'prod_id' is indexed, or sql server must table-scan the child table for each record being deleted from the parent.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Flower11Author Commented:
Thanks angellll.

But a clustered index has to be unique, doesn't it?  There is many rows with the same "usager".
0
 
Flower11Author Commented:
Answer to kelfink.

The table is temporary, and there is no foreign key.

Flower11
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
The clustered index doesn't need to be on a single column, and doesnt event need to be unique at all...

CHeers
0
 
thegroupCommented:
If your table doesn't reference another one try building an index on your search field (usager)

Disabling indexes allows you to execute the delete very fast, then you reenable the index to rebuild it.

I don't know SQL Server very well, but in Interbase the sintaxis is:

alter index <index> inactive;
<do your stuff>
alter index <index> active;

Hope helps.
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
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.

All Courses

From novice to tech pro — start learning today.