473,322 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

Jul 23 '05 #1
2 1626

"Magnus Österberg" <ma**************@abo.fi> wrote in message
news:d9**********@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
Jul 23 '05 #2
Magnus Österberg (ma**************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Dave | last post by:
Hello all, In the code below, I see the following output: base::operator new(size_t, int) base::base() base::~base() base::operator delete(void *) In the case of an exception being thrown...
1
by: Nimmi Srivastav | last post by:
There's a rather nondescript book called "Using Borland C++" by Lee and Mark Atkinson (Que Corporation) which presents an excellent discussion of overloaded new and delete operators. In fact there...
3
by: Nimmi Srivastav | last post by:
There's a rather nondescript book called "Using Borland C++" by Lee and Mark Atkinson (Que Corporation) which presents an excellent discussion of overloaded new and delete operators. I am...
1
by: Douglas Peterson | last post by:
class Allocator { public: virtual void * Alloc(size_t) = 0; virtual void * Free(void*) = 0; }; class Object { public:
2
by: Dave | last post by:
Hello all, I'd like to find a source on the web that discusses, in a comprehensive manner and in one place, everything about new / delete. It should include overloading operator new, the new...
3
by: silver360 | last post by:
Hello, I'm trying to create a basic Heap manager and i have some question about new/delete overloading. The following code give me this output : >> $./heap >> registered : 0x804d098 >>...
5
by: junw2000 | last post by:
I use the code below to study delete and destructor. #include <iostream> using namespace std; struct A { virtual ~A() { cout << "~A()" << endl; }; //LINE1 void operator delete(void* p) {...
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
15
by: LuB | last post by:
I am constantly creating and destroying a singular object used within a class I wrote. To save a bit of time, I am considering using 'placement new'. I guess we could also debate this decision -...
29
by: =?Utf-8?B?R2Vvcmdl?= | last post by:
Hello everyone, I remembered delete is implemented through operator overloading, but I am not quite clear. Could anyone recommend some links about how delete is implemented so that I can...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.