What does query overhead mean?

[Aarmin]

I've noticed some add-ons have a statement that they add queries.

More importantly, how would this affect little me on a shared server? How many queries are too many?

The two add-ons that I'm currently testing add '0' query overhead, but I would still like to somewhat understand this.

Keep in mind, I'm clueless about this even after searching the lands of the internet

 

[tenants]

A good performing add-on will use the minimum number of queries it can, this can sometimes be 0 (and it's nice when it is). 0 is what Add-on developers should be aiming for, but it's not always possible depending on the nature of the add-on.

It's also worth noting that some queries are just bad by design (just 1 query can be very slow, and some queries can be very fast).

If you want to know the overhead of any plug-ins, turn on debug mode (turn this off after use), open your config.php (yourForum/library/config.php) and put this line into it:

$config['debug'] = true;

I would do this on a local test environment, since I don't think setting debug mode to true on a real environment is a good idea (if you do, do it for only a short time, since these pages will be picked up by indexing spiders and cached.. )


At the bottom of the page, you will then see something like this (the values will differ):
Timing: 0.0208 seconds Memory: 1.491 MB DB Queries: 6


Check the number of queries for that page with the add-on installed
Check the number of queries for that page with the add-on turned off

But more importantly check the time it takes to load the page (since some queries are faster than others) and do this a few times to get an average

There is no set amount for "too many" but what you think is acceptable.... some developers are better at targeting performance more than others.

 

[Chris D]

It also worth noting that some queries are just bad by design (just 1 query can be very slow, and some quires can be very fast).

I think this is an important aspect. The way I see it, an additional query in itself isn't necessarily a bad thing and although I do my best to avoid them, they are sometimes unavoidable.

tenants, can you give a good example of a badly designed query that might be too slow?

 

[tenants]

Sure... just take a peek through my plugins...
, but hopefully not

That question is probably better answered by some of the Oracle devs / DBAs here (there are some around)... I've got a few:

• Joining multiple tables without linking correctly (you could end up with millions of results instead of a few)
• Not making use of LIMITS / WHERE where you can reduce the number of tables it needs to look through
• Not using an index when you can... that's fairly bad (indexes are updated at the time of insert and can be time saving when queried)
• Not using the correct column for indexing (sometimes you need to think about this)
• "ORDER BY" for a very large table does take time (I worked on a corporate project that had over 20 million entries and they wanted to use quite a few order bys and they wondered why it was taking more than 10 seconds ... silly silly)
• Using something like "ORDER BY RAND()" can be quite bad for a large table: SELECT * FROM table ORDER BY RAND() LIMIT 1 <<<< ekks. There are lots of ways around this, see here (I was looking at this recently)
• SELECT * is not always great, if you can, pick out what you need
• Using larger than needed datatypes (blobs instead of varchars) I've done this recently, but it wasn't a mistake.. and not on a large table used on the front end, this was more of a "will look at correct size when I have time")

The list is goes on (and probably better answered on a SQL forum by a SQL dev)

In a related note, but not the the query it's self: not making use of cache isn't a great design