How to enhance query performance when loop within temp table ? RRS feed

  • 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 ,
    ('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
    DELETE @Rows
    RowNumber ,
    GivenPartNumber ,
    select TOP 5000 DocumentPartID , isnull(AffectedProduct,''), isnull(CompanyName,'') FROM #Temp where
    (CategoryID = 517884 or CategoryID = 1110481) and (DocumentPartID > @Currentindex) and [Status] is null
    RowNumber ,
    GivenPartNumber ,
    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

    Thursday, February 13, 2020 2:16 AM

All replies

  • User753101303 posted


    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 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 :

    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