Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can any one opmioptimize my code Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:48 AM
Points: 21, Visits: 143
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

)

select EmpBusinessID,
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
Post #1505643
Posted Thursday, October 17, 2013 6:48 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 1,472, Visits: 8,401
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.


BrainDonor
Linkedin
Blog Site
Post #1505678
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse