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

ahmedsalah

Member
Joined
Sep 26, 2018
Messages
14
Programming Experience
3-5
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 :

SQL:
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
 
Last edited by a moderator:

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
Looks like a SQL question rather than a C# question.
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
Anyway, just scanning that SQL script, two things came to mind:
  • DELETE is slow because indexes need to be rebuilt.
  • Should use SELECT INTO instead of INSERT.
See:
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
844
Location
UK
Programming Experience
10+
Looks like a SQL question rather than a C# question.
There is a database question section on the forum where these questions are allowed with or without C# code. ;)
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
844
Location
UK
Programming Experience
10+
Oh dear. He's one of them...

Actually looks like he got a fairly half shabby answer on code project. I don't have mod rights over there to improve the answer, since I deleted my account. Looks like he is getting enough help elsewhere so. (y)
 
Top Bottom