Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
2.50/5 (2 votes)
I've got a bit of a strange problem...
We have a table-valued function in our database. It's quite complex and I can't really tell you what it does, except that filters and even ordering is dependent on input parameters (without using dynamic SQL!). The function calls a few other table-valued functions (all in one SELECT statement). It's not really important what it all does.

So the problem, when called from our software (using plain old ADO.NET (SqlConnection, SqlCommand, SqlDataReader...) when a page opens the query runs fine (gets its data in less than a second). When we change just one parameter (a date filter) the query times out! From less than a second to more than 30 by changing a parameter...
Now when I run that same function with the same parameters from SSMS it runs under a second again! So then we go from timeout to less than a second. And after that it will run fine in the software as well.

I guess making a query plan is complicated because the query is quite complicated, but why does it run fine in SSMS and not from our software? And why does changing a single parameter make the query timeout? Ideas on where to look? What to search for? I'm completely at a blank here...

Thanks.
Posted
Comments
PIEBALDconsult 13-Feb-15 11:55am    
I don't know, but I agree it seems unusual.
One point I'd like to make though is that a function or view probably shouldn't be concerned with ordering -- that's a responsibility of the application.
Sander Rossel 13-Feb-15 11:58am    
Yeah, I prefer ordering in my application too, but it shouldn't make the function come to a grinding halt either...
Slacker007 13-Feb-15 12:07pm    
Date filters get executed first, I believe. By changing the date filter parameter, are you removing it? If so, I would think that is your problem, or part of it.
Sander Rossel 13-Feb-15 14:41pm    
Won't it simply look at what indexes are available and pick the one it can work with best?
Slacker007 13-Feb-15 14:58pm    
I have personally witnessed queries with a date parameter/filter, execute quite fast but as soon as you remove that date parameter or filter, the query would take forever, even in SSMS.

I am not saying this is your situation, but felt like mentioning my experience.

1 solution

This is often down to differences in the SET options between SSMS and ADO.NET, which causes SQL to select a different execution plan. ARITHABORT is a common one - it's usually on in SSMS, but off for ADO.NET connections.

You can see the current options by looking at @@OPTIONS and performing some bit tests:
http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/[^]

You can change most of the default options in the properties of the database - ARITHABORT is "Arithmetic Abort Enabled".

Quite a lot of other possibilities are covered in this article[^] by Erland Sommarskog[^].
 
Share this answer
 
Comments
Sander Rossel 13-Feb-15 14:34pm    
Awesome, thanks! I actually had a similar problem a few months back and wasn't able to solve it back then. This may be just what I need!
I'm going to look at this first thing next monday :)
Sander Rossel 16-Feb-15 9:48am    
The article by Erland Sommarskog helped me find the problem and solutions. One problem was indeed ARITHABORT, but that was not all. Parameter sniffing was the real culprit. Created a local variable inside my function, assigned it the value of the parameter and used that in the query. Problem solved. Took me almost an entire day, but it was worth it :-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900