How to write loop below with best practice performance?

Transact-SQL
1

How to write loop below with best practice performance ?

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
2

avoid loops, like the plague! Have you tried this script locally? it has a few errors.

for ex what is this?
DECLARE @Rows [dbo].[Type_ValidationInPut];

1 Like
3

if i avoid loop can you please give me alternative solution for that
I know table have this line have issue

DECLARE @Rows [dbo].[Type_ValidationInPut];

i need if possible alternative solution for that .
so can you help me please
you can make my any solution for my case please ?
you can give me solution for that and make code as pseudo code

4

this is my full query
how to remove loop are you have any alternative solutions
Create Table [dbo].PartsData
(
BatchID nvarchar(50) primary key,
RowNumber int,
GivenPartNumber nvarchar(50),
GivenManufacturer nvarchar(100)

)

--select * from [dbo].PartsData
Create Table [dbo].[Type_ValidationInPut]
(
    RowNumber int,
    GivenPartNumber nvarchar(50),
GivenManufacturer nvarchar(100)

)

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 [dbo].PartsData (BatchID,RowNumber,GivenPartNumber,givenmanufacturer) 
SELECT  @NewID ,0,GivenPartNumber,GivenManufacturer from  @Rows 



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

tried this with random data of 100000 takes about 1 second. Will post back with how long it takes with loop

USE sqlteam 

go 

IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
    DROP TABLE #temp

IF OBJECT_ID('tempdb..#dataparser') IS NOT NULL 
    DROP TABLE #dataparser

IF OBJECT_ID('partsdata') IS NOT NULL 
    DROP TABLE partsdata

IF OBJECT_ID('type_validationinput') IS NOT NULL 
    DROP TABLE type_validationinput

CREATE TABLE [dbo].partsdata 
  ( 
     batchid           NVARCHAR(50) PRIMARY KEY default Newid(), 
     rownumber         INT, 
     givenpartnumber   NVARCHAR(50), 
     givenmanufacturer NVARCHAR(100) 
  ) 

--select * from [dbo].PartsData 
CREATE TABLE [dbo].[type_validationinput] 
  ( 
     rownumber         INT, 
     givenpartnumber   NVARCHAR(50), 
     givenmanufacturer NVARCHAR(100) 
  ) 

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]) 
SELECT TOP 100000
        companyname = case 
  when RIGHT(ABS(CHECKSUM(NEWID())%10000)+10000,4) % 2 = 0 
then 'Nokia' 
else 'IPHONE' end,
        affectedproduct = 'RF' + RIGHT(ABS(CHECKSUM(NEWID())%10000)+10000,4),
		replacementpart = 'RFv1.1' + RIGHT(ABS(CHECKSUM(NEWID())%10000)+10000,4),
		replacementcompany = ac1.name,
		category = 'HTS' + ac1.name
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2

--VALUES      ('Nokia', 
--             'RF1550', 
--             'RF1550', 
--             'HTS', 
--             'HTS'), 
--            ('IPHONE', 
--             'TF1545', 
--             'TF1545', 
--             'Corning Incorporated', 
--             'HTS2') 

create table #dataparser(documentpartid INT NULL, givenpartnumber NVARCHAR(50),   givenmanufacturer NVARCHAR(100) )

insert into #dataparser
SELECT documentpartid, 
       Isnull(affectedproduct, '') as givenpartnumber, 
       Isnull(companyname, '')  as givenmanufacturer
 FROM #temp 
WHERE ( categoryid = 517884 OR categoryid = 1110481 ) 
  AND [status] IS NULL 
union all
SELECT documentpartid, 
       Isnull(Substring(replacementpart, 0, 70), '') as givenpartnumber, 
       Isnull(replacementcompany, '')  as givenmanufacturer
FROM   #temp 
WHERE [status] IS NULL 
  AND replacementpart IS NOT NULL 


--CREATE CLUSTERED INDEX CIX_dataparser  ON #dataparser (TestCol1);   

INSERT INTO [dbo].partsdata 
            (rownumber, 
            givenpartnumber, 
            givenmanufacturer) 
select 0 rownumber, 
       givenpartnumber, 
       givenmanufacturer
  from #dataparser

select * from partsdata


drop table #temp
drop table #dataparser
drop table partsdata
6

using loop very slightly more but busier code, less succinct

USE sqlteam 

go 

IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
    DROP TABLE #temp

IF OBJECT_ID('tempdb..#dataparser') IS NOT NULL 
    DROP TABLE #dataparser

IF OBJECT_ID('partsdata') IS NOT NULL 
    DROP TABLE partsdata

IF OBJECT_ID('type_validationinput') IS NOT NULL 
    DROP TABLE type_validationinput

CREATE TABLE [dbo].partsdata 
  ( 
     batchid           NVARCHAR(50) PRIMARY KEY default Newid(), 
     rownumber         INT, 
     givenpartnumber   NVARCHAR(50), 
     givenmanufacturer NVARCHAR(100) 
  ) 

--select * from [dbo].PartsData 
CREATE TABLE [dbo].[type_validationinput] 
  ( 
     rownumber         INT, 
     givenpartnumber   NVARCHAR(50), 
     givenmanufacturer NVARCHAR(100) 
  ) 

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]) 
SELECT TOP 100000
        companyname = case when RIGHT(ABS(CHECKSUM(NEWID())%10000)+10000,4) % 2 = 0 then 'Nokia' else 'IPHONE' end,
        affectedproduct = 'RF' + RIGHT(ABS(CHECKSUM(NEWID())%10000)+10000,4),
		replacementpart = 'RFv1.1' + RIGHT(ABS(CHECKSUM(NEWID())%10000)+10000,4),
		replacementcompany = ac1.name,
		category = 'HTS' + ac1.name
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2


DECLARE @MaxValue INT = (SELECT Max(documentpartid)   FROM   #temp) 
DECLARE @Currentindex INT =0 
DECLARE @Rows table(rownumber int, givenpartnumber nvarchar(4000), givenmanufacturer nvarchar(4000))

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 [dbo].partsdata 
                  (rownumber, 
                   givenpartnumber, 
                   givenmanufacturer) 
      SELECT 0, 
             givenpartnumber, 
             givenmanufacturer 
      FROM   @Rows 

      SET @Currentindex = @Currentindex + 5000 

      DELETE @Rows 
  END
7

How long did that take on 100,000 rows?

8

About 2 seconds