October 17, 2013 at 6:05 am
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
October 17, 2013 at 6:48 am
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