Post reply

Delete statement takes forever

  • December 12, 2005 at 7:09 am

    #92688

    Hi,

     

    I've got a table with about 500 000 records and growing monthly by about 40 000 records

    When I perform the following query:

    DELETE from [myTable] WHERE Month = '07' AND Year='2005'

    This query will take about 10 minutes to execute. Columns Month & Year are both indexed.

    Surely MSSQL can't be this slow on only 500 000  records.

    Must I do some other database optimization ??? Examples please.

     

    Thanks

  • December 12, 2005 at 7:24 am

    #608989

    Put the clustered index on a DATE COLUMN

    Then > Delete from Table where DateCol between @StartDate and @EndDate.

    That will speed it up a lot. Also make sure you have optmized your delete triggers if any. Keep in mind too that any other indexes will have to be updated so that may take quite some times if you have a lot of indexes. Do you have any foreign keys linked to that table?

  • December 12, 2005 at 11:18 pm

    #609137

    Hi,

    Delete triggers ???

    How do I run/update my indexes ?

    I have no foreign keys linked to this table.

    Any ideas ?

     

    Thanks for reply.

     

  • December 13, 2005 at 8:42 am

    #609232

    So it's not a trigger problem.

    The indexes are automatically updated by the server, but that can take a long time if there are many of them.

    As I said, change the culstered index to the date and that'll run much faster.

  • December 13, 2005 at 12:07 pm

    #609286

    Part of the problem is likely to be that a delete logs the records deleted.  You might want to test selecting the data you want to keep into a temp table, truncating the live table then inserting the data back into the live table from the temp table.  It may be slower than the delete.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply