20,000 row delete takes 30 minutes! Why?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: 20,000 row delete takes 30 minutes! Why?

  1. #1
    Join Date
    Nov 2003
    Posts
    37

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    CBO/RBO?
    Index on PRODCODE?

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.)

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    JMac has inspired another reason for it to be slow:
    - A v.large number of indexes on the table

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    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.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    very high HWM?

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  


Click Here to Expand Forum to Full Width