January 12, 2008 at 9:04 pm
When running this particular stored proc in Mgmt Studio it returns data in approx 4 - 8 seconds, when running the same stored proc via ASP.NET using the SQL .Net Client it takes 264+ seconds.
Anyone encountered anything like this?
TIA
Dave
January 14, 2008 at 12:37 am
I'd first of all suspect the way the proc's parameters are defined in the vb.net app.
( http://www.sqlservercentral.com/scripts/Miscellaneous/31639/ )
Avoid implicit conversions !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2008 at 7:38 am
It may have to do with the query plan being cached on the first run and reused, even though it's non-optimal for the particular data. See: "How SQL Server Chooses the Type of Join" ( http://www.sqlservercentral.com/articles/Performance+Tuning/2863/%5B/url%5D )
January 14, 2008 at 5:43 pm
I found the exact same problem here
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=295646
Applying the build which it is apparently fixed in (3175) did nothing at all.
Anyway, glad to have it fixed.
January 15, 2008 at 9:14 am
I'd bet that its not SQL server. Run profiler and you'll probably see some differences. Are you using a DataReader or DataSet in the web app?
I've seen an ADO apps that used server side cursors to read a recordset, when you switched the same query to client side cursors it screamed.
This article might be helpful
http://msdn.microsoft.com/msdnmag/issues/04/08/DataPoints/
Good luck and be sure to post a fix if you find one
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply