Lots of small queries vs one big query

[listerofsmeg01]

Hi,

Pretty new to PHP and MySQL.

I have a page on my site that displays a lot of information from
various tables. Currently I have lots of small PHP wrapper functions
around SQL queries to get each bit of information. This results in
maybe 10 queries to display one page, but they are all very small and
simple, and it keeps the PHP looking nice too as they are simple
function calls to get each piece of info, which can be called from any
page in my site.

However, after reading up on SQL a bit, I am wondering whether I should
be looking at just generating one large query at the start, and using
joins etc to lump all the relevant info together. This complicates the
PHP as this means a custom query for each page of my site depending on
what needs to be displayed, but I am wondering if it is worth it if I
get a fair performance gain.

Can any gurus point me in the right direction? Is there a "proper" way
to do this sort of thing?

Many thanks.

[Jerry Stuckle]

li************@hotmail.com wrote:

Hi,

Pretty new to PHP and MySQL.

I have a page on my site that displays a lot of information from
various tables. Currently I have lots of small PHP wrapper functions
around SQL queries to get each bit of information. This results in
maybe 10 queries to display one page, but they are all very small and
simple, and it keeps the PHP looking nice too as they are simple
function calls to get each piece of info, which can be called from any
page in my site.

However, after reading up on SQL a bit, I am wondering whether I should
be looking at just generating one large query at the start, and using
joins etc to lump all the relevant info together. This complicates the
PHP as this means a custom query for each page of my site depending on
what needs to be displayed, but I am wondering if it is worth it if I
get a fair performance gain.

Can any gurus point me in the right direction? Is there a "proper" way
to do this sort of thing?

Many thanks.

That's what relational databases are all about - having related data in
different tables and joining them together.

Sure you'll have different queries on different pages. That's because
you need different data on different pages, don't you? The rest of your
code isn't the same - why should the queries be?

Let the database do what it's best at!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Bart op de grote markt]

li************@hotmail.com schreef:

Hi,

Pretty new to PHP and MySQL.

I have a page on my site that displays a lot of information from
various tables. Currently I have lots of small PHP wrapper functions
around SQL queries to get each bit of information. This results in
maybe 10 queries to display one page, but they are all very small and
simple, and it keeps the PHP looking nice too as they are simple
function calls to get each piece of info, which can be called from any
page in my site.

However, after reading up on SQL a bit, I am wondering whether I should
be looking at just generating one large query at the start, and using
joins etc to lump all the relevant info together. This complicates the
PHP as this means a custom query for each page of my site depending on
what needs to be displayed, but I am wondering if it is worth it if I
get a fair performance gain.

Can any gurus point me in the right direction? Is there a "proper" way
to do this sort of thing?

Many thanks.

Normally if you join tables, it's because they need to be joined. E.g.
if you want to display the private details of different persons from
PRIVATE_DETAILS_TABLE and all the messages that these persons have sent
from MESSAGES_TABLE, these to tables have to be joined to know what
messages belong to what person. That's logical.
Of course u can first query the PRIVATE_DETAILS_TABLE and based on that
data query the MESSAGES_TABLE with a loop, but that's not so clean and
I guess much slower since you have to query the DB again and again,
while u only need to query it once.

Greets,

Bart

[jinxidoru]

You are generally going to get a performance gain from grouping smaller
queries into one query if possible. Be careful though, because this is
not always the case. There are situations where the cost of the joins
and the possible locking of the tables may decrease performance. These
cases are rare though.

Now, just because you get better performance does not mean that you
should go out and change your code immediately. You mentioned that
your code is currently easy to maintain. This carries a lot of value.
There are certain situations where speed is critical and must be
achieved at any cost, even if it increases the difficulty of the code
maintenance. There's always that balance.

Of course, do not think that the best performance is only achieved
through the most cryptic code. There might be a way to get the best of
both worlds. Is there a way that you can use the functions you have
right now to, rather than perform queries, aggregate onto a query. I
have done this many times where a function call does not generate a
query of its own but simply adds to a query that I am generating. Once
I have called all my necessary methods, I then run the query and
extract the data.

Hopefully this has been somewhat helpful.

[Dr.Zoidberb]

There is no "proper" way for all systems. You have to strike a balance
that works for your system. The query cache stores the results of
queries and returns the stored result rather than performing the query
again, provided the tables underneath have not changed.

So if all of your tables update infrequently then you would be able to
get some performance gains out of combining all your queries into one.
However, if one of those tables updates frequently that same combined
query could potentially decrease performance depending on the
complexity of the joins. If some update frequently and some don't then
you could get some gain combining the queries that update infrequently,
and leaving the others alone.

See where I'm going? It's database specific. You need to analyze how
often things update to see whether or not you might gain something by
combining queries. Like others have said, you also have to keep in mind
maintenance.

[listerofsmeg01]

Just wanted to say thanks for everyone who replied. Much appreciated
and have taken on board many of your comments.

In the end I have grouped some similar queries together rather than the
whole lot, and cached the results in a php class. I then access the
values through that class which just does the one fetch initially, then
feeds me similar values should I request them without doing a lookup.

Thanks again.