Single database call vs multiple database calls Performance

What is the difference in performance of making a single database (MySql) call that 开发者_如何学Pythonretrieves 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)

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.

What is heavier - a pound of apples or a bike?
Queries aren't equal. You cannot just compare them by numbers.
A query must be sensible in the first place. Doing its job the best way possible, not being imaginably "faster". That's the only reason to prefer one query to another.

I hope you meant different queries, not just a single query called 10 times.
Of course, running the same query multiple times should be avoided, as any other repetitive action - that's one of the basic rules in the art of programming.

And once your query/queries are right, you got to make them fast. And to answer the question how to do that - learn about profiling.

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

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.

OLD thread I know... thought I would lend my 2 cents anyway :)

There are some unlikely situation where multiple calls CAN be necessary... Any ANYONE, please correct me if I am wrong here.

I ran into a situation where I was pulling information from the database and compiling into a simple CSV... Well eventually, there was SOO much data tied to MANY tables, members, orders, items in an order, the product, different product versions, etc... Running a single query literally took forever and you can argue that the DB shema should probably have been a bit different, but the way I fixed this was to run a simply query for the data... then run individual queries and flush() the data to the front-end.

While I agree in 99% of situations that 1 query is always best, there are a few unlikely scenarios where multiple can be helpful.

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.