Asked by:
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
Thursday, February 13, 2020 2:16 AM
All replies
-
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.
Thursday, February 13, 2020 12:10 PM -
User696604810 posted
can i do another solution alternative to while loop
so please help me
Thursday, February 13, 2020 12:24 PM -
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 nullis 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.
Thursday, February 13, 2020 1:12 PM -
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
Thursday, February 13, 2020 2:23 PM -
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.
Thursday, February 13, 2020 3:09 PM