June 8, 2015 at 4:13 am
Hello,
Maybe not exactly the right forum so excuse if I still ask the question: why is it that my SQL SP runs fast in SQL Management Studio, but slow in my .NET 4.0 application?
I experience it on several parts in my application, with different SP's (which all generate results within milliseconds in MMS). In my .NET application it sometimes takes up to 20 seconds before the data arrives, after which the webpage is built. We can see that in the Google Chrome debugger, so it is definitely not a problem of how the .NET application treats the data after receiving. It really takes so long before the data actually arrives. Some pages with SP's don't experience this at all and the webpage is very fast (so the setup of our SQL Server / Webserver should be okay). Other pages with SP's experience it every time, so it is not a random thing.
On the web some solutions were parameter sniffing, or clearing the SP execution plan cache; both didn't help us.
Is anyone else experiencing this behaviour, and what to do about this?
Thanks!
June 8, 2015 at 6:43 am
Assuming we're talking about an identical database, indexes and statistics and exactly the same parameters being passed so that you can guarantee that you ought to be seeing identical execution plans, the next thing that comes to mind is ANSI settings. Different ANSI settings can cause different execution plans to be generated. Check that. Also, if your queries against SSMS are done on the server, then you're not seeing network transfers, so that might be affecting things.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2015 at 6:47 am
and don't forget to check the connection... How do you connect to sql server from your app?
Do you use any connectors/ODBC/drivers etc?
June 8, 2015 at 7:06 am
The most detailed and comprehensive article on this subject can be found on Erland Sommarskog's site: http://www.sommarskog.se/query-plan-mysteries.html
-- Gianluca Sartori
June 8, 2015 at 7:11 am
Thanks for the feedback guys.
We nailed it down to the following: we looked at "running queries" and found out that there was a ASYNC_NETWORK_IO /
PREEMPTIVE_OS_WAITFORSINGLEOBJECT issue, and we found on google that that in most cases had to do with a client not consuming the data fast enough. The resultset on the clientside is transformed to JSON and it appeared that the used library was too slow. When we took that line out in a test all went as fast as expected. We're changing the library now and expect to have solved it this way.
Thanks again,
Raymond
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply