3

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;
2
  • there is also option to use 'select [columns] from id in (1544,4765,...)' . My best guess is that 400 queries will be noticeable slower than generating 'id in (...)' code. One problem is max query length (see :stackoverflow.com/questions/1869753/…) , do i have paper to prove that it is slower, no, this is just a comment Jun 10, 2014 at 8:47
  • 3
    My answer here might be worth a read.
    – Jon Seigel
    Jun 10, 2014 at 14:06

2 Answers 2

2

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.

1
  • Yes I agree. I will try WHERE IN() as has been mentioned by others and process it on the client side.
    – CrazyTim
    Jun 11, 2014 at 1:22
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.

1
  • Hi @David, the records I'm after can be retrieved in date order, however the ids are not date sequential, otherwise your solution for <first> <last> would be perfect!
    – CrazyTim
    Jun 11, 2014 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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