SQL Server DELETE is slower with indexes

I have an SQL Server 2005 database, and I tried putting indexes on the appropriate fields in order to speed up the DELETE of records from a table with millions of rows (big_table has only 3 columns), but now the DELETE execution time is even longer! (1 hour versus 13 min for example)

I have a relationship between to tables, and the column that I filter my DELETE by is in the other table. For example

  • Export Image column from SQL Server 2000 using BCP
  • problem with
  • SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns
  • Database triggers
  • calculating age from sysdate and birthdate using SQL Server
  • How do I fix an error connecting to SQL Server: SSL Provider, error: 0 - The message received was unexpected or badly formatted
  • DELETE FROM big_table
    WHERE big_table.id_product IN (
    SELECT small_table.id_product FROM small_table
    WHERE small_table.id_category = 1)

    Btw, I’ve also tried:

    DELETE FROM big_table
    (SELECT 1 FROM small_table
    WHERE small_table.id_product = big_table.id_product
    AND small_table.id_category = 1)

    and while it seems to run slightly faster than the first, it’s still a lot slower with the indexes than without.

    I created indexes on these fields:

    1. big_table.id_product
    2. small_table.id_product
    3. small_table.id_category

    My .ldf file grows a lot during the DELETE.

    Why are my DELETE queries slower when I have indexes on my tables? I thought they were supposed to run faster.


    Okay, consensus seems to be indexes will slow down a huge DELETE becuase the index has to be updated. Although, I still don’t understand why it can’t DELETE all the rows all at once, and just update the index once at the end.

    I was under the impression from some of my reading that indexes would speed up DELETE by making searches for fields in the WHERE clause faster.

    Odetocode.com says:

    “Indexes work just as well when searching for a record in DELETE and UPDATE commands as they do for SELECT statements.”

    But later in the article, it says that too many indexes can hurt performance.

    Answers to bobs questions:

    1. 55 million rows in table
    2. 42 million rows being deleted
    3. Similar SELECT statement would not run (Exception of type ‘System.OutOfMemoryException’ was thrown)

    I tried the following 2 queries:

    SELECT * FROM big_table
    WHERE big_table.id_product IN (
    SELECT small_table.id_product FROM small_table
    WHERE small_table.id_category = 1)
    SELECT * FROM big_table
    INNER JOIN small_table
    ON small_table.id_product = big_table.id_product
    WHERE small_table.id_category = 1

    Both failed after running for 25 min with this error message from SQL Server 2005:

    An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

    The database server is an older dual core Xeon machine with 7.5 GB ram. It’s my toy test database 🙂 so it’s not running anything else.

    Do I need to do something special with my indexes after I CREATE them to make them work properly?

  • Enable constraint
  • how to enter values in rowguid column?
  • How do I check SQL replication status via T-SQL?
  • query to update data in a table in SQL
  • How do you convert VARCHAR to TIMESTAMP in MSSQL?
  • SQL - Count number of itemactive within a date rate
  • 5 Solutions collect form web for “SQL Server DELETE is slower with indexes”

    Indexes make lookups faster – like the index at the back of a book.

    Operations that change the data (like a DELETE) are slower, as they involve manipulating the indexes. Consider the same index at the back of the book. You have more work to do if you add, remove or change pages because you have to also update the index.

    I Agree with Bobs comment above – if you are deleting large volumes of data from large tables deleting the indices can take a while on top of deleting the data its the cost of doing business though. As it deletes all the data out you are causing reindexing events to happen.

    With regards to the logfile growth; if you arent doing anything with your logfiles you could switch to Simple logging; but i urge you to read up on the impact that might have on your IT department before you change.

    If you need to do the delete in real time; its often a good work around to flag the data as inactive either directly on the table or in another table and exclude that data from queries; then come back later and delete the data when the users aren’t staring at an hourglass. There is a second reason for covering this; if you are deleting lots of data out of the table (which is what i am supposing based on your logfile issue) then you will likely want to do an indexdefrag to reorgnaise the index; doing that out of hours is the way to go if you dont like users on the phone !

    You can also try TSQL extension to DELETE syntax and check whether it improves performance:

    DELETE FROM big_table
    FROM big_table AS b
    INNER JOIN small_table AS s ON (s.id_product = b.id_product)
    WHERE s.id_category  =1

    JohnB is deleting about 75% of the data. I think the following would have been a possible solution and probably one of the faster ones. Instead of deleting the data, create a new table and insert the data that you need to keep. Create the indexes on that new table after inserting the data. Now drop the old table and rename the new one to the same name as the old one.

    The above of course assumes that sufficient disk space is available to temporarily store the duplicated data.

    Try something like this to avoid bulk delete (and thereby avoid log file growth)

    declare @continue bit = 1
    -- delete all ids not between starting and ending ids
    while @continue = 1
        set @continue = 0
        delete top (10000) u
        from    <tablename> u WITH (READPAST)
        where   <condition>
        if @@ROWCOUNT > 0
            set @continue = 1 
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.