CODEWITHSUNDEEP
sqlsql-serverperformance
user2987605
user2987605

Reputation: 31

move records from details table to history table using stored procedure

I have a stored procedure which copies records from detail table to history table. It will run once in a day. Volume of records in detail table is few hundred thousand. It is taking more then 2 hrs to copy the detail to history table.

How to improve the performance?

My procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_Job_Archive_Record_Count]            
    @p_ToDate  DATETIME,          
    @p_Detail_Ins  INT OUTPUT,          
    @p_Detail_Del INT OUTPUT,          
    @p_Header_Ins INT OUTPUT,          
    @p_Header_Del INT OUTPUT          
as          
BEGIN TRAN    
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED          
    INSERT into tbl_Detail_History    
    SELECT * FROM tbl_Detail WITH(NOLOCK)    
    WHERE i_HeaderKey IN           
    (SELECT i_HeaderKey FROM tbl_Header WITH(NOLOCK)
    WHERE d_dateTime <= cast(@p_ToDate AS DATETIME)          
    )          

    SET @p_Detail_Ins = @@ROWCOUNT        
    INSERT INTO tbl_Archive_Count values('Detail',@p_Detail_Ins,getdate  (),@p_ToDate)          

    DELETE FROM tbl_detail           
    WHERE i_DetailKey IN          
        (SELECT i_DetailKey FROM tbl_Detail_History WITH(NOLOCK))          

    SET @p_Detail_Del = @@ROWCOUNT                   


    INSERT INTO tbl_Header_History    
    SELECT * FROM tbl_Header WITH(NOLOCK)    
        WHERE d_dateTime <= cast(@p_ToDate as DATETIME)          

    SET @p_Header_Ins = @@ROWCOUNT        
    INSERT INTO tbl_Archive_Count values('Header',@p_Header_Ins,getdate(),@p_ToDate)          

    DELETE FROM tbl_Header    
    WHERE i_Headerkey IN           
    (SELECT i_HeaderKey FROM tbl_Header_History WITH(NOLOCK))          

    SET @p_Header_Del = @@ROWCOUNT     

    IF (@@ERROR <> 0)    
        BEGIN    
            ROLLBACK TRAN       
        END    
COMMIT TRAN

Upvotes: 3

Views: 1041

Answers (1)

conan
conan

Reputation: 124

Depending on indexes and the rows ratio between tbl_detail and tbl_detail_history changing

DELETE FROM tbl_detail           
WHERE i_DetailKey IN          
    (SELECT i_DetailKey FROM tbl_Detail_History WITH(NOLOCK))

to

DELETE FROM tbl_detail  D         
WHERE exists (select 1 from FROM tbl_Detail_History WITH(NOLOCK) where i_DetailKey = d.i_DetailKey)

a similiar change can be made with the tbl_header delete. Should you get the performance increase you are looking for I would also suggest removing the no lock hint, particularily on the delete statements as you could cause yourself to have integrity issues there. Have a look at read_committed_snapshot which could be of benefit

HTH

Upvotes: -1

Related Questions