Multiple SELECT vs single SELECT over TCIP

Question

My question is a performance one:

I need to select roughly 400+ rows, i know their ids.

currently i loop in code and fire off multiple queries over tcp/ip - because its easy:

...
select [columns] where id = 1544;
select [columns] where id = 4765;
select [columns] where id = 3544;
select [columns] where id = 4090;
select [columns] where id = 5060;
...

Would it be more efficient on SQL server to fire off a single query for a date range instead (the rows I'm after are date-sequential, but we currently don't have a date field in that table), keeping in mind the overhead of multiple transactions vs a single transaction:

select [columns] where date between x AND y;

Answer 1

The round trip time from the client to the server per query starts to add up with your approach. Even if it is only 1ms per query, you are burning almost half a second for no gain. Combine the statements into one. At the very least submit them as a single batch and process the individual results sets in the application.

Answer 2

With SQL server (and, I suspect, pretty much everything else) querying for a range will be more efficient than querying for many individual rows, even when working locally. The range query will likely result in an index seek followed by a short scan through the few subsequent the index pages (followed by any other page lookups needed), then many single selects will result in many individual index seeks (followed by any other page lookups needed).

The "where in (<static list>)" option mentioned by user503207 may perform similarly to the date based range query in SQL Server, try it and see what query plan results, though in some DBMSs (mySQL for instance, unless that has improved in that area since I last used it) such queries can be quite inefficient (sometimes resulting in a full index scan).

As your IDs are in sequential order by date and you know which ones you are needing (or you wouldn't be able to put together the list of individual selects) then you could instead sort them when producing your query to find the range and use where id between <first> and <last> which would also avoid needing to add the date column.

When considering doing the same remotely the query performance difference will be the same if all the selects are sent as a single batch, though you will see a little more bandwidth used because transferring many small resultselts is less efficient than a single larger one containing the same rows. If you are submitting the selects individually then you will see a significant slowdown as you are adding network latency to each individual query instead of to the batch as a whole.