Solved

Speed up delete with SQL Server

Posted on 2002-05-28
8
1,874 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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