3

I'm using MS SQL 2008 R2. I have been using a series of OR and have an issue where my app periodically give the following error:

Exception message: Timeout expired. The timeout period elapsed prior to obtaining
a connection from the pool. This may have occurred because all pooled connections
were in use and max pool size was reached. 

My SQL skills are fairly basic but I have a situation where I need to make selections from a database. Originally the selections were simple (either a single filter or select all)

Select column_names... From Table Where column = value

Or

Select column_names... From Table

Now I need to select where column could be one of a series of items. Which would be best to use

Select column_names... From Table Where column = value1 Or column = value2 Or column = ValueX...

Or

Select column_names... From Table Where column In (value1, value2, valueX...) 
  • 1
    See stackoverflow.com/questions/3074713 – Kilian Foth Feb 17 '15 at 9:58
  • IN makes code easier to read and maintain. Go with IN. I'd still suggest using IN due to better looking code. As far as I know IN is not significantly faster performance wise, in MS SQL. – Sejanus Feb 17 '15 at 9:58
  • 2
    Have you compared the execution plan of each? – user40980 Feb 17 '15 at 12:34
  • 5
    You may be experiencing an XY problem - using IN vs OR is not likely to be the cause of the connection pool timeout errors – Dan Pichelman Feb 17 '15 at 14:58
  • 5
    It should be worth noting that the error message has nothing to do with the efficiency of the query. You are getting a Connection timeout, which occurs well before you statement is executed. – TGnat Feb 17 '15 at 14:59
7

SQL Server will typically optimize the IN to the same as the multiple OR statements. Take this for example:

use AdventureWorks;
go

select BusinessEntityID
from HumanResources.Employee
where BusinessEntityID = 153
or BusinessEntityID = 25
or BusinessEntityID = 37;

select BusinessEntityID
from HumanResources.Employee
where BusinessEntityID in (153, 25, 37);

Looking at the execution plan I can see that SQL Server treated them as like statements and used the same execution for them both:

enter image description here

Ultimately, though, you need to do the same exercise in your environment with your queries and your data (if you have differing plans for these different variations, feel free to post the post execution plans in your question and I can analyze). And of course like all things relational data, you need to ensure that you have the proper querying and indexing in place in order to optimize the data retrieval.

The other side of that is readability/maintainability. Consistency is king here for development teams, and if this is a solo code base then I'd tack on personal preference to that. IN is a lot less verbose with characters, and the typical choice.

Just a note, though, if these conditional checks get out of control (hundreds of hundreds of elements in the IN clause, I've seen it before way too often) you might need to step back at what you are really doing and refactor your logic.

Your actual error is because of a different cause though...

It is worth noting though that your actual error message is a connection timeout, not a command timeout. It looks as though you've exhausted all pooled connections in your connection pool for the application. Take a look at this blog post I wrote on connection pooling. That should explain why you're getting that error and how to fix. It is very possible that other sessions are running too long, and not closing or disposing of the connection to return it back to the pool. You would most definitely need to take a spearheaded approach in troubleshooting your connection timeout issues due to exhausted connection pooling.

| improve this answer | |
  • Thanks... I have updated my selects to utilise IN based on what I have read in the various updates/comments. I will look at connection pool exhaustion. I've been using old fashioned sql commands for my queries but my predecessor used Linq2SQL and it looks like he was not disposing of his DataContext objects. – Mych Feb 17 '15 at 16:58
  • You're quite welcome, and yes that would most definitely be a contributor to that issue. – Thomas Stringer Feb 17 '15 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.