Solved

Speed up delete with SQL Server

Posted on 2002-05-28
8
1,863 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…

762 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