Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Slow Cursor

  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: Slow Cursor

    I have a sub procedure that gets call about 2M times. It's taking nearly 20 minutes to complete. If I remark out the following sub, the job runs in about 2 minutes. I tried remarking just the INSERT INTO, but that didn't make a difference, so it's not that, it's the damn FETCH, and a bit of the SET statement just prior to it. Any ideas what I can do to speed it up?
    Thanks,
    Carl

    Code:
    ALTER     PROCEDURE [dbo].[proc_SAHSGenSnroDetail] @currHumpDtm DATETIME, @pos INT, @humpRate SMALLINT, @combID INT AS
    DECLARE @carInit CHAR(4)
    DECLARE @carNbr CHAR(10)
    DECLARE @lstHumpDtm DATETIME
    DECLARE @snroScr SMALLINT
    
    DECLARE curSnroDetail CURSOR FOR
    SELECT CAR_INIT, CAR_NBR, LST_HMP_DTM FROM TSA_HS_OBJ_TRN 
    WHERE WKLD_ID = @pos ORDER BY TRK_SEQ_NBR
    OPEN curSnroDetail
    FETCH NEXT FROM curSnroDetail INTO @carInit, @carNbr, @lstHumpDtm
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @currHumpDtm <= @lstHumpDtm
            BEGIN
                SET @snroScr = 1
            END    
        ELSE
            BEGIN
                SET @snroScr = 0
            END            
    
        INSERT INTO TSA_HS_SNRO_DTL (COMB_ID, WKLD_ID, CAR_INIT, CAR_NBR, SNRO_HMP_DTM, SNRO_SCR)
        VALUES (@combID, @pos, @carInit, @carNbr, @currHumpDtm, @snroScr)
    
    
        SET @currHumpDtm = DATEADD(s, (CONVERT(DECIMAL,1)/CONVERT(DECIMAL,@humpRate))* 3600, @currHumpDtm)
    
        FETCH NEXT FROM curSnroDetail INTO @carInit, @carNbr, @lstHumpDtm
    END
    CLOSE curSnroDetail
    DEALLOCATE curSnroDetail
    Here's the source table for the cursor

    Code:
    CREATE TABLE [dbo].[TSA_HS_OBJ_TRN] (
    	[WKLD_ID] [int] NOT NULL ,
    	[TRK_SEQ_NBR] [int] NOT NULL ,
    	[CAR_INIT] [char] (4) COLLATE Latin1_General_CI_AI NOT NULL ,
    	[CAR_NBR] [char] (10) COLLATE Latin1_General_CI_AI NOT NULL ,
    	[OS_LGTH_FT] [int] NULL ,
    	[GRS_TON] [int] NULL ,
    	[YBLK] [char] (6) COLLATE Latin1_General_CI_AI NULL ,
    	[TA_DTM] [datetime] NULL ,
    	[OBJ_DEP_TRN] [char] (10) COLLATE Latin1_General_CI_AI NULL ,
    	[LST_HMP_DTM] [datetime] NULL 
    ) ON [PRIMARY]
    And here's the table that the insert goes into

    Code:
    CREATE TABLE [dbo].[TSA_HS_SNRO_DTL] (
    	[COMB_ID] [int] NULL ,
    	[WKLD_ID] [int] NULL ,
    	[CAR_INIT] [char] (4) COLLATE Latin1_General_CI_AI NULL ,
    	[CAR_NBR] [char] (10) COLLATE Latin1_General_CI_AI NULL ,
    	[SNRO_HMP_DTM] [datetime] NULL ,
    	[SNRO_SCR] [smallint] NULL ,
    	[CAR_CNT] [smallint] NULL 
    ) ON [PRIMARY]

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Any ideas what I can do to speed it up?
    Rewrite it to set based
    -----------------
    KH


  3. #3
    Join Date
    Jun 2003
    Posts
    269
    --deleted----
    Last edited by mallier; 02-22-06 at 06:30.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What is the process that provides these values?

    Code:
    @currHumpDtm DATETIME, @pos INT, @humpRate SMALLINT, @combID
    I need that to eliminate the CURSOR
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by khtan
    Rewrite it to set based
    May not be possible, as there are some indications that his logic needs to be processed sequentially.

    Carlmal, what is the purpose of this statement:
    Code:
    SET @currHumpDtm = DATEADD(s, (CONVERT(DECIMAL,1)/CONVERT(DECIMAL,@humpRate))* 3600, @currHumpDtm)
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    I should've mentioned, the CURSOR that uses TSA_HS_OBJ_TRN is usually 1k-2k rows, and like I said, the output table TSA_HS_SNRO_DETL will end up with .8-2M rows.

    Brett: This sub gets called by a proc that goes thru 720 possible combinations (6 factorial), this supplies the parameters to the sub proc_SAHSSnroDtl. The proc runs in less than a minute if I disable this crappy sub procedure.

    Blindman: I do have to process sequentially, it's a workload optimizer in a push/pull type process, you have 25 groups of things to do, which order do you do them in? We try rearranging the 1st 6 of the 25, hence the 720 possibilities, but I still have to carry the times over to the remaining groups.

    Code:
    SET @currHumpDtm = DATEADD(s, (CONVERT(DECIMAL,1)/CONVERT(DECIMAL,@humpRate))* 3600, @currHumpDtm)
    is where I increment the times the objects get processed for this particular sort combination. The process has a flow rate, say 120/hour, when I process 1 object, I bump the time 1/120 * 3600 seconds in an hour.

    We give things a score based on did we speed up or slow down the object, that's the @snroScr. I hope I'm 'splaining it OK.

    What's "Set Based" anyway? I didn't see it in the Books Online.

    Thanks guys,
    Carl

  7. #7
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    I dont see anything specific which is taking lots of resources.
    Check creating an index on WKLD_ID (if not created already)..
    try out parsing the sp and execute it individually...

    how much time is this sp taking when you execute it individually??,if not more time then check if any dependencies are there which are taking more time..

    Hope this helps u out..

    regards,
    Pavan.

  8. #8
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by pavan_kashyap
    I dont see anything specific which is taking lots of resources.
    Check creating an index on WKLD_ID (if not created already)..
    try out parsing the sp and execute it individually...

    how much time is this sp taking when you execute it individually??,if not more time then check if any dependencies are there which are taking more time..

    Hope this helps u out..

    regards,
    Pavan.
    I hope u have seen him using cursor.that itself is enough to chew all ur resources.
    Last edited by mallier; 02-22-06 at 11:34.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    cursors should very rarely be used in application code. i am pretty sure you can do this as a

    INSERT INTO ...
    SELECT...

    with a couple of case statements but I have not put in under the microscope.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    His business logic will not allow an efficient set-based solution, as the values inserted into both SNRO_HMP_DTM and SNRO_SCR are non-linear (depend upon the values of previous calculations).

    I think he needs to reevaluate the algorithm being used to prioritize jobs. Is it an industry standard, or is it something you can change? What do SNRO_HMP_DTM and SNRO_SCR represent, and is it absolutely necessary to calculate them at the same time as the optimization process?
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    I have to keep a running tab of the @currHumpDtm, but I don't have to do the scoring now, I can calculate that after the job is run.

    If I can create a function that references the previous row of the results, I could do it all without a CURSOR perhaps. Is that possible?

    Thanks,
    Carl

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by carlmal
    If I can create a function that references the previous row of the results, I could do it all without a CURSOR perhaps. Is that possible?

    What does previous mean?

    See below
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    Say you wanted a field that incremented by 1 each row. The previous row was say 11, so the current row is 12.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Add an identity column or datetime column. That will allow you to do what you want to do.

    As long as you don't use SELECT * in your code you should be fine
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by carlmal
    If I can create a function that references the previous row of the results, I could do it all without a CURSOR perhaps. Is that possible?
    This can be done without a great deal of difficulty. The resulting query will not particularly fast, but it will far outpace what you are doing now.
    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
  •