Post reply

Odbc Management Studio very interesting

  • June 14, 2011 at 2:34 am

    #251866

    I have procedure firstly ı try to run it in exec odbc connection and it is waiting a long time. When ı run that procedure it

    comes about 2 second and then ı try it in exec test with recompile it is come about 2 second when ı try to run it

    without recompile hint exec test it dosent come.And ınteresting ı run it in manegment studio it is comming 2 second with recompile

    hint or without thanks.....

  • June 14, 2011 at 5:48 am

    #1338280

    SQL SERVER 2008 R2 Native Client

    SQL SERVER 2008 R2

  • June 14, 2011 at 6:09 am

    #1338294

    sounds like "parameter sniffing"

    typical symptoms:

    1. SSMS with hardcoded values = fast

    2. With Recompile = fast

    3. run from an application with values other than you tested with = slow.

    there's a lot of posts on here on how to handle it...search a bit for "parameter sniffing" and read some of the many posts on it;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 14, 2011 at 6:20 am

    #1338301

    it can be i know parameter snifing i don t know syhptoms now i can solve....thanks much and much

  • June 14, 2011 at 6:36 am

    #1338315

    i'll leave the research and testing to you, but here's a quick list, in the order that i would try first:

    1. use the OPTION(OPTIMIZE FOR @p1 UNKNOWN) or OPTION(OPTIMIZE FOR @p1='specific value') , where @p1 would be all the parameters that get passed to your proc...you have 5 params, you might want all 5 in the OPTIMIZE directive.

    2. declare local variables inside the proc, and pass the exposed parameters to the local variables, since the optimizer cannot assume a value for local variables.

    3. Create the proc with the WITH RECOMPILE directive.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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