Results 1 to 4 of 4

Thread: Slow Code ????

  1. #1
    Join Date
    Feb 2007
    Posts
    33

    Question Unanswered: Slow Code ????

    THE GOAL

    TO TEST THE VALUE OF THE LOAD PAY TYPE FOR EACH RATED LINE AND MAKE SURE THE ONE THAT HAS BEEN SELECTED BY THE SYSTEM ALREADY IS THE HIGHEST ONE

    THIS CODE RUNS FINE SEPERATLY IT TAKES A SECOND OR SO BUT IN A STORE PROCEDURE WITH THE @CompDiv and @OrdNum being supplied it runs for minutes.

    Any suggestions to speed this code up would be great.

    ASSUME ALL VARIABLES USED HAVE BEEN DECLARED ABOVE

    Code:
    SET @CompDiv = 'GLPC-TRANS'
    SET @OrdNum = '00022728-0'
     
     
    SET @NoneStationNum = 'NONE'
    SELECT @DLSeq = DLSeq, @PUSeq = PUSeq, @OriginLocation = OriginLocation, @DLCustomerID = DLCustomer, @DestLocation = DestLocation FROM [CSITSS].[dbo].[Ordinfodt] WHERE Companydiv = @CompDiv and OrderNumber = @OrdNum
    SET @ActualLoadPayType =(SELECT [LoadPayType] FROM [CSITSS].[dbo].[Orders] WHERE CompanyDiv = @CompDiv and Deleted = 0 and OrderNumber = @OrdNum)
    SET @ActualLoadPayValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE DELETED = 0 and CompanyDiv = @CompDiv and LoadPayType = @ActualLoadPayType)
    SET @NumOfOtherStops = @DLSeq - @PUSeq - 1
    SET @Counter = 1
    IF @NumOfOtherStops > 0
    BEGIN
    DELETE FROM [CSITSS].[tdecker].[GLP_LoadPayTest]
    WHERE CompanyDiv = @CompDiv and OrderNumber = @OrdNum
     
    SELECT @RateTableUsed = [RateTableUsed] FROM [CSITSS].[dbo].[Ordrate] WHERE Companydiv = @CompDiv and OrderNumber = @OrdNum and [Sequence]= 1
    WHILE @Counter < 4
    BEGIN
    SET @SOCustomerID1 = NULL 
     
     
    WHILE @SOCustomerID1 IS NULL
    BEGIN
     
        SELECT @SOCustomerID1 = [CustomerID] FROM [CSITSS].[dbo].[Orderdt] WHERE CompanyDiv = @CompDiv and OrderNumber = @OrdNum and LineType = 'SO' and [Sequence] = @Counter + 2
     
    IF @SOCustomerID1 IS NULL
        BEGIN
            SET @COUNTER = @COUNTER + 1
        END
    END
     
     
     
    SELECT @SOCust1StatNum = [StationNum], @SOCust1Location = [Location] FROM [CSITSS].[dbo].[Customer] WHERE CompanyDiv = @CompDiv and CustomerID = @SOCustomerID1
    SELECT @SOCust1LoadPayType = [LoadpayCompany] FROM [CSITSS].[dbo].[Ratedt] WHERE DELETED = 0 and [TableID]=@RateTableUsed and [Origin]=@OriginLocation and [Destination]=@SOCust1Location and [StationNum]=@SOCust1StatNum
    IF @SOCust1LoadPayType IS NULL
    BEGIN
    SELECT @SOCust1LoadPayType = [LoadpayCompany] FROM [CSITSS].[dbo].[Ratedt] WHERE DELETED = 0 and [TableID]=@RateTableUsed and [Origin]=@OriginLocation and [Destination]=@SOCust1Location and [StationNum]=@NoneStationNum
    END
    SELECT @SOCust1LoadPayValue = [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE DELETED = 0 and CompanyDiv = @CompDiv and LoadPayType = @SOCust1LoadPayType
     
    IF @SOCust1LoadPayValue IS NULL 
    BEGIN
    SET @SOCust1LoadPayValue = 0
    END
    ELSE 
    BEGIN
    INSERT INTO [CSITSS].[tdecker].[GLP_LoadPayTest]([CompanyDiv], [OrderNumber], [LoadPayType], [LoadPayValue], [Sequence])
    VALUES(@CompDiv, @OrdNum, @SOCust1LoadPayType, @SOCust1LoadPayValue, @COUNTER)
    END
        SET @Counter = @Counter + 1 
    END
     
    SELECT @DLCustStatNum = [StationNum] FROM [CSITSS].[dbo].[Customer] WHERE CompanyDiv = @CompDiv and CustomerID = @DLCustomerID
     
    SELECT @DLCustLoadPayType = [LoadpayCompany] FROM [CSITSS].[dbo].[Ratedt] WHERE DELETED = 0 and [TableID]=@RateTableUsed and [Origin]=@OriginLocation and [Destination]=@DestLocation and [StationNum]=@DLCustStatNum
     
    IF @DLCustLoadPayType IS NULL
    BEGIN
    SELECT @DLCustLoadPayType = [LoadpayCompany] FROM [CSITSS].[dbo].[Ratedt] WHERE DELETED = 0 and [TableID]=@RateTableUsed and [Origin]=@OriginLocation and [Destination]=@DestLocation and [StationNum]=@NoneStationNum
    END
     
    SELECT @DLCustLoadPayValue= [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE DELETED = 0 and CompanyDiv = @CompDiv and LoadPayType = @DLCustLoadPayType
     
    INSERT INTO [CSITSS].[tdecker].[GLP_LoadPayTest]([CompanyDiv], [OrderNumber], [LoadPayType], [LoadPayValue], [Sequence])
    VALUES(@CompDiv, @OrdNum, @DLCustLoadPayType, @DLCustLoadPayValue, 4)
     
    SELECT @MaxLoadPayValue = MAX(LoadPayValue) FROM [CSITSS].[tdecker].[GLP_LoadPayTest] WHERE CompanyDiv = @CompDiv and OrderNumber = @OrdNum
    SELECT @MaxLoadPayType = LoadPayType FROM [CSITSS].[tdecker].[GLP_LoadPayTest] WHERE CompanyDiv = @CompDiv and OrderNumber = @OrdNum and LoadPayValue = @MaxLoadPayValue
     
    IF @MaxLoadPayType = @ActualLoadPayType
    BEGIN
    SET @MaxLoadPayTest = 1
    INSERT INTO [CSITSS].[tdecker].[GLP_LoadPayTest]([CompanyDiv], [OrderNumber], [LoadPayType], [LoadPayValue], [Sequence])
    VALUES(@CompDiv, @OrdNum, @MaxLoadPayType, @MaxLoadPayValue, 5)
    END
    ELSE
    BEGIN
    SET @MaxLoadPayTest = 0
    INSERT INTO [CSITSS].[tdecker].[GLP_LoadPayTest]([CompanyDiv], [OrderNumber], [LoadPayType], [LoadPayValue], [Sequence])
    VALUES(@CompDiv, @OrdNum, @ActualLoadPayType, @ActualLoadPayValue, 5)
    PRINT 'Order Number ' + @OrdNum
    Print 'Line 3 Results'
    PRINT 'Customer ID = ' + @SOCustomerID1
    PRINT 'Station Number = ' + @SOCust1StatNum
    PRINT 'Location = ' + @SOCust1Location
    PRINT 'LoadPayType = ' + @SOCust1LoadPayType
    PRINT @SOCust1LoadPayValue
    Print 'Line 4 Results'
    PRINT 'Customer ID = ' + @SOCustomerID2
    PRINT 'Station Number = ' + @SOCust2StatNum
    PRINT 'Location = ' + @SOCust2Location
    PRINT 'LoadPayType = ' + @SOCust2LoadPayType
    PRINT @SOCust2LoadPayValue
    Print 'Line 5 Results'
    PRINT 'Customer ID = ' + @SOCustomerID3
    PRINT 'Station Number = ' + @SOCust3StatNum
    PRINT 'Location = ' + @SOCust3Location
    PRINT 'LoadPayType = ' + @SOCust3LoadPayType
    PRINT @SOCust3LoadPayValue
    Print 'DL Customer Results'
    PRINT 'Customer ID = ' + @DLCustomerID
    PRINT 'Station Number = ' + @DLCustStatNum
    PRINT 'Location = ' + @DestLocation
    PRINT 'LoadPayType = ' + @DLCustLoadPayType
    PRINT @DLCustLoadPayValue
    PRINT 'Rate Table Used'
    PRINT @RateTableUsed
    PRINT 'Actual Load Pay Type = ' + @ActualLoadPayType
    PRINT 'Max Load Pay Type = ' + @MaxLoadPayType
    PRINT 'Max Load Pay Type Test Results'
    PRINT @MaxLoadPayTest
    END
     
    IF @MaxLoadPayTest IS NULL
    BEGIN
    SET @MaxLoadPayTest = 0
    INSERT INTO [CSITSS].[tdecker].[GLP_LoadPayTest]([CompanyDiv], [OrderNumber], [LoadPayType], [LoadPayValue], [Sequence])
    VALUES(@CompDiv, @OrdNum, @ActualLoadPayType, @ActualLoadPayValue, 5)
    END
     
     
     
     
     
    END
     
    ELSE
    BEGIN
     
    SET @MaxLoadPayTest = 1
    DELETE FROM [CSITSS].[tdecker].[GLP_LoadPayTest] WHERE CompanyDiv = @CompDiv and OrderNumber = @OrdNum and [Sequence] = 5
    INSERT INTO [CSITSS].[tdecker].[GLP_LoadPayTest]([CompanyDiv], [OrderNumber], [LoadPayType], [LoadPayValue], [Sequence])
    VALUES(@CompDiv, @OrdNum, @ActualLoadPayType, @ActualLoadPayValue, 5)
    END
    Last edited by pootle flump; 05-28-07 at 05:32. Reason: Changed <code> tags to [code] tags

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I would think that this code is slow because of the while loops. it's better to do things in a set based way than use loops (which is equivalent to a cursor)

    also this code is very hard to read as you are not using any meaningful formatting. I merely scanned it and found the WHILEs, stopped scanning at that point. you need to wrap it in [ code ] [ /code ] tags (without the spaces)

    finally, addressing the forum with ALL CAPS is bad form. it's not INTERNATIONAL CAPS LOCK DAY yet.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Eliminate loops (WHILEs and cursors), and you should see up to a hundred-fold increase in speed.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Ahhhh...it's nice to be reminded of the ole' days, and again realize how far it is from procedural thinking to set-based thinking....but how fun it was the day the epiphany came.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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