Post reply

Performance problem

  • October 28, 2013 at 10:28 am

    #1662091

    Do you get the same plan when run through the application?

    Do you always get the same plan when using different parameters?

    How different are the estimated to actual rows for each operation?

    It may be parameter sniffing that is causing the query to perform poorly via the application.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • October 28, 2013 at 10:29 am

    #1662092

    Check the properties of each index seek in the actual execution plan and see how often each is running. It takes a whole lot longer and uses a heck of a lot more resources to do (for example) 100,000 index seeks than it does to do a single scan on a 100,000 row table.

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

  • October 29, 2013 at 3:28 am

    #1662253

    Hi guys!

    Thanks for the input, it appears that a simple UPDATE STATISTICS did the trick.

  • October 29, 2013 at 8:21 am

    #1662374

    mbn 46383 (10/29/2013)

    Thanks for the input, it appears that a simple UPDATE STATISTICS did the trick.

    Statistics update invalidates the query plans for all tables whose stats were updated. I am guessing that if it was purely statistics you would have had similar performance when running the query in SSMS.

    If you had a "bad plan" due to parameter sniffing then when you updated the stats that plan was invalidated and the next time the query ran a new plan was compiled. This plan may have been compiled with different parameters that created a more optimal plan for the majority of parameters. If it was a bad plan due to parameter sniffing the issue may return.

    check out this post http://www.sqlsoldier.com/wp/sqlserver/misunderstoodandabusedupdatestatisticscommand

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

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

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