Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: Is there a better way to skin this cat?

    I have a table with about 50k rows. It's taking about an hour to process 10k rows of it. The outer cursor has about 30k rows, the inner between 1 and 7 rows.

    I know cursors ar slow, especially the inner update cursor. I could do the same thing with a single cursor, and just keep track to see when @carInit or @carNumb change and reset @tripID.

    I have no clue how this all could be accomplished using a set based approach. If anybuddy has a bright idea, I'd love to hear it. Any indexes i should be using that I'm not?

    Thanks,
    Carl

    Code:
    ALTER PROCEDURE  dbo.sp_FPS_CarSupplied AS
    DECLARE @carInit CHAR(4)
    DECLARE @carNumb CHAR(10)
    DECLARE @tripID INTEGER
    DECLARE @currStat CHAR(3)
    DECLARE @isSupStatus BIT
    
    DECLARE curCar CURSOR FOR
    SELECT CAR_INIT, CAR_NUMB FROM T_FPS_CCO_CAR_HIST
    GROUP BY CAR_INIT, CAR_NUMB
    ORDER BY COUNT(*) DESC
    
    OPEN curCar
    FETCH NEXT FROM curCar
    INTO @carInit, @carNumb
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE curSetTrip CURSOR FORWARD_ONLY FOR
        SELECT CAR_STAT_CD FROM T_FPS_CCO_CAR_HIST
        WHERE CAR_INIT = @carInit AND CAR_NUMB = @carNumb
        ORDER BY CAR_STAT_DT DESC, CAR_STAT_TM DESC 
        FOR UPDATE OF CAR_TRIP_ID, SUP_CNT_IND
    
        OPEN curSetTrip
        FETCH NEXT FROM curSetTrip
        INTO @currStat
        
        SET @tripID = 1
        SET @isSupStatus = 0
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @currStat IN ('SPT','DEL','CP') AND @isSupStatus = 0
            BEGIN
                SET @isSupStatus = 1
                UPDATE T_FPS_CCO_CAR_HIST 
                SET CAR_TRIP_ID = @tripID, SUP_CNT_IND = 1
                WHERE CURRENT OF curSetTrip
            END
            IF @currStat IN ('REC','REL') AND @isSupStatus = 1
            BEGIN
                SET @isSupStatus = 0
                SET @tripID = @tripID + 1
                UPDATE T_FPS_CCO_CAR_HIST 
                SET CAR_TRIP_ID = @tripID
                WHERE CURRENT OF curSetTrip
            END
            IF @currStat = 'PER' AND @isSupStatus = 1
            BEGIN
                UPDATE T_FPS_CCO_CAR_HIST SET IS_SUP_ERR = 1
                WHERE CAR_INIT = @carInit AND CAR_NUMB = @carNumb
                AND CAR_STAT_CD IN ('SPT','DEL','CP')
                AND CAR_TRIP_ID = @tripID
            END
    
            FETCH NEXT FROM curSetTrip
            INTO @currStat
        END
        CLOSE curSetTrip
        DEALLOCATE curSetTrip
    
        FETCH NEXT FROM curCar
        INTO @carInit, @carNumb
    END
    CLOSE curCar
    DEALLOCATE curCar
    Here's the table:
    Code:
    CREATE TABLE [T_FPS_CCO_CAR_HIST] (
    	[SUP_CNT_IND] [bit] NULL ,
    	[IS_SUP_ERR] [bit] NULL ,
    	[CAR_TRIP_ID] [int] NULL ,
    	[CAR_INIT] [nvarchar] (4) COLLATE Latin1_General_CI_AI NULL ,
    	[CAR_NUMB] [nvarchar] (10) COLLATE Latin1_General_CI_AI NULL ,
    	[UPD_DTTM] [smalldatetime] NULL ,
    	[CAR_STAT_CD] [nvarchar] (3) COLLATE Latin1_General_CI_AI NULL ,
    	[CAR_STAT_DT] [smalldatetime] NULL ,
    	[CAR_STAT_TM] [nvarchar] (8) COLLATE Latin1_General_CI_AI NULL ,
    	[LOAD_EMPTY] [nvarchar] (1) COLLATE Latin1_General_CI_AI NULL ,
    	[CCO_NBR] [nvarchar] (6) COLLATE Latin1_General_CI_AI NULL ,
    	[CCO_TYP] [nvarchar] (1) COLLATE Latin1_General_CI_AI NULL ,
    	[REJ_CD] [nvarchar] (2) COLLATE Latin1_General_CI_AI NULL 
    ) ON [PRIMARY]
    GO
    
    
    CREATE 
      INDEX [ix_carid] ON T_FPS_CCO_CAR_HIST ([CAR_INIT], [CAR_NUMB])
    WITH
        DROP_EXISTING
    ON [PRIMARY]
    
    CREATE 
      INDEX [ix_dtm] ON T_FPS_CCO_CAR_HIST ([CAR_STAT_DT], [CAR_STAT_TM])
    WITH
        DROP_EXISTING
    ON [PRIMARY]
    
    CREATE 
      INDEX [IX_T_FPS_CCO_CAR_HIST] ON T_FPS_CCO_CAR_HIST ([CAR_TRIP_ID])
    WITH
        DROP_EXISTING
    ON [PRIMARY]

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are correct that you do not need a cursor to do this.
    I would expect a performance boost of between 2 and 3 orders of magnitude (100-1000 times as fast) by converting this to an UPDATE statement using CASE functions to implement your logic.
    Give it your best shot, and post what you come up with.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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