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...)
5
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:
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.
2