none
Simple delete query VERY SLOW RRS feed

  • Question

  • The following query takes forever to run (more than 4 minutes!)

    DELETE FROM APPDB.dbo.trHOLDINGS WHERE Date < '12/10/2009'

    The table has about 200000 rows, with Date column having 2 values 12/9/2009, 12/10/2009.  The delete statement affects about 100000 rows.

    The table has a primary key and a unique key both include the Date column.

    the Execution plan says deleting clustered index (the primary key) takes 92% of cost.

    Any idea how to resolve this?
    HaveFun
    Friday, December 11, 2009 4:06 PM

Answers

  • DELETE operation maybe slow due to index reshuffling as a result of removed keys. 

    If this is one time operation, you can do the following (much faster than deleting 1/2):

    1. Rebuild the table with SELECT  ..... INTO mytable .... FROM renamedmytable WHERE (what you want to keep).

    2. Apply indexes and constraints after rebuild.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, December 11, 2009 4:17 PM
    Moderator
  • Due to the large amount of records affected by the statement compared to the total size of the table (low selectivity) a clustered index scan will be required.  The speed of the operation can be improved by having your clustered index only on date column and reducing the amount of nonclustered indexes on the table.
    Anthony Martin | www.emc.com/mspractice

    True Anthony in most cases a clustered index scan will be done, but not in all cases.  It is quite possible to get a more efficient query plan using a nonclustered index, even when 50% of the table is deleted.  Here is a example below.

    IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
    BEGIN
    	DROP TABLE dbo.[TestData];
    END
    GO
    
    CREATE TABLE dbo.TestData(
    RowNum INT PRIMARY KEY,
    SomeId INT,
    SomeCode CHAR(2),
    Dt DATETIME
    );
    GO
    
    INSERT INTO dbo.TestData
    SELECT TOP 200000 
    	ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
    	ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, 
    	CHAR(ABS(CHECKSUM(NEWID()))%26+65)
        + CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode,
        CASE WHEN ROW_NUMBER() OVER (ORDER BY t1.NAME) % 2 = 0 THEN '2009-12-11' ELSE '2009-12-10' END AS dt
    FROM 
    	Master.dbo.SysColumns t1,
    	Master.dbo.SysColumns t2
    GO
    
    CREATE INDEX IXC_TestData_Cover2 ON dbo.TestData(dt) 
    GO
    


    DELETE FROM dbo.TestData WHERE dt < '2009-12-11'



    Here is a the query plan:


    Rows                 Executes             StmtText                                                                                                                                                                        StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                             DefinedValues                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                          Warnings Type                                                             Parallel EstimateExecutions
    -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
    100000               1                    DELETE [dbo].[TestData]  WHERE [dt]<@1                                                                                                                                          1           1           0           NULL                           NULL                           NULL                                                                                                                                                 NULL                                100000        NULL          NULL          NULL        4.973367         NULL                                NULL     DELETE                                                           0        NULL
    100000               1                      |--Clustered Index Delete(OBJECT:([tempdb].[dbo].[TestData].[PK__TestData__27265C9E]), OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]) WITH UNORDERED PREFETCH)      1           2           1           Clustered Index Delete         Delete                         OBJECT:([tempdb].[dbo].[TestData].[PK__TestData__27265C9E]), OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]) WITH UNORDERED PREFETCH        NULL                                100000        4.41527       0.2           9           4.973367         NULL                                NULL     PLAN_ROW                                                         0        1
    100000               1                           |--Top(ROWCOUNT est 0)                                                                                                                                                   1           4           2           Top                            Top                            TOP EXPRESSION:((0))                                                                                                                                 NULL                                100000        0             0.01          11          0.3580968        [tempdb].[dbo].[TestData].[RowNum]  NULL     PLAN_ROW                                                         0        1
    100000               1                                |--Index Seek(OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]), SEEK:([tempdb].[dbo].[TestData].[Dt] < CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)  1           5           4           Index Seek                     Index Seek                     OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]), SEEK:([tempdb].[dbo].[TestData].[Dt] < CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD  [tempdb].[dbo].[TestData].[RowNum]  100000        0.2379398     0.110157      11          0.3480968        [tempdb].[dbo].[TestData].[RowNum]  NULL     PLAN_ROW                                                         0        1
    


    I also agree with Kalman that other factors may also be slowing down the delete, like too many indexes or clustered index maintenance, if an incrementing key is not used etc...

    http://jahaines.blogspot.com/
    Friday, December 11, 2009 4:54 PM
    Moderator

All replies

  • <<The table has a primary key and a unique key both include the Date column.

    Is the date column first in the index? If not then the optimizer will have to scan the primary key instead of seeking rows, which means this simple query takes more time than it really should have to.  You need to make sure you have an index on date and it does have to be first in the index l list.  Also, when rows are deleted all foreign keys have to be checked.  You have have an issue where an index is missing on one of the referencing tables and that too can slow down the delete.  Finally, any delete triggers or cascading will also affect delete performance. 

    If I had to guess at your problem, I would say you probably do not have an index on date (does not matter if it is included in an existing index, as it has to be listed first in the index key) and therefore the optimizer is scanning the table.
    http://jahaines.blogspot.com/
    Friday, December 11, 2009 4:11 PM
    Moderator
  • Due to the large amount of records affected by the statement compared to the total size of the table (low selectivity) a clustered index scan will be required.  The speed of the operation can be improved by having your clustered index only on date column and reducing the amount of nonclustered indexes on the table.
    Anthony Martin | www.emc.com/mspractice
    Friday, December 11, 2009 4:15 PM
    Answerer
  • DELETE operation maybe slow due to index reshuffling as a result of removed keys. 

    If this is one time operation, you can do the following (much faster than deleting 1/2):

    1. Rebuild the table with SELECT  ..... INTO mytable .... FROM renamedmytable WHERE (what you want to keep).

    2. Apply indexes and constraints after rebuild.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, December 11, 2009 4:17 PM
    Moderator
  • Due to the large amount of records affected by the statement compared to the total size of the table (low selectivity) a clustered index scan will be required.  The speed of the operation can be improved by having your clustered index only on date column and reducing the amount of nonclustered indexes on the table.
    Anthony Martin | www.emc.com/mspractice

    True Anthony in most cases a clustered index scan will be done, but not in all cases.  It is quite possible to get a more efficient query plan using a nonclustered index, even when 50% of the table is deleted.  Here is a example below.

    IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
    BEGIN
    	DROP TABLE dbo.[TestData];
    END
    GO
    
    CREATE TABLE dbo.TestData(
    RowNum INT PRIMARY KEY,
    SomeId INT,
    SomeCode CHAR(2),
    Dt DATETIME
    );
    GO
    
    INSERT INTO dbo.TestData
    SELECT TOP 200000 
    	ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
    	ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, 
    	CHAR(ABS(CHECKSUM(NEWID()))%26+65)
        + CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode,
        CASE WHEN ROW_NUMBER() OVER (ORDER BY t1.NAME) % 2 = 0 THEN '2009-12-11' ELSE '2009-12-10' END AS dt
    FROM 
    	Master.dbo.SysColumns t1,
    	Master.dbo.SysColumns t2
    GO
    
    CREATE INDEX IXC_TestData_Cover2 ON dbo.TestData(dt) 
    GO
    


    DELETE FROM dbo.TestData WHERE dt < '2009-12-11'



    Here is a the query plan:


    Rows                 Executes             StmtText                                                                                                                                                                        StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                             DefinedValues                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                          Warnings Type                                                             Parallel EstimateExecutions
    -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
    100000               1                    DELETE [dbo].[TestData]  WHERE [dt]<@1                                                                                                                                          1           1           0           NULL                           NULL                           NULL                                                                                                                                                 NULL                                100000        NULL          NULL          NULL        4.973367         NULL                                NULL     DELETE                                                           0        NULL
    100000               1                      |--Clustered Index Delete(OBJECT:([tempdb].[dbo].[TestData].[PK__TestData__27265C9E]), OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]) WITH UNORDERED PREFETCH)      1           2           1           Clustered Index Delete         Delete                         OBJECT:([tempdb].[dbo].[TestData].[PK__TestData__27265C9E]), OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]) WITH UNORDERED PREFETCH        NULL                                100000        4.41527       0.2           9           4.973367         NULL                                NULL     PLAN_ROW                                                         0        1
    100000               1                           |--Top(ROWCOUNT est 0)                                                                                                                                                   1           4           2           Top                            Top                            TOP EXPRESSION:((0))                                                                                                                                 NULL                                100000        0             0.01          11          0.3580968        [tempdb].[dbo].[TestData].[RowNum]  NULL     PLAN_ROW                                                         0        1
    100000               1                                |--Index Seek(OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]), SEEK:([tempdb].[dbo].[TestData].[Dt] < CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)  1           5           4           Index Seek                     Index Seek                     OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]), SEEK:([tempdb].[dbo].[TestData].[Dt] < CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD  [tempdb].[dbo].[TestData].[RowNum]  100000        0.2379398     0.110157      11          0.3480968        [tempdb].[dbo].[TestData].[RowNum]  NULL     PLAN_ROW                                                         0        1
    


    I also agree with Kalman that other factors may also be slowing down the delete, like too many indexes or clustered index maintenance, if an incrementing key is not used etc...

    http://jahaines.blogspot.com/
    Friday, December 11, 2009 4:54 PM
    Moderator
  • Due to the large amount of records affected by the statement compared to the total size of the table (low selectivity) a clustered index scan will be required.  The speed of the operation can be improved by having your clustered index only on date column and reducing the amount of nonclustered indexes on the table.
    Anthony Martin | www.emc.com/mspractice

    True Anthony in most cases a clustered index scan will be done, but not in all cases.  It is quite possible to get a more efficient query plan using a nonclustered index, even when 50% of the table is deleted.  Here is a example below.

    IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
    
    BEGIN
    
    	DROP TABLE dbo.[TestData];
    
    END
    
    GO
    
    
    
    CREATE TABLE dbo.TestData(
    
    RowNum INT PRIMARY KEY,
    
    SomeId INT,
    
    SomeCode CHAR(2),
    
    Dt DATETIME
    
    );
    
    GO
    
    
    
    INSERT INTO dbo.TestData
    
    SELECT TOP 200000 
    
    	ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
    
    	ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, 
    
    	CHAR(ABS(CHECKSUM(NEWID()))%26+65)
    
        + CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode,
    
        CASE WHEN ROW_NUMBER() OVER (ORDER BY t1.NAME) % 2 = 0 THEN '2009-12-11' ELSE '2009-12-10' END AS dt
    
    FROM 
    
    	Master.dbo.SysColumns t1,
    
    	Master.dbo.SysColumns t2
    
    GO
    
    
    
    CREATE INDEX IXC_TestData_Cover2 ON dbo.TestData(dt) 
    
    GO
    
    
    
    


    DELETE FROM dbo.TestData WHERE dt < '2009-12-11'
    
    



    Here is a the query plan:


    Rows                 Executes             StmtText                                                                                                                                                                        StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                             DefinedValues                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                          Warnings Type                                                             Parallel EstimateExecutions
    
    -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
    
    100000               1                    DELETE [dbo].[TestData]  WHERE [dt]<@1                                                                                                                                          1           1           0           NULL                           NULL                           NULL                                                                                                                                                 NULL                                100000        NULL          NULL          NULL        4.973367         NULL                                NULL     DELETE                                                           0        NULL
    
    100000               1                      |--Clustered Index Delete(OBJECT:([tempdb].[dbo].[TestData].[PK__TestData__27265C9E]), OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]) WITH UNORDERED PREFETCH)      1           2           1           Clustered Index Delete         Delete                         OBJECT:([tempdb].[dbo].[TestData].[PK__TestData__27265C9E]), OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]) WITH UNORDERED PREFETCH        NULL                                100000        4.41527       0.2           9           4.973367         NULL                                NULL     PLAN_ROW                                                         0        1
    
    100000               1                           |--Top(ROWCOUNT est 0)                                                                                                                                                   1           4           2           Top                            Top                            TOP EXPRESSION:((0))                                                                                                                                 NULL                                100000        0             0.01          11          0.3580968        [tempdb].[dbo].[TestData].[RowNum]  NULL     PLAN_ROW                                                         0        1
    
    100000               1                                |--Index Seek(OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]), SEEK:([tempdb].[dbo].[TestData].[Dt] < CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)  1           5           4           Index Seek                     Index Seek                     OBJECT:([tempdb].[dbo].[TestData].[IXC_TestData_Cover2]), SEEK:([tempdb].[dbo].[TestData].[Dt] < CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD  [tempdb].[dbo].[TestData].[RowNum]  100000        0.2379398     0.110157      11          0.3480968        [tempdb].[dbo].[TestData].[RowNum]  NULL     PLAN_ROW                                                         0        1
    
    


    I also agree with Kalman that other factors may also be slowing down the delete, like too many indexes or clustered index maintenance, if an incrementing key is not used etc...

    http://jahaines.blogspot.com/

    Interesting, I ran the exact code you posted and got a clustered index scan

    StmtText
      |--Clustered Index Delete(OBJECT:([AdventureWorks].[dbo].[TestData].[PK__TestData__4F4A68527CA47C3F]), OBJECT:([AdventureWorks].[dbo].[TestData].[IXC_TestData_Cover2]))
           |--Top(ROWCOUNT est 0)
                |--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[TestData].[PK__TestData__4F4A68527CA47C3F]),  WHERE:([AdventureWorks].[dbo].[TestData].[Dt]<'2009-12-11 00:00:00.000') ORDERED FORWARD)
    Anthony Martin | www.emc.com/mspractice
    Friday, December 11, 2009 6:22 PM
    Answerer
  • Anthony like I said before it is for the most part unpredicable and it really depends on the data distribution, among other factors.  I reran the code a lot of times and got the index seek a few times, but majority were in fact the clustered index scan. I guess my first test was a good one :^).  With all that said, you can change the < to an equality join and see that the same number of rows is deleted however the optimizer does in fact choose a seek, instead of a clustered scan, which generates a far less complex execution plan.  And this is the point I wanted to focus on.. just because 50% of the table is being deleted does not necessarily mean a scan has to or will occur.  This is why it is still important to make sure you have the proper indexes in place, so the optimizer can make the best decision about which query plan to use.  In fact if you run two seperate deletes on two seperate tables, you will just how much more work the clustered index scan is causing the query.  In my comparisons it is nearly 48% more expensive to use the clustered scan than the index.

    Take a look at these execution plans. 

    Seek Vs Scan: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/ExecutionPlans/ExecutionPlan^_Scan^_vs^_Seek.JPG
    Both Seeks: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/ExecutionPlans/ExecutionPlan^_Both^_Seeks.JPG


    http://jahaines.blogspot.com/
    Friday, December 11, 2009 7:06 PM
    Moderator
  • Okay, I will concede that it is possible to get different query plans even based on the low selectivity of the query.  However, I don't believe that I agree that the index seek performs better in this scenario.  Take a look at STATISTICS IO and STATISTICS TIME output from the nonclustered index seek (using an equality join worked to get this) versus the clustered index scan...

    NONCLUSTERED (COVERING) INDEX SEEK
    Table 'TestData'. Scan count 1, logical reads 907546, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    SQL Server Execution Times:
       CPU time = 1812 ms,  elapsed time = 4101 ms.
    
    CLUSTERED INDEX SCAN
    Table 'TestData'. Scan count 1, logical reads 301733, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    SQL Server Execution Times:
       CPU time = 984 ms,  elapsed time = 4438 ms.

    What I see from this output is that while total execution is slightly higher, CPU time is 50% less and logical reads are 66% less.

    We can get even get significanatly better results by creating the clustered index on the dt column instead of the rownum column as outlined below

    CLUSTERED INDEX SEEK (clustered index on dt column)
    Table 'TestData'. Scan count 1, logical reads 3986, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    SQL Server Execution Times:
       CPU time = 203 ms,  elapsed time = 471 ms.

    Another advantage of creating the clustered index on the date column is it will probably make more sense if the user wants to take advantage of partitioning.

    I'm interested in hearing yours and others take on this


    Anthony Martin | www.emc.com/mspractice
    Friday, December 11, 2009 7:50 PM
    Answerer
  • Try using profiler instead of set statistics IO for the reads.  Sometimes set statistcs IO can be off.  I based my analysis on the % cost of the operators.  To have such a huge cost difference, something IO bound must not be reporting correct.  It is possible that the optimizer is just way off in the % to batch, but I have to give it the benefit of doubt and suspect the IO stats returned is not accurate. We wont know for sure until profiler gives us an accurate read count.  If you dont mind, please do the test again in profiler and make sure to not display any execution plans and discard query results.  If you cant get to it, I will take another stab at this tonight.


    <<We can get even get significanatly better results by creating the clustered index on the dt column instead of the rownum column as outlined below
    I definetly agree with this but a lot of times a clustered index is already in place and there is usually a better candidate for the clustered index; however, this will most defintely yield the best performance.


    http://jahaines.blogspot.com/
    Friday, December 11, 2009 8:08 PM
    Moderator
  • Very similar results were observed through profiler.  I will post the screenshot to my skydrive, when I'm able to disconnect from VPN (skydrive is blocked while connected to my work network)
    Anthony Martin | www.emc.com/mspractice
    Friday, December 11, 2009 8:44 PM
    Answerer
  • These results just dont make sense to me.  Even though the clustered index had less reads, which it should not have because a scan scans all pages where as a seek does not, just seems contradictory.  Also, the clustered index scan query had to sort the data before doing index maintainence, which costs makes it even more expensive.  I just cant see how the seek is more expensive.  I will have to do a little more digging on this one.
    http://jahaines.blogspot.com/
    Friday, December 11, 2009 9:19 PM
    Moderator
  • Friday, December 11, 2009 9:43 PM
    Answerer
  • Thanks guys.  I will try moving the Date column to be the first column in both the primary key and unique key and see how it goes, as Adam suggested.

    This table doesn't have foreign keys and also when the delete happens, no one else would be accessing it since I run the delete overnight.  So this should rule out a lot of the possible causes. 

    I searched on the Web and someone seems to be suggesting that a large delete creates large log file and this may hurt the query speed.  Any thoughts on this?

    Also it seems that adding the primary key and unique key slows down the insert query a lot.  I have to delete and insert about 50% of the data as a daily refreshing process.
    HaveFun
    Friday, December 11, 2009 10:26 PM
  • Hi,

    Please try deleting the records in Batches.


    Tuesday, July 18, 2017 7:54 AM
  • I believe you are doing a truncate load.Where you delete same some data and load same with some updated information.

    I will suggest to use SSIS packages for this as with that you can search the existing data and can update wherever required.

    Similarly if there will be new data same can be inserted.

    this can be achieved through conditional split in SSIS.


    Charanjit Jagdev

    Thursday, March 15, 2018 3:20 PM