Post reply

SSMA - Oracle to SQL Server inefficient code made worse - Fastest CURSOR type?

  • December 20, 2018 at 4:12 am

    #369733

    Okay, my Disclaimer first - I KNOW CURSORS ARE BAD - I fully subscribe to Jeff Moden's anti-RBAR pro-set based theory.

    However, SSMA (SQL Server Migration Assistant) has taken inefficient code (I believe Oracle is better at using cursors) and made it 1000 times worse.  Time, money, politics, resourcing, etc  means that a full re-write isn't possible...yet.

    Some pseudo-code:

    EXEC STORED_PROCEDURE1

          CURSOR1(Open/Close)

          CURSOR2 (Open/Close)

          CURSOR3(Open/Close)

    EXEC STORED_PROCEDURE2 (All of the following is inside of SP2)
              CURSOR4 (Open...  (This is a loop of approx. 23k rows)
              Create 100 temp tables (these were originally Table variables - thanks SSMA! - but it ran even slower with these)

    • CURSOR5 (Open/Close)
    • CURSOR6 (Open/Close)
    • CURSOR7 (Open/Close)
    • CURSOR8 (Open/Close)
    • CURSOR9 (Open/Close)
    • CURSOR10 (Open...

                       CURSOR11(Open/Close)
                       CURSOR12(Open/Close)
     Close 10)

               EXEC STORED_PROCEDURE3
              Close 4)

    Nasty isn't it? :crazy:  Not all cursors are hit in each loop, so some loops are quicker than others. Using temp tables instead of TV has helped but now I am looking at the cursor type and currently the best performing are all STATIC.  I have looked at this excellent article from Aaron Bertrand https://sqlperformance.com/2012/09/t-sql-queries/cursor-options and I have tried his recommendations LOCAL STATIC READ_ONLY FORWARD_ONLY & LOCAL FAST_FORWARD however these were slower.  I know they should all be set based, but right now I need to find the fastest cursor type.  I am thinking it would be a mixture of types?

    TIA

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • December 20, 2018 at 9:29 am

    #2016972

    Is it possible to move this to 2016 Development and T-SQL forum?

    Cheers

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply