.NET Query issue



  • Anybody ever had a problem where a stored procedure will run in sub second in query analyser then time out in .NET at runtime?? (Exactly the same code)

    and if so... anybody know how to fix it??


  • ♿ (Parody)

    You need to be more specific. Short of that, some general guidelines:

    • run a profiler trace to see the difference in queries
    • see if the problem is parameter sniffing (google it)
    • look at the connection string differences

     



  • @PeterStephenson said:

    Anybody ever had a problem where a stored procedure will run in sub second in query analyser then time out in .NET at runtime?? (Exactly the same code)

    and if so... anybody know how to fix it??

    I have run into that problem quite a few times since upgrading to 2.0.  You won't like the solution that usually works for me:  do the chicken dance, kick some of the machines(or tap lightly on the side, whatever your style is), recompile the stored procedures multiple times, and reboot.  Mix up this combination of activities while humming "We Are the Champions", and eventually the problem will clear itself up.  Also, make sure you used "set nocount on" at the top of your procedure.  I've had some success with hanging a rubber chicken over the servers; real chickens are totally unnecessary and may soil your boxes.

    p.s. - I'm pretty sure adding "nocount", recompiling procedures, and rebooting stuff are the only really effective elements of the above fix, but YMMV.

    Lastly, don't forget to check to make sure you're running the pages and procedures you think you're running on the servers you think you're using.  If I had a nickel...



  • @Oscar L said:

    p.s. - I'm pretty sure adding "nocount", recompiling procedures, and rebooting stuff are the only really effective elements of the above fix, but YMMV.



    This might actually work. At my first job, we had a dot-matrix-printer that regulary jammed during a long printout. That was until we placed a big hammer next to it, telling it "do that again and...".
    The next long printout worked perfectly fine.


  • ♿ (Parody)

    @ammoQ said:

    At my first job, we had a dot-matrix-printer that regulary jammed during a long printout. That was until we placed a big hammer next to it, telling it "do that again and...".
    The next long printout worked perfectly fine.

    Be careful implenting this approach: I lost a rather expensive (at the time) VCR as a result of this. And honestly, I don't think it had a very positive effect to the morale of the other household appliances.



  • @Alex Papadimoulis said:

    @ammoQ said:

    At my first job, we had a dot-matrix-printer that regulary jammed during a long printout. That was until we placed a big hammer next to it, telling it "do that again and...".
    The next long printout worked perfectly fine.

    Be careful implenting this approach: I lost a rather expensive (at the time) VCR as a result of this. And honestly, I don't think it had a very positive effect to the morale of the other household appliances.


    Sometimes you have to show them who's boss.


  • @Alex Papadimoulis said:

    You need to be more specific. Short of that, some general guidelines:

    • run a profiler trace to see the difference in queries
    • see if the problem is parameter sniffing (google it)
    • look at the connection string differences

    Will try your suggestions next time im at the machine

    Ok a few more details, sorry for being vague earlier I was rushing to get out the door ;)

    I developed a stored procedure in Query Analyser, and was very careful to make sure it ran in an acceptable time (with the specific parameters being passed, sub second)

    I then call this stored procedure in my .net application- time out!

    I dont think its a connection issue as there are other SP calls in the .net (same page, same connection)that run fine
    Also I am definately using the same parameters as i have ran the actual query (exception handing will print the query to the browser if on a dev server)

    There are only 2 queries that time out. These queries have a parameter that specifies how it should look for a certain field, effectiely it can run one of 3 different (but very simple) queries into a table variable that is then used in the main part of the SP

    This is a recurring issue, and it has in the past been solved by dropping, recreating and executing the SP again before using the web app, which leads me to believe its to do with being pushed out of the cache- but this makes no sense (to me at least) with the working in QA + not .NET app.... however this time, a drop + recreate hasnt fixed it.. and to be honest this "solution" is not acceptable ;) so im looking for a better way before I have to MTP this stuff ;)

    (trying to dodge a "if the server breaks run sp_recreateTheOtherSps" WTF here guys ;) )



  • My best bet would be parameter sniffing problem.

    Declare local variables in you sp and set them to values of parameters. Use local varables in your sp instead of parameters.

    nonDev



  • @PeterStephenson said:

    (trying to dodge a "if the server breaks run sp_recreateTheOtherSps" WTF here guys ;) )

    Post the better solution when you find it, please.  My servers are colocated, and it's a long drive just to slap a machine around.  Seriously, check the nocount flag on that procedure and all the dependent procedures, too.



  • ok we "solved" the issue ;)

    After several midnight chanting sessions we believe we finally exorcised the demon ><

    Seriously all we did is drop the SP, comment out soem fields, re create- it worked
    we readded the fields 1 by 1 until they were all back and it still works fine! no changes to the ACTUAL code of the SP

    (and i had previously tried a drop and recreate)

    We have no idea why this did it! Anyone?



  • @PeterStephenson said:

    (trying to dodge a "if the server breaks run sp_recreateTheOtherSps" WTF here guys ;) )



    Are you prefixing your stored procedures with "sp" or "sp_"?  This may or may not be part of the issue.  User stored procedures should not be prefixed with "sp", since SQL Server views these as system procedures.  If you attempt to call a procedure prefixed with "sp", the database will search the system procedures first (I think in the master database) and then if its not found it will look in the current database. 

    Besides the performance penalty of searching the system procedures first, you could also be introducing ambiguity.  If your prefixing with "sp", it is possible that the user procedure just happens to conflict with a system procedure.

    Larry



  • @lpope187 said:

    @PeterStephenson said:

    (trying to dodge a "if the server breaks run sp_recreateTheOtherSps" WTF here guys ;) )



    Are you prefixing your stored procedures with "sp" or "sp_"?  This may or may not be part of the issue.  User stored procedures should not be prefixed with "sp", since SQL Server views these as system procedures.  If you attempt to call a procedure prefixed with "sp", the database will search the system procedures first (I think in the master database) and then if its not found it will look in the current database. 

    Besides the performance penalty of searching the system procedures first, you could also be introducing ambiguity.  If your prefixing with "sp", it is possible that the user procedure just happens to conflict with a system procedure.

    Larry


    This is a very good point, at my workplace developers used to always create stored procedures with sp_ as the prefix....they have switched now to "prc"



  • @Oscar L said:

    @PeterStephenson said:

    Anybody ever had a problem where a stored procedure will run in sub second in query analyser then time out in .NET at runtime?? (Exactly the same code)

    and if so... anybody know how to fix it??

    I have run into that problem quite a few times since upgrading to 2.0.  You won't like the solution that usually works for me:  do the chicken dance, kick some of the machines(or tap lightly on the side, whatever your style is), recompile the stored procedures multiple times, and reboot.  Mix up this combination of activities while humming "We Are the Champions", and eventually the problem will clear itself up.  Also, make sure you used "set nocount on" at the top of your procedure.  I've had some success with hanging a rubber chicken over the servers; real chickens are totally unnecessary and may soil your boxes.

    p.s. - I'm pretty sure adding "nocount", recompiling procedures, and rebooting stuff are the only really effective elements of the above fix, but YMMV.

    Lastly, don't forget to check to make sure you're running the pages and procedures you think you're running on the servers you think you're using.  If I had a nickel...



    oscar, this has to be the funniest post I have ever seen that actually had valid information in it! 


  • @iowacoder said:

    @Oscar L said:

    @PeterStephenson said:

    Anybody ever had a problem where a stored procedure will run in sub second in query analyser then time out in .NET at runtime?? (Exactly the same code)

    and if so... anybody know how to fix it??

    I have run into that problem quite a few times since upgrading to 2.0.  You won't like the solution that usually works for me:  do the chicken dance, kick some of the machines(or tap lightly on the side, whatever your style is), recompile the stored procedures multiple times, and reboot.  Mix up this combination of activities while humming "We Are the Champions", and eventually the problem will clear itself up.  Also, make sure you used "set nocount on" at the top of your procedure.  I've had some success with hanging a rubber chicken over the servers; real chickens are totally unnecessary and may soil your boxes.

    p.s. - I'm pretty sure adding "nocount", recompiling procedures, and rebooting stuff are the only really effective elements of the above fix, but YMMV.

    Lastly, don't forget to check to make sure you're running the pages and procedures you think you're running on the servers you think you're using.  If I had a nickel...



    oscar, this has to be the funniest post I have ever seen that actually had valid information in it! 

    Glad you appreciate it.  I find it therapeutic to laugh at the mistakes, er... my friends make.



  • @PeterStephenson said:

    ok we "solved" the issue ;)

    After several midnight chanting sessions we believe we finally exorcised the demon ><

    Seriously all we did is drop the SP, comment out soem fields, re create- it worked
    we readded the fields 1 by 1 until they were all back and it still works fine! no changes to the ACTUAL code of the SP

    (and i had previously tried a drop and recreate)

    We have no idea why this did it! Anyone?

    I'm getting a deja vu here.  I seem to remember something like that going on in my last foggy moments of desparation fixing the same problem.  Is there anything that would tell you the procedure was definitely recompiling as you added each line?  I've never had the leisure time to carefully observe since it of course only happens when I deploy to production, but my gut feeling is that for some reason the procedure isn't recompiling until some magic flag resets itself.



  • @Alex Papadimoulis said:

    You need to be more specific. Short of that, some general guidelines:

    • run a profiler trace to see the difference in queries
    • see if the problem is parameter sniffing (google it)
    • look at the connection string differences

     

    Parameter Sniffing: How I paid dearly for ignoring Alex's posts.



  • @Oscar L said:

    @PeterStephenson said:

    (trying to dodge a "if the server breaks run sp_recreateTheOtherSps" WTF here guys ;) )

    Post the better solution when you find it, please.  My servers are colocated, and it's a long drive just to slap a machine around.  Seriously, check the nocount flag on that procedure and all the dependent procedures, too.

     

    finally managed to reliably fix this by forcing the Sproc to recompile every time it is run (add with recompile after params b4 the "as")

    Only thing i found that made any difference!



  • @PeterStephenson said:

    @Oscar L said:
    @PeterStephenson said:

    (trying to dodge a "if the server breaks run sp_recreateTheOtherSps" WTF here guys ;) )

    Post the better solution when you find it, please.  My servers are colocated, and it's a long drive just to slap a machine around.  Seriously, check the nocount flag on that procedure and all the dependent procedures, too.

     

    finally managed to reliably fix this by forcing the Sproc to recompile every time it is run (add with recompile after params b4 the "as")

    Only thing i found that made any difference!

    That might explain why my nocount "solution" only works some of the time.  Must've been forcing a recompile.  Thanks for the tip.  I think 2005 is getting a little too smart for my liking.  I don't remember having this problem with 2000.



  • @iowacoder said:


    This is a very good point, at my workplace developers used to always create stored procedures with sp_ as the prefix....they have switched now to "prc"

    Grrrr -  I hate hungarian notation.  I've made sure that everyone in my shop has stopped using btnBlarg, txtField, frmForm, and spSomething.  What happens when I change option buttons to checkboxes at a client's request?  I have to change *every* little bit of code referring to them, or optFirstOption is actually a checkbox.  I label everything by its purpose - the control named Submit (guess what?) submits the form.  UserName contains the (guess what?) username, and Logout loggs the user out.

    <FONT color=#ff0000>(Bring the flames!!!)</FONT>



  • @Albatross said:

    @iowacoder said:


    This is a very good point, at my workplace developers used to always create stored procedures with sp_ as the prefix....they have switched now to "prc"

    Grrrr -  I hate hungarian notation.  I've made sure that everyone in my shop has stopped using btnBlarg, txtField, frmForm, and spSomething.  What happens when I change option buttons to checkboxes at a client's request?  I have to change *every* little bit of code referring to them, or optFirstOption is actually a checkbox.  I label everything by its purpose - the control named Submit (guess what?) submits the form.  UserName contains the (guess what?) username, and Logout loggs the user out.

    <font color="#ff0000">(Bring the flames!!!)</font>



    I agree with you, I too hate hungarian notation, but it is a corporate policy here, and the darn schools beat it into you.

    I have ran into more confusion with hungarian notation than with anything else......


  • @Albatross said:

    Grrrr -  I hate hungarian notation.  I've made sure that everyone in my shop has stopped using btnBlarg, txtField, frmForm, and spSomething.  What happens when I change option buttons to checkboxes at a client's request?  I have to change every little bit of code referring to them, or optFirstOption is actually a checkbox.  I label everything by its purpose - the control named Submit (guess what?) submits the form.  UserName contains the (guess what?) username, and Logout loggs the user out.

    <font color="#ff0000">(Bring the flames!!!)</font>



    As much as I hate hungarian notation (believe me I cringe when I see it), in database design its standard practice for certain objects.  Typically you see SEQ_, IX_, and PK_ for sequences, indexes and primary keys.  I guess since these things are typically hidden from developers and really only visible to DBAs its not too bad.  As long as the database doesn't use hungarian notation for tables, fields, views, and procs; I'm fine with it.

Log in to reply