Results 1 to 11 of 11

Thread: Deletes

  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Deletes

    In a heavly indexed table, why does a delete step run forever?
    Jim

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Becuase it has to change the data pages and the indexes...

    INSERTS should take even longer...

    What does the DELETE statement look like?

    And what about the DDL for the table...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by Brett Kaiser
    Becuase it has to change the data pages and the indexes...

    INSERTS should take even longer...

    What does the DELETE statement look like?

    And what about the DDL for the table...
    Thats what i was thinking

    the statment is basiacaly
    delete tablename
    where date = '2003/12/12'
    and direction = 'E'

    simple enought but there are about 12 indexes on the table. There is also 20 some od columns to the table so its got a bit to go through. Sounds like a delete step isnt the right thing to do...but I dont know of anything else. Rebuilding the table has proven to be faster though, guess im gona go back to that.
    Jim

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    apparently you have a long table with quite a few records meeting the criteria, because even if there are 12 indexes it should go through delete farely fast if the number of rows affected it significantly less than the total number of rows (<500)

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by ms_sql_dba
    apparently you have a long table with quite a few records meeting the criteria, because even if there are 12 indexes it should go through delete farely fast if the number of rows affected it significantly less than the total number of rows (<500)
    we are trying to delete about 500000 rows out of a 23Million row table
    hehe
    Jim

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do any of your indexes have date and/or direction?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by Brett Kaiser
    Do any of your indexes have date and/or direction?
    Direction yes, no date indexes...coleage of mine was thinking that it would be benificial if we had a date index.....
    Jim

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well hell yes...

    Do a show estimated execution plan, and tell us what you get...

    I'm thinking table scan...

    Edit is direction part of a composite of another index, or is it all by it's lonesome?

    If it's alone, just add date to that one, and go have a couple of beers...it'll take a while.....

    better to schedule a script to run overnight...

    let us know how long it takes if you do do it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    An informative an lengthy discourse on indexes

    http://www.sqlteam.com/forums/topic....x,intersection
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    you'd really benefit having date and direction as an index (order depends on selectivity of direction vs. date, the less selective field should be first).

  11. #11
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Make sure you have a clustered index on the table as well as heaps reclaim empty space and deletes on fragmented tables take longer than normal.

    dbcc showcontig on the table will tell you how fragemented the table is.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •