Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    19

    Exclamation Unanswered: Fine tune the script to run faster.

    Currently i have the below script, The below script runs more than 30 hrs to complete the process.The records which i am updating is 1000000. Please let me know how to fine tune the script in order to complete the process as soon as possible.




    SET NOCOUNT ON

    DECLARE @Counter INT,
    @MaxCounter INT,
    @ShipmentId varchar(21),
    @ShipIdentifier INT,
    @MaxIdent INT

    SELECT @Counter = MIN(Counter)
    FROM ArchiveTracking.dbo.ArchiveProgress_Shipment WITH (NOLOCK)
    WHERE Archived = 0

    SELECT @MaxCounter = MAX(Counter)
    FROM ArchiveTracking.dbo.ArchiveProgress_Shipment WITH (NOLOCK)
    WHERE Archived = 0

    PRINT 'Update/convert identifiers in ArchiveProgress_Shipment for duplicate shipments from counter = '
    + CAST(@Counter AS VARCHAR(10)) + ' to counter = ' + CAST(@MaxCounter AS VARCHAR(10)) + '.'

    WHILE @Counter <= @MaxCounter
    BEGIN
    SELECT @ShipmentId = Shipmentid
    ,@ShipIdentifier = ShipIdentifier
    FROM ArchiveTracking.dbo.ArchiveProgress_Shipment WITH (NOLOCK)
    WHERE Counter = @Counter

    SELECT @MaxIdent = ISNULL( MAX(ShipIdentifier), 0 )
    FROM ArchiveTracking.dbo.Shipment
    WHERE ShipmentId = @ShipmentId

    UPDATE ArchiveTracking.dbo.ArchiveProgress_Shipment
    SET NewIdentifier = @MaxIdent + @ShipIdentifier
    WHERE Counter = @Counter

    SET @Counter = @Counter + 1
    END

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Without seeing the data in question, I would suggest making sure that you have sufficient indexes for the SELECT statements. Alternatively, try building a view that holds all the information that you need to pull into variables, and using that - at least that way each iteration of the loop would have half the number of SELECT statements.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'll never get this run fast until you get rid of that loop.
    Try this:
    Code:
    SET NOCOUNT ON
    
    DECLARE @Counter INT,
    @MaxCounter INT,
    @ShipmentId varchar(21),
    @ShipIdentifier INT,
    @MaxIdent INT
    
    SELECT	@Counter = MIN(Counter)
    FROM	ArchiveTracking.dbo.ArchiveProgress_Shipment
    WHERE	Archived = 0
    
    SELECT @MaxCounter = MAX(Counter)
    FROM ArchiveTracking.dbo.ArchiveProgress_Shipment
    WHERE Archived = 0
    
    PRINT 'Update/convert identifiers in ArchiveProgress_Shipment for duplicate shipments from counter = '
    + CAST(@Counter AS VARCHAR(10)) + ' to counter = ' + CAST(@MaxCounter AS VARCHAR(10)) + '.'
    
    ;with Calculations as
    		(SELECT	Counter,
    				Shipmentid,
    				ShipIdentifier,
    				ISNULL(MAX(ShipIdentifier), 0 ) as MaxIdent
    		FROM	ArchiveTracking.dbo.ArchiveProgress_Shipment
    				left outer join ArchiveTracking.dbo.Shipment on ArchiveProgress_Shipment.ShipmentId = Shipment.ShipmentID
    		WHERE	ArchiveProgress_Shipment.Counter between @Counter and @MaxCounter
    		GROUP BY ArchiveProgress_Shipment.Counter,
    				ArchiveProgress_Shipment.Shipmentid,
    				ArchiveProgress_Shipment.ShipIdentifier)
    UPDATE	ArchiveTracking.dbo.ArchiveProgress_Shipment
    SET		NewIdentifier = Calculations.MaxIdent + Calculations.ShipIdentifier
    FROM	ArchiveTracking.dbo.ArchiveProgress_Shipment
    		inner join Calculations
    			on ArchiveProgress_Shipment.Counter = Calculations.Counter
    			and ArchiveProgress_Shipment.ShipmentID = Calculations.ShipmentID
    			and ArchiveProgress_Shipment.ShipmentIdentifier = Calculations.ShipmentIdentifier
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2010
    Posts
    19
    K will try this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •