Solved

Ultra-Slow DELETE

Posted on 2004-04-08
8
1,579 Views
Last Modified: 2012-06-27
Hi there,

I have a table (MyTable) with the following structure:

dataCol1 int,
dataCol2 int,
dataCol3 tinyint,
datacol4 float

dataCol1 and dataCol2 together are the primary key, no other indices are defined on the table.
dataCol1 is a foreign key to another table, dataCol2 is a foreign key to a different table.
There are approx 18000 rows in this table.
The database in total is a little under 1Gb, but SQL Server should be able to cope with that OK.  There were also no active connections to the DB when I ran the command.

I am running the command DELETE FROM MyTable WHERE dataCol2 = x.  In my case, there should never really be more than 70 being deleted at any one time.  

When there are 70 rows to be deleted, this command (run in Query Analyzer) takes approx. 43 seconds to complete.  That's almost 0.6 seconds per row!!  How can this be, and how can I improve the performance so that it works in a (much) more reasonable time scale?

Thanks,

Malcie.  
0
Comment
Question by:malcolmbegg
8 Comments
 
LVL 8

Expert Comment

by:william_jwd
ID: 10784562
You can improve the query performance by indexing the column in the where clause (dataCol2).  I dont think that there  is any problem with your query...
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 10784591
when did you last time did indexes maintenances like reindex (see DBCC INDEXDEFRAG or\and DBCC DBREINDEX); update statistics (see sp_updatestats)?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 10784601
BTW: could be not so bad to create nonclustered index on column dataCol2
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10787700
Do you have any triggers on the table?
Are there any referential integrity constraints to other tables?
Is your DB reasonably defragmented?

mono
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10787932
What does the query plan show?
Have you run this with profiler on to see what's really happening?

How fast would a select with the same criteria be?
0
 
LVL 1

Author Comment

by:malcolmbegg
ID: 10789769
Thank you all for your help so far.

william_jwd: I tried setting up a non-clustered index on dataCol2 - this had no real effect.

monosodiumg: There are no triggers defined on the table.  The foreign key relationships I mentioned previously are enforced as relationships within the DB.  How can I tell if the DB is defragmented?  If it isn't how do I sort it out?

ChrisFretwell: Query Execution Plan shows the following (in as much detail as I can squeeze in!):

DELETE (0%) <-- Assert (0%) <-- Nested Loops (0%) <-- Clustered Index Delete (70%)
                                                 ^
                                                  |
                                                  Row Count Spool (0%) <-- Index Scan (30%)

The Clustered Index Delete is taking up the majority of the time (obviously), but should it?  Does this plan look right?

A SELECT using the same criteria is pretty fast (almost instantaneous).

Malcie.
0
 
LVL 2

Accepted Solution

by:
LordSilk earned 250 total points
ID: 10790686
Hi,

Have you checked if there are other tables referencing your table? In other words,are there tables with foreign keys to your table?
If there are, check if the foreign keys are indexed. If they are not indexed, SQL server has to scan that table (in your case) 70 times and if it are big tables that could take some time.

Silk
0
 
LVL 1

Author Comment

by:malcolmbegg
ID: 10791042
Spot-on LordSilk, there was one foreign key to this table (which I hadn't noticed), referencing a fairly big table (approx. 400000 records).  I indexed the foreign keys and hey presto, it now deletes virtually instantly.

Many thanks, and thanks also to all others who contributed - especially monosodiumg.  I realise now you were going along the right tracks with your comment, but I only noticed after LordSilk's solution.

Malcie.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 72
SQL Script to find duplicates 16 20
Date conversion in sql server 2012 6 26
SQL JOIN + SUBQUERY? 3 14
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now