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.