?
Solved

Speed up delete with SQL Server

Posted on 2002-05-28
8
Medium Priority
?
1,881 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 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