How to enhance query performance when loop within temp table ?RSS

• • Reply
  ahmedbarbary

  Member

  30 Points

  280 Posts

  How to enhance query performance when loop within temp table ?

  Feb 13, 2020 02:16 AM|ahmedbarbary|LINK

  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

• • Reply
  PatriceSc

  All-Star

  47030 Points

  17314 Posts

  Re: How to enhance query performance when loop within temp table ?

  Feb 13, 2020 12:10 PM|PatriceSc|LINK

  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.

• • Reply
  ahmedbarbary

  Member

  30 Points

  280 Posts

  Re: How to enhance query performance when loop within temp table ?

  Feb 13, 2020 12:24 PM|ahmedbarbary|LINK

  can i do another solution alternative to  while loop 

  so please help me

• • Reply
  PatriceSc

  All-Star

  47030 Points

  17314 Posts

  Re: How to enhance query performance when loop within temp table ?

  Feb 13, 2020 01:12 PM|PatriceSc|LINK

  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.

• • Reply
  ahmedbarbary

  Member

  30 Points

  280 Posts

  Re: How to enhance query performance when loop within temp table ?

  Feb 13, 2020 02:23 PM|ahmedbarbary|LINK

  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

• • Reply
  PatriceSc

  All-Star

  47030 Points

  17314 Posts

  Re: How to enhance query performance when loop within temp table ?

  Feb 13, 2020 03:09 PM|PatriceSc|LINK

  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.