> The queries themselves are simple, normally drawing information from one
> table with few conditions or in the most complex cases using joins on
> two table or sub queries. These behave very well and always have, the
> problem is that these queries take place in rather large amounts due to
> the dumb nature of the scripts themselves.
Hum, maybe this "dumb" thing is where to look at ?
I'm no expert, but I have had the same situation with a very dump PHP
application, namely osCommerce, which averaged about 140 (!!!!!) queries
on a page !
I added some traces to queries, and some logging, only to see that the
stupid programmers did something like (pseudo code):
for id in id_list:
select stuff from database where id=id
Geee...
I replaced it by :
select stuff from database where id in (id_list)
And this saved about 20 requests... The code was peppered by queries like
that. In the end it went from 140 queries to about 20, which is still way
too much IMHO, but I couldn't go lower without an extensive rewrite.
If you have a script making many selects, it's worth grouping them, even
using stored procedures.
For instance using the classical "tree in a table" to store a tree of
product categories :
create table categories
(
id serial primary key,
parent_id references categories(id),
etc
);
You basically have these choices in order to display the tree :
- select for parent_id=0 (root)
- for each element, select its children
- and so on
OR
- make a stored procedure which does that. At least 3x faster and a lot
less CPU overhead.
OR (if you have say 50 rows in the table which was my case)
- select the entire table and build your tree in the script
It was a little bit faster than the stored procedure.
Could you give an example of your dumb scripts ? It's good to optimize a
database, but it's even better to remove useless queries...