problem deleting a line from db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

problem deleting a line from db

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-17 : 02:52:40

when i try to delete al ine from db in this 2 ways:
1)by code ->i get time out expired
2)deleting physiclly from the enterprise manager -it stuck !
why is that?
do i need to fix some how the table?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-17 : 03:05:56

1. What is the volume of the data in the table?
2. What is the query you are using to delete the data?
3. What are indexes on the table?

Harsh Athalye
India.
"Nothing is Impossible"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-17 : 03:12:41

1)i dont umderstand what u mean by "volume"?
2)the table has an autoincrement colum and i do for example :
"delete from table1 where id=556"
3)and the id colum which has the autoincrement is the "key" of the table

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-17 : 03:34:14

quote:
Originally posted by pelegk2

1)i dont umderstand what u mean by "volume"?
2)the table has an autoincrement colum and i do for example :
"delete from table1 where id=556"
3)and the id colum which has the autoincrement is the "key" of the table

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

By volume of data I mean the size of table (no. of rows). Also, check if there is any trigger defined on the table (ON DELETE Trigger).

If the table has clustered index on the table, the ID column is part of it and table size is huge, then try deleting the clustered index, delete the row and then recreate the index.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-17 : 04:10:42

It happens to me too sometimes. Then I rewrite my query to include


WHERE    ....
       AND primarykey = primarykey

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-17 : 04:55:55

1) i have about 15000 rows and i dont have any triggers
2)PESO - i dint understand what u meant?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-17 : 05:27:14

I have the same problem sometimes that a simple select takes over 5 minutes on a ~10k table.
What I do, is to add a new WHERE comparison. I add the primary key. Then the delete query taks about 3 milliseconds again to run.
Don't ask my why it works differently. It just does, for me. This has happened to me about three times in the last 6 months.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-17 : 05:49:46

quote:
Originally posted by Peso

I have the same problem sometimes that a simple select takes over 5 minutes on a ~10k table.
What I do, is to add a new WHERE comparison. I add the primary key. Then the delete query taks about 3 milliseconds again to run.
Don't ask my why it works differently. It just does, for me. This has happened to me about three times in the last 6 months.

Peter Larsson
Helsingborg, Sweden

But in pelegk2's case, the id column is already key (I assume that means primary key) column of the table...and with so little data, why it should take so much time, I have no idea !!

Peter,
Before and after adding primary key to WHERE condition, any significant change is shown in the execution plan (I mean which can reduce query const drastically) ?

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources