Home Forums SQL Server 2008 SQL Server 2008 - General SQL Function takes more than 2 hours to return a table.. RE: SQL Function takes more than 2 hours to return a table..

  • February 5, 2013 at 5:10 pm

    #1584673

    sanjaytiwari31 (2/4/2013)

    Can anybody tell me how to optimize this function or where is the problem.

    Yes I can but you're not going to like it. The code cannot be optimized no matter how much you play with it because of the nested cursors. It would be a far better thing sack the code entirely and rewrite it from the ground up without the cursors.

    Because of the length of the code, I've not done a serious deep dive on it but I agree with what has already been said... it looks like it could be resolved by a single relatively simple query across 4 tables. If no one in your shop can handle the conversion, your shop should get some hired help. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)