Single database call vs multiple database calls Performance

Question

What is the difference in performance of making a single database (MySql) call that retrieves 10 results versus making 10 single db calls to retrieve those results individually?

If one is better than the other, is there a way I can go about to test the performance between the two? (I'm just curious because in the future if I have another question on performance, I could use the suggested way to test it out myself)

Answer 1

A single call will always be faster than several calls for the same data. Simply the network turn around and latency alone is a component, but also the start up and tear down of the SQL processing will have some impact.

Answer 2

the single call is better. Less round trips between you and the DB, less processing involved. Less data transfer for the recordsets (well, only a little).

Testing the performance. You can obviously test it programmatically - your client app can write the start/stop times between the 2, that's easy and shows the performance difference from a client point-of-view. If you want to see the server performance, you can enable the general log (or binary log) and see the performance of each query.

Answer 3

What is heavier - 10 apples or a bike?
Queries aren't equal!. You cannot just compare it by numbers!
A query must be sensible. Period. Not imaginable faster, but sensible. Doing it's job on it's place. That's the only reason to prefer one query over another.

I hope you meant different queries, not just single query called 10 times.
Of course, running equal queries several times should be avoided, as well as any other repetitive action - it's one of basic programming rules.

And to answer your future performance question -
Learn profiling

BENCHMARK <query> and EXPLAIN <query> are tools for mysql profiling and performance tuning

Answer 4

You can easily do performance testing between the two. You would do something like the following pseudocode:

start = CurrentTime()
do 1000 times
  for i from 1 to 10
    query_for_one_row(i)
  end
end
end = CurrentTime()
time_for_individual_queries = (end - start) / 1000

start = CurrentTime()
do 1000 times
  query_for_rows(1,10)
end
end = CurrentTime()
time_for_bulk_queries = (end - start) / 1000

Note that I run each one a thousand times, because usually when performance testing, the thing you are testing is too quick to observe a difference consistently unless you run it repeatedly. You will, of course, want to tweak for your particular situation.