How to enhance query performance when loop within temp table ?

Question

User696604810 posted

I work on SQL server 2012

I have temp table get data from excel and based on data exist on excel i insert on table inside loop

temp table always have big amount of data may be at least 5000 or 10000 or 15000 or more

I need every iteration increased by 5000 rows insert from temp table

so that i need best solutions for that according to speed and memory like that

and if there are any thing not correct as logic please tell me

my Query as below :

create table #Temp(
DocumentPartID int identity(1,1),
CompanyName VARCHAR(4000),
[AffectedProduct] NVARCHAR(4000),
[ReplacementPart] VARCHAR(4000) ,
[ReplacementCompany] VARCHAR(4000) ,
[Category] VARCHAR(4000) ,


DocumentID int null,
CompanyID VARCHAR(4000) null,
PartID int null,
ReplacementPartID int null,
CategoryID int null,
[Status] VARCHAR(4000) null ,


)



insert into #Temp
(
CompanyName ,
[AffectedProduct],
[ReplacementPart],
[ReplacementCompany],
[Category]
)
values
('Nokia','RF1550','RF1550','HTS','HTS'),
('IPHONE','TF1545','TF1545','Corning Incorporated','HTS2')



DECLARE @MaxValue int = ( select Max(DocumentPartID) from #Temp)
DECLARE @Currentindex int =0
DECLARE @Rows [dbo].[Type_ValidationInPut];

while @Currentindex < @MaxValue
begin


DELETE @Rows
INSERT INTO @Rows
(
RowNumber ,
GivenPartNumber ,
GivenManufacturer
)


select TOP 5000 DocumentPartID , isnull(AffectedProduct,''), isnull(CompanyName,'') FROM #Temp where

(CategoryID = 517884 or CategoryID = 1110481) and (DocumentPartID > @Currentindex) and [Status] is null

INSERT INTO @Rows
(
RowNumber ,
GivenPartNumber ,
GivenManufacturer
)


select TOP 5000 DocumentPartID, isnull(substring(ReplacementPart,0,70),''), isnull(ReplacementCompany,'') FROM #Temp where
(DocumentPartID > @Currentindex) and [Status] is null and ReplacementPart is not null


DECLARE @NewID nVARCHAR(4000) =newID()
insert into [ls30].[validation].[dbo].PartsData (BatchID,RowNumber,GivenPartNumber,givenmanufacturer)
SELECT @NewID ,0,GivenPartNumber,GivenManufacturer from @Rows



set @Currentindex = @Currentindex +5000
DELETE @Rows
end

Answer 1

User753101303 posted

Hi,

It really can't be done in one go? TOP could be replaced by selecting DocumentPartID between two values. You are inserting twice from more or less the same row(s). Generally speaking I always try to do things in one go rather  by looping.

Also https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql?view=sql-server-ver15 could perhaps help to really analyze what SQL Server does rather than trying to guess.

Answer 2

User696604810 posted

can i do another solution alternative to  while loop 

so please help me

Answer 3

User753101303 posted

You used a loop because? For now it seems to me that if :

select  @newId,DocumentPartID , isnull(AffectedProduct,''), isnull(CompanyName,'')
FROM #Temp
where (CategoryID = 517884 or CategoryID = 1110481) and /*(DocumentPartID > @Currentindex) and*/ [Status] is null
-- and replacementpart is null ???

select @newid,DocumentPartID, isnull(substring(ReplacementPart,0,70),''), isnull(ReplacementCompany,'') FROM #Temp
where /*(DocumentPartID > @Currentindex) and*/ [Status] is null and ReplacementPart is not null

is giving the data you want to insert (a bit worried about having two where criteria which doesn't seems to guarantee each row is processed one time) then you could use two INSERT statements as you are doing or you could also use :

INSERT INTO ...
SELECT ...
UNION ALL SELECT ...

I believe you can do what you want with a single IN SERT statement or at worst two.

Answer 4

User696604810 posted

How to iterate within data 

if i make select 5000 from temp 

this will select only one time 

I need to make them 5000 by 5000 

so that i need to do that it main requirment

Answer 5

User753101303 posted

If you want to keep and enhance your current approach it seems to me you could skip using @rows to which you copy data to then copy them unchanged to the final table and deleting them from @rows.

If this to insert both SELECT inside a single transaction you could use an explicit transaction or the same UNION query shown above (just keep your full WHERE criteria)

Also I really don't like the TOP query without an ORDER BY (this is an old version, I would have thought this to be even not allowed ?)

Edit: else you could use ROW_NUMBER to generate a new batch id but I believe you are using an old SQL Server version which doesn't have this. Also ultimately using tools such as "show plan" allows SQL Server to tell you how he proceed and can give directions for enhancing your query (do you have an index on DocumentPartId ?) rather than by guessing.