Delete is very slow

  • I have a table Mytable1 with 735507  rows and this table has 16 columns with about 6 columns with datatype varchar. I am trying to delete about 48,000 rows from this table but it is taking very long ( about 10 minutes ) to delete even 200 rows.

    Here are the things I have tried-

    Before deleting I ran the following

    alter table MyTable1 nocheck constraint all

     

    create a view vwToDeleteSelect that selects 1000 rows at a time -- this is to delete in batches

    delete vw from vwToDeleteSelect vw

    inner join #tempTable1 t

    on vw.key1 = t.key1

     

    I have a list of key1 values  that I want to delete in the temp table and I have indexed the temp table on key1 column upon creation of the temp table.

    I have even tried deleting the indexes , keeping the indexes,  but no performance gain. Are there any other ideas on how I can gain the delete performance?

     

     

  • Is there any blocking from other processes ?

  • No, I am the only one running this query at the moment.

  • Guras wrote:

    No, I am the only one running this query at the moment.

    That doesn't mean there can't be another process locking your table.

    • This reply was modified 5 years, 7 months ago by  Y.B.. Reason: Added additional comment


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Y.B. wrote:

    Guras wrote:

    No, I am the only one running this query at the moment.

    That doesn't mean there can't be another process locking your table.

     

    No processes nothing. I am the only one connected to the server at the moment.

  • Look at the query plan.  Is SQL able to do seeks to find the 1000 rows or does it have to scan the whole table first?

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • It has scan  to find the 1000 rows

    code for the view

    select top 200 key1 from [dbo].[Mytable1]

    where key1 not in (select key1 from Mytable2)

    order by Key1

     

    --I was not ordering before still no performance gain.

  • Guras wrote:

    Before deleting I  create a view vwToDeleteSelect that selects 1000 rows at a time -- this is to delete in batches delete vw from vwToDeleteSelect vw inner join #tempTable1 t on vw.key1 = t.key1   I have a list of key1 values  that I want to delete in the temp table and I have indexed the temp table on key1 column upon creation of the temp table.

    There is no need to create a view. You can just delete 1000 rows in a loop.

    Do you have many indexes on dbo.MyTable1?

    What is the average size of a row?

    I would create a script like this:

    1. Insert all the rows that need to be deleted into the temporary table in one go.
    2. Put a primary key on the #tempTable1(key1).
    3. After every delete of the batch number of rows, delete the rows on #TempTable that have been deleted from [dbo].[Mytable1]. This will shorten the length of the temp table so make it a bit faster.
    4. Insert the keys of the rows that have been deleted into a temporary table by OUTPUTing the key rows that have been deleted.
    5. Make sure you have an index/primary key on  [dbo].[Mytable1](key1)?

    So the script would be:

    IF OBJECT_ID('tempdb..#tempTable1','U') IS NOT NULL BEGIN
    DROP TABLE #tempTable1
    END
    --
    -- Insert ALL the rows that are going to be deleted
    SELECT key1
    INTO #tempTable1
    FROM dbo.MyTable1 a
    WHERE NOT EXISTS(SELECT * FROM Mytable2 b WHERE b.key1 = a.key1)
    --
    CREATE UNIQUE CLUSTERED INDEX IX_#tempTable1_1 ON #tempTable1(key1)
    --
    IF OBJECT_ID('tempdb..#RowsToDeleteInBatch','U') IS NOT NULL BEGIN
    DROP TABLE #RowsToDeleteInBatch
    END
    CREATE TABLE #RowsToDeleteInBatch(key1 int NOT NULL PRIMARY KEY CLUSTERED);
    --
    DECLARE @BatchSize int = 1000
    DECLARE @RowCount int = @BatchSize
    --
    WHILE @RowCount = @BatchSize BEGIN
    --
    -- Insert the keys that are going to be deleted into table #RowsToDeleteInBatch and delted them from #tempTable1
    DELETE TOP(@BatchSize) X
    OUTPUT key1 INTO #RowsToDeleteInBatch
    FROM #tempTable1 X
    --
    DELETE TOP(@BatchSize) X
    FROM dbo.MyTable1 X
    INNER JOIN #RowsToDeleteInBatch t ON t.key1 = X.key1;
    --
    SELECT @RowCount = @@ROWCOUNT;
    --
    TRUNCATE TABLE #RowsToDeleteInBatch;
    --
    END
    DROP TABLE #RowsToDeleteInBatch;
    DROP TABLE #tempTable1;
    GO

     

    • This reply was modified 5 years, 7 months ago by  Jonathan AC Roberts. Reason: Updated code to be slightly more efficient
  • Thank you  I will try this.

  • Guras wrote:

    Thank you  I will try this.

     

    You really need to answer the questions about how many indexes there are.  You'll also need to identify how many foreign keys there are.  We had a table where it took over 5 minutes to delete just one row.  It turned out to be the FKs... they weren't correctly indexed.  Once we indexed those, time to delete a row when down to just a couple of milliseconds.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Guras wrote:

    Thank you  I will try this.

    You'll also need to identify how many foreign keys there are.  We had a table where it took over 5 minutes to delete just one row.  It turned out to be the FKs... they weren't correctly indexed.  Once we indexed those, time to delete a row when down to just a couple of milliseconds.

    Yes, particularly foreign keys on other tables that reference dbo.MyTable1 would be a cause of slow deletes as the SQL engine would have to check the absence of the related child row on the other table. These are more likely to be unindexed columns as FK's are all related to either a primary key or unique index. The OP should run the following query to check that there are no other tables referencing the table he is deleting from:

    EXEC sp_fkeys @pktable_name='MyTable1', @pktable_owner='dbo'

    After rechecking the question it looks like maybe Mytable2(key1) needs an index for this reason?

  • Yes, the indexing on the FOREIGN  KEY on the referencing tables did it. Thank you !

  • Guras wrote:

    Yes, the indexing on the FOREIGN  KEY on the referencing tables did it. Thank you !

    Awesome.  Thanks for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

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