Delete-operation performance problem

Hi!

I'm experiencing the following experience problem with my SQL Server 2000.

Explanation a' la example;

1.
I insert data using my SP; EXEC dbo.up_DataInsert
This is fine, SQL Profiler duration only a few ms.

2.
I try to select the data.
select * from dbo.tblData where DataNumber = 283279
This is fine, SQL Profiler duration only a few ms.

3.
delete from dbo.tblData where DataNumber = 283279
This is NOT fine, SQL Profiler duration up to 50 seconds!!!

I have got a unique index with ignore duplicate key and the table has about
180.000 records.

What could be wrong here??

/Magnus"Magnus sterberg" <magnus.osterberg@.abo.fi> wrote in message
news:d9bgug$rcr$1@.plaza.suomi.net...
> Hi!
> I'm experiencing the following experience problem with my SQL Server 2000.
> Explanation a' la example;
> 1.
> I insert data using my SP; EXEC dbo.up_DataInsert
> This is fine, SQL Profiler duration only a few ms.
> 2.
> I try to select the data.
> select * from dbo.tblData where DataNumber = 283279
> This is fine, SQL Profiler duration only a few ms.
> 3.
> delete from dbo.tblData where DataNumber = 283279
> This is NOT fine, SQL Profiler duration up to 50 seconds!!!
> I have got a unique index with ignore duplicate key and the table has
> about 180.000 records.
> What could be wrong here??
> /Magnus

No idea - have you checked the query plan in Profiler to see where the time
is going? And are there any other factors, such as a DELETE trigger on the
table? If you can post the table DDL (including keys and indexes) and also
details of the query plan (SET SHOWPLAN_TEXT/ALL), someone may be able to
suggest something.

Simon|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> I'm experiencing the following experience problem with my SQL Server 2000.
> Explanation a' la example;
> 1.
> I insert data using my SP; EXEC dbo.up_DataInsert
> This is fine, SQL Profiler duration only a few ms.
> 2.
> I try to select the data.
> select * from dbo.tblData where DataNumber = 283279
> This is fine, SQL Profiler duration only a few ms.
> 3.
> delete from dbo.tblData where DataNumber = 283279
> This is NOT fine, SQL Profiler duration up to 50 seconds!!!
> I have got a unique index with ignore duplicate key and the table has
> about 180.000 records.

Possible causes:

1) Blocking.

2) Autogrow.

3) There is a trigger on the table.

4) There is a FK constraint from another, big table, and the FK
column in that table is not indexed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp