Solved

Speed up delete with SQL Server

Posted on 2002-05-28
8
1,857 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:Flower11
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7041084
If you add/modify a clustered index on the usager column, you will probably get a better deletion time.
CHeers
0
 
LVL 5

Expert Comment

by:kelfink
ID: 7042800
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
 
LVL 1

Author Comment

by:Flower11
ID: 7044546
Thanks angellll.

But a clustered index has to be unique, doesn't it?  There is many rows with the same "usager".
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 1

Author Comment

by:Flower11
ID: 7044553
Answer to kelfink.

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

Flower11
0
 
LVL 143

Expert Comment

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

CHeers
0
 
LVL 1

Expert Comment

by:thegroup
ID: 7044674
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7265834

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
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7296595
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question