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=idGeee...
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 onOR
- 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...
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
