Can any one opmioptimize my code

  • Hi ,

    Can any one suggestion me how to optimize bellow code because it is taking 9 minutes for 1 Lakh records:

    stage table and destination tables from one database

    error table is another database

    semp1 is the source,demp1 is the destination table and Failed rows table is the tracking error row wise

    Declare @total int

    Declare @TestingTable table(rownum int,EmpBusinessid bigint,Empname varchar(200),age bigint,MaratialStatus char(1),ManagerID bigint,StatusCode varchar(1))

    insert into @TestingTable

    select row_number() over (order by EmpBusinessid) as rownum,EmpBusinessid,Empname,age,MaratialStatus,ManagerID,StatusCode

    from SCD.dbo.semp1 where StatusCode=1

    set @total=@@ROWCOUNT

    set nocount on

    declare @row as int

    declare @Rows1 as int

    declare @Errorid as int

    Declare @PackageStepLogID int

    Declare @PackageStepID int

    Declare @PackageLogID int

    Declare @StartDateTime datetime

    Declare @TableID as int

    set @row=1

    declare @ErrorDesc as varchar(500)

    Declare @demp1 table(EmpBusinessID int,EmpName varchar(20),age int,MaratialStatus char(1),ManagerID int,EMP_ROW_IS_CURRENT varchar(1),EMP_EFFECTIVE_DATE datetime,EMP_EXPIRY_DATE datetime)

    Declare @FailedRows table(id int,PackageStepLogID int ,PackageStepID int ,PackageLogID int ,StartDateTime datetime,EndDateTime datetime,TableID int ,ErrorId int,ErrorDesc varchar(500))

    while @row<=@total

    begin

    Begin Try

    INSERT INTO @demp1

    (

    EmpBusinessID,

    EmpName,

    age,

    MaratialStatus,

    ManagerID,

    EMP_ROW_IS_CURRENT,

    EMP_EFFECTIVE_DATE,

    EMP_EXPIRY_DATE

    )

    selectEmpBusinessID,

    EmpName,

    age,

    MaratialStatus,

    ManagerID,

    'Y',

    GETDATE(),

    Null

    from @TestingTable t

    where rownum = @row

    End try

    begin catch

    set @ErrorDesc=@@ERROR

    set @Errorid=ERROR_NUMBER()

    select @PackageStepLogID=100,@PackageStepID=101,@PackageLogID=102,@StartDateTime=getdate()

    select @TableID=4

    insert into @FailedRows(id,PackageStepLogID,PackageStepID,PackageLogID,StartDateTime,EndDateTime,TableID,ErrorId,ErrorDesc)

    values(@row,

    @PackageStepLogID,

    @PackageStepID,

    @PackageLogID,

    @StartDateTime,

    getdate(),

    @TableID,

    ERROR_NUMBER(),

    ERROR_MESSAGE()

    )

    end catch

    set @row = @row+1

    end

    insert into demp1( EmpBusinessID,

    EmpName,

    age,

    MaratialStatus,

    ManagerID,

    EMP_ROW_IS_CURRENT,

    EMP_EFFECTIVE_DATE,

    EMP_EXPIRY_DATE)

    select * From @demp1

    insert into [CC_ETL_CONFIG].dbo.FailedRows(id,

    PackageStepLogID,

    PackageStepID,

    PackageLogID,

    StartDateTime,

    EndDateTime,

    TableID,

    ErrorId,

    ErrorDesc)

    select * From @FailedRows

  • I may have misunderstood your intention with this code, so could I just check?

    You appear to be selecting data from scd.dbo.semp1 where Statuscode is 1, into @testingtable. Then you select all of these rows, one at a time in a WHILE loop into @demp1. Then you select all rows from @demp1 into demp1.

    Is that correct? If so, I don't understand why you can't just select from scd.dbo.semp1 into demp1 and miss out all of the WHILE loop in the middle.

    Because none of your tables have constraints I can't see how @FailedRows would ever get used, except for possible conversion issues, but resolve that issue by ensuring source and target data types are the same (Empname is varchar(200) in @testingtable but only varchar(20) in @demp1, for example), or convert them correctly in the code.

Viewing 2 posts - 1 through 1 (of 1 total)

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