Post reply

replace cursor ?????????????????

  • December 28, 2007 at 4:39 am

    #181707

    How can I speed this up ?

  • December 28, 2007 at 6:10 am

    #763232

    Ok, this is a lot of code to go through so we may need your help a bit.

    What part of this is giving you a performance problem?

    The first section appears to drop a bunch of tables and then copy new versions of them from another database. Are you dropping them and re-creating them because schema changes happen constantly? If not, and you are getting a performance problem in this area, it is often much more efficient to update existing records, insert new ones, and remove deleted records. If only a small percentage of your records changed this can make a big difference. You can use SSIS for this and there is a free TableDifference component on http://www.SQLBI.com that works pretty well for large tables.

    The second section looks like it is trying to copy all new records for a group of tables from one database to another. I notice a couple of important things. The first is that you are moving data in chunks of 8000 records and then waiting a second between each piece. This is "weird" in T-SQL and I am not sure it gives you any benefit other than possibly allowing you to manage the soze of your transaction log. Moving everything in a single batch may be more efficient. At the very least, I don't think the WaitFor does anything other than add time to your process. It also looks like you may miss records if your total record count is not divisible by 8000, but I may have missed that piece. The biggest improvement you could do here is to allow some parallel processing. This is all nice with the dynamic nature of it, but you could have this process print out the necessary T-SQL and run them from several connections all at once. This would also give you the option of tuning each one appropriately for the table being copied. If you did not want to spend that kind of time, I would siggest you simply create two versions of this section and have one loop through tables starting with letters A-M and the second for tables starting with N-Z. Run them both at the same time and you will likely see a drastic performance increase.

    Looking at the remaining code, my suggestions for the previous section pretty much apply. I think whoever wrote this tried to make something really dynamic with the least amount of actual T-SQL but sacrificed performance to do it. Your simplest solution will to parallel process.

  • December 28, 2007 at 1:10 pm

    #763401

    raym (12/28/2007)

    How can I speed this up ?

    Ray, the title of your post hit the nail on the head... get rid of the cursors, plain and simple. I'm not going to take the time to rewrite them for you because, well, I'd normally get paid for that 😉

    The real key is to figure out what you want the code to actually do... then, throw all of the original code away and start over using good set based practices. That's precisely what I'd do.

    --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)

  • December 31, 2007 at 10:01 am

    #763727

    you should not name user stored procedures sp_

    I'd suggest a T-SQL training course which will help you to understand set based ops.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • January 1, 2008 at 4:35 pm

    #763892

    which training course would you recommend colin? any in particluar

    Gethyn Elliswww.gethynellis.com

  • January 2, 2008 at 8:38 am

    #764069

    I agree with Jeff. Get rid of the cursor. My personnel belief is that cursors are the tools invented by the evil....;)

    colin Leversuch-Roberts (12/31/2007)

    you should not name user stored procedures sp_

    That is a good advice. There is a known performance hit incurred when user objects use “sp_”

    SQL Server will always first look for a system object matching the called object name. If not found it will look for a user object. The performance hit is the result of an imposed lookup order for stored procedures that carry the prefix.

    This might not seem to be a big performance hit. But like the saying goes "Every Penny counts"....

    -Roy

  • January 2, 2008 at 9:01 pm

    #764247

    My personnel belief is that cursors are the tools invented by the evil....

    Heh... HOT DAMN! A kindred spirit! 😀

    --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)

  • January 2, 2008 at 9:32 pm

    #764251

    Iam sure many have already told u this but once again just to reinfore the point. Remove the cursor and use SET based logic.

    I have done this earlier and results have been spectaclular.

    "Keep Trying"

  • January 3, 2008 at 2:14 pm

    #764579

    I, like Jeff, would be happy to rework this for you at my normal work-from-home consulting rate (which is a good bit less than my onsite rate). And I will ensure that you benefit from my efforts with some mentoring as well! Otherwise this is way past the normal efforts required for a forum assistance. 😀

    Actually, are you an independent consultant Jeff?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 3, 2008 at 5:24 pm

    #764619

    Actually, are you an independent consultant Jeff?

    Only on occasion...

    --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)

Viewing 10 posts - 1 through 9 (of 9 total)

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