stored procs slow after SQL2K upgrade

  • I know this has been brought up many times but I am not finding anything fast...

    We upgraded from SQL7 to SQL2K sp3a this past weekend and all went mostly well.

    The problem is a small number of our stored procs are runnining super long.

    A stored proc that was running around 35 seconds has now been running between 5 - 11 minutes.

    I ran sp_updatestats on the database and I am running the Optimize portion of our DB Maint Plan tonite which I assume does a DBREINDEX on all the indexes in the database.

    Is there anything I should be looking at?

    Thank you very much.

    RandyB

  • I would also force a recompile on all of your stored procs, it's possible that the execution does not match what it would have done in sql7.



    Shamless self promotion - read my blog http://sirsql.net

  • Thanks Nicolas.

    I tried that on your advice but it didn't help.

  • Have you run execution plans for the procs that are causing the problems to see if there are any glaring problems like table scans etc?



    Shamless self promotion - read my blog http://sirsql.net

  • I did run execution plans and they used index seeks extensively.

    Here is what we ended up doing.

    The developer was joining several tables; he used 5 Left outer joins and 4 inner joins.

    He changed the first 4 joins to inner joins leaving 8 inner joins and one outer join.

    This dropped the query execute time from 5 - 8 minutes down to 5 seconds.

    I never realized outer joins were that much of a performance hit.

    Again, thanks for your help.

  • You have to bear in mind that an outer join will return every record from the left table, whether there is a matching record in the right table or not. You have a couple of outer joins and things can really start getting out of control.



    Shamless self promotion - read my blog http://sirsql.net

Viewing 6 posts - 1 through 5 (of 5 total)

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