SQL Server Performance Forum – Threads Archive
SP works from QA but not from app
Hi there, We’ve got an application that calls a sp, it retrieves 20 rows and then for each row another sp is called from the client app.The query is subsecond to a second when running it from the query analyser but times out from the application using the same parameters. We’ve run a trace on the start and completion of stored procs and found that this particular stored proc doesn’t end and we inevitably time out but running from query analyser works a treat (with the same parameters). Is there any obvious factors that we could be missing, I don’t think it’s the network as other querys work fine. Thanks
Shaun
World Domination Through Superior Software
Do you have SET NOCOUNT ON turned on from within the SP? This won’t account for all of your problems, but it might account for part of the delay. Also, when you run the SP from the app (andt the SPs from within the main SP), are you calling it by its full name, i.e. object_owner.object_name? Using the full name will also help some, but again, probably not a lot. Until I say the query, the trace, and the execution plan, I don’t think I can offer much more help. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Check permissions and blocking
We are using SET NOCOUNT ON, I was under the impression this would actually be speed up the application by not having to return or calculate certain data regarding the number or rows returned? I’ll check the execution plan as advised, might find something there. As for the sp, it’s not being called with the object_owner which is something that’s been corrected but no change really. My main concern is why the sp executes really fast in QA but not from the app! The same queries executed, 1 is a secod the other times out. Cowboy, I’ll take your advice as well and see if there are any premission problems, I’m not sure what you mean by blocking though, do you mean locks and such? ‘*
An update on this, the sp we thought was the source of the problems wasn’t the one at all, the results from the sp are used to loop through and call other sp’s for each row, we’re using a firehose cursor in ADO to retrieve the first set of results, is it possible that the trace didn’t come up with the completion of the first sp because we were going through a firehose cursor and calling other sp’s and since these other sp’s timedout we never saw the completion of the first sp? Then again we didn’t see any of the other sp’s executing in the trace either? Thanks
Shaun World Domination Through Superior Software
I wonder, if perhaps, that you are not tracing all the relevant events. You might want to verify that you have selected all the events from the Events tab of the Profiler and see what happens. On the SET NOCOUNT ON, this does help performance (my mistake if I implied otherwise). ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Yeah sorry Brad, when I re-read your comment you were suggesting that I make sure NOCOUNT was on, initially I thought that it meant setting NOCOUNT ON was possibly causing a delay, oops. On a lighter note I think we’ve found the problem and it wasn’t really related to the database. It seems the customers had decreased the swap file size to something ridiculously low on the webserver and it seemed to hamper this sp in particular because it was returning 20 rows and then we were retrieveing further recordsets for each of those twenty which put a burden on the server, since the database was on it’s own server, when we ran the sp from QA it worked fine becuase we were doing it from our machines to the database server so we didn’t suffer from the web servers increased paging to swap file.. thanks for all the help though, just goes to show our investigation skills need a little work instead of just jumping to conclusions about the db. Cheers
Shaun World Domination Through Superior Software
Bah, people always blame the DB/SQL Server [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br />Edit: people as in non-DBA’s [<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]<br /><br />/Argyle
Sorry to plug, but I need to in this case because it could help shed light on the problem. We have created a product called NetworkSmart QA that monitors SQL Performance of an application across the network. The twist is that we are like a packet sniffer and we rebuild all of your calls. We can show the delay difference (server and transmission) from both your app and Query Analyzer. We even show what SQL content is being sent in every packet allowing you to debug exactly what is happening. You can grab a 15 day fully functional eval at http://www.leadbyte.com/products/nsqa. I hope this helps.
]]>