How much SQL Query is too much SQL Query?

Question

I was researching for a CMS to use and ran into a review on vBulletin 4.0; using about 200 queries on one page load.

I was then worried.

Further research brought me to other sites to see how much queries they are using and I found that some forum software such as Invision Power Board and PHPBB are using queries as low as 6 or 8.

Currently, my site uses about 25 to 40 queries.

Should I be worried?

Answer 1

If you aim for something, aim for 3 reads and 1 writes per HTTP hit.

While these are arbitrary numbers (somehow, they are actually taken from the Advanced PHP Programming), they emphasize the ideas:

• the number of SQL roundtrips should be low, under 10 for sure, per HTTP call
• there is a difference between reads and writes, and the ratio should favour reads. writes create contention

Also remember that not all reads are equal: the 3 reads should be highly optimized reads, not full table scans with 4-5 outer joins...

Answer 2

Don't be worried about number of queries.

Be worried about:

1. Pages loading too slowly
2. The SQL being too complicated to maintain.

Clarification:

SQL being too complicated can come from either too many queries OR a few queries that are very complicated (lots of joins and sub queries, etc).

Answer 3

It Depends. The more you hit the db, the more load you have. Just some things to look for. If you need to display values from several different tables, you will probably need to run several queries. If you only have a couple of users and you know you're not going to have lots of data, it probably doesn't matter.

Some things to consider:

• Are you running the same query multiple times per page load? If you can reuse the result, do it.
• Are you running a query-per-result of another query? If so, maybe allow the DB to do the join and only do one pull.

If your page is slow from hitting the db too much, look at memcached.

Answer 4

You might try re-factoring your code over time to decrease the number of round-trips to SQL Server. One way to do this could be to utilize caching. For example, data you need frequently can be loaded when the application is started, then grabbed from the cache when it is needed.

Another approach could be to de-normalize your data into tables that are specifically designed to give you the data your site needs in a fewer number of queries.

Answer 5

Also consider if some of those queries (those you use to populate lookup values for instance) can be cached. That way if the same query is called on multiple pages or each time you move from one group of records to another, the database isn't hit again to run exaclty the same query. I remeber one time we were trying to determine why the site was so slow when the stored proc that was running was very fast and found using profiler that it was being sent over and over and over again when it didn't need to be.

Answer 6

You can cache all those queries with vbulletin. If you look at pbnation.com they have over a million visitors a day and only around 3-4 queries per page load. Everything else is cached in memcached.