-
20,000 row delete takes 30 minutes! Why?
I am trying to delete 20000 rows from a table (which has appx 25000 rows). But it is taking 30 minutes to delete the rows! It is a development database and no other activity is going on the table.
Any idea what went wrong.
I used this query
DELETE FROM PRODUCT WHERE PRODCODE LIKE '99%' AND LENGTH(PRODCODE)=7
-
Lots of things might cause the problem.
- Are there tables with Foreign Keys on the product table? If so, the 20'000 deletes might be generating a massive number of updates on other tables.
- Are there triggers on this table?
-
Re: 20,000 row delete takes 30 minutes! Why?
Originally posted by sbasak1
Any idea what went wrong.
What does the trace say?
Jeff Hunter
-
CBO/RBO?
Index on PRODCODE?
-
Originally posted by JMac
CBO/RBO?
Index on PRODCODE?
Not sure which way you're voting on this! If he's deleting 80% of the table, I'd hope that it would be reading the product table with a FTS. (So RBO & an index would not be a good idea.)
-
JMac has inspired another reason for it to be slow:
- A v.large number of indexes on the table
-
Give me a minute and I will formulate a way to blame this on the developers, then we can campaign to SHOOT THEM!!
MH
I remember when this place was cool.
-
Originally posted by DaPi
...I'd hope that it would be reading the product table with a FTS.
...hence if we had a trace or a simple explain plan we could tell what the problem is. Maybe the query is using RBO to pick an index which would be totally inappropriate. Maybe the stats were last run when there were 2000 rows in the table. Maybe, maybe, maybe....
Jeff Hunter
-
-
Originally posted by marist89
...if we had a trace or a simple explain plan ....
5ยข says we never get it
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|