CODEWITHSUNDEEP
sqlsql-server
Kesiya Abraham
Kesiya Abraham

Reputation: 853

Slowness while deleting large amount of data from a table

I have two tables [AssetPhoto] and [AssetPhoto_Backup] and have to delete some records, say 7 million, from [AssetPhoto] based on [AssetPhoto_Backup] table.

The table structure was very simple

CREATE TABLE [dbo].[AssetPhoto](
    [PhotoID] [int] NOT NULL,
    [AssetNumber] [nvarchar](50) NULL,
    [PhotoCaption] [nvarchar](50) NULL,
    [PhotoContent] [image] NULL,
    [PhotoCaptured] [datetime] NULL,
    [LastEditDate] [datetime] NULL,
    [CreationDate] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Structure of [AssetPhoto_Backup] table

    CREATE TABLE [dbo].[AssetPhoto_Backup](
    [PhotoID] [int] NOT NULL,
    [AssetNumber] [nvarchar](50) NULL,
    [PhotoCaption] [nvarchar](50) NULL,
    [PhotoContent] [image] NULL,
    [PhotoCaptured] [datetime] NULL,
    [LastEditDate] [datetime] NULL,
    [CreationDate] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

the query for deletion is

declare @i int =0; 



while(@i<=700) 

begin 

delete top(10000) a  from AssetPhoto a  join AssetPhoto_Backup ab 
on ab.photoid=a.photoid  

set @i=@i+1; 

end

Both have clustered index.When I tried to improve the performance using tuning advisor of MSSQl, it didn't give any recommendation.Both tables have PhotoId as PK.

How can I improve the query performance?Why the process speed slows down as the process goes on.Thanks in advance for the answers.

I have one more query,

    declare @i int =0; 



        while(@i<=700) 

        begin 

       delete  a from AssetPhoto a 
where a.photoid  in(select Top 10000 Suba.photoid
         from AssetPhoto Suba join AssetPhoto_Backup subab
         on subab.photoid=suba.photoid)

        set @i=@i+1; 

        end

Which one gives better performance?I am not that much of expert in SQL server.So, please anyone help me to understand the difference between these two queries?

Upvotes: 2

Views: 86

Answers (1)

Marc Guillot
Marc Guillot

Reputation: 6465

Commit your deletes after every batch, so the transaction log doesn't need to grow.

Also, I have decreased the batch size and increased the number of loops, so the batches will be smaller and commited more often, putting less load on the transaction log.

declare @i int =0; 

while(@i<=7000)    
begin 

  begin transaction;

  delete top(1000) a  
  from AssetPhoto a  
       join AssetPhoto_Backup ab on ab.photoid=a.photoid;

  commit transaction;

  set @i=@i+1;     
end

Upvotes: 1

Related Questions