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

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:

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]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.sql