Post reply

performance of a query

  • July 5, 2005 at 10:21 am

    #86744

    Hi,

    I have a query stated below.I have used @variables to get the values in variables they will be used while inserting in other table.

    SELECT @fltPresentFoodValue=[Ratings: Food Score],

            @fltPresentDecorValue=[Ratings: Decor Score],

             @fltPresentServiceScore=[Ratings: Service Score],

      @fltFoodscore=[Ratings: FDS Score],

     @fltFoodCost=[Ratings: Cost],  

              FROM TempRating INNER JOIN TempItemHist

            ON TempItemHist.ItemID = TempRating.[Item ID]  where TempItemHist.ItemID = @itemid

    There are many more values which i am fetching in  variables.

    This query is running in a loop.

    When i run this query the performance will degrade.

    IF i remove the variables it is executed very fast.

    I want to know how can i do the same operation without degrading the performance.

    Kindest Regards,

    Pavas

    Dream The Dream
    Explore the World
    Experince The Exhilaration

  • July 5, 2005 at 10:25 am

    #570926

    Don't use a loop and do it set based...

    Can we see the rest of the proc's code, the table definition, some sample data and the expected output?

  • July 5, 2005 at 10:41 am

    #570928

    In addition to Remi's suggestion:

    I assume that the @itemid is a passed in variable (before the AS).  Instead of using this variable set an internal (after the AS) variable.  There is a potential performance problem (not well known/documented) that could also be affecting performance.



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 5, 2005 at 11:24 am

    #570948

    Please not that one again .

  • July 6, 2005 at 6:42 am

    #571196

    AJ... you said "Instead of using this variable set an internal (after the AS) variable.  There is a potential performance problem (not well known/documented) that could also be affecting performance."

    Would you be nice and give us a little more on this??

    THANK YOU!!!! 

    Nicolas

  • July 6, 2005 at 7:00 am

    #571205

    Search this site for parameter sniffing, you'll find the info he's talking about.

    Anyways can you post the rest of the code, this is where you'll get the biggest performance boost in my opinion.

  • July 6, 2005 at 3:35 pm

    #571589

    Nicolas,

    Believe it or not that is all I have.  A co-worker ran across this and we have implemented same proc with using the input params vs input > internal and they perform better.

    Sorry,



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 6, 2005 at 10:35 pm

    #571635

    Yup we're still gonna need to see that code if we want to optimize it.

Viewing 8 posts - 1 through 7 (of 7 total)

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