Is 20 SQL Queries Per Page Load Really Considered a Lot

How many queries are too many?

10 fast queries can be better than 1 slow one. Define what's acceptable in terms of response time, throughput, in normal and peek traffic conditions, and measure if these 10 queries are a problem or not (i.e. don't respect your expectations).

If they are, then try to change your design and find a better solution.

How much SQL Query is too much SQL Query?

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).

how bad is it to have extra database queries?

Short answer: (1) make sure you're staying at the same big-O level, reuse connections, measure performance; (2) think about how much you care about data consistency.

Long answer:

Performance

Strictly from performance perspective, and generally speaking, unless you are already close to maxing out your database resources, such as max connections, this is not likely to have major impact. But there are certain things you should keep in mind:

• do the "6-8" queries that replace "2-4" queries stay in the same execution time? e.g. if current database interaction is at O(1) is it going to change to O(n)? Or current O(n) going to change to O(n^2)? If yes, you should think about what that means for your application
• most application servers can reuse existing database connections, or have persistent database connection pools; make sure your application does not establish a new connection for every query; otherwise this is going to make it even more inefficient
• in many common cases, mainly on larger tables with complex indexes and joins, doing few queries by primary keys may be more efficient than joining those tables in a single query; this would be the case if, while doing such joins, the server not only takes longer to perform the complex query, but also blocks other queries against affected tables

Generally speaking about performance, the rule of thumb is - always measure.

Consistency

Performance is not the only aspect to consider, however. Also think about how much you care about data consistency in your application.

For example, consider a simple case - tables A and B that have one-to-one relationship and you are querying for a single record using a primary key. If you join those tables and retrieve result using a single query, you'll either get a record from both A and B, or no records from either, which is what your application expects too. Now consider if you split that up into 2 queries (and you're not using transactions with preferred isolation levels) - you get a record from table A, but before you could grab the matching record from table B, it is deleted/updated by another process. Now your application has a record from A but none from B.

General question here is - do you care about ACID compliance of your relational data as it pertains to the queries you are breaking apart? If the answer is yes, you must think about how your application logic will react in these specific cases.

How to speed up a page with 100 mysql queries?

There are a number of options for MySQL.

First is to setup a Query Cache in your MySQL config. If your program is SELECT heavy, try setting low-priority-updates to on. This gives higher priority on the server to SELECT statements, and less priority to INSERT/DELETE/UPDATE statements.

Changing MySQL's use of memory might be a good idea, especially if you use a lot of JOIN statements - I usually set the join_buffer_size to about 8M.

From a PHP point-of-view, try caching results.

Edit: the class down the forum page that Suresh Kamrushi posted is a nice way of caching in PHP.

A very long time of loading data. Large Amount of Data. SQL queries in C#

You can try caching the whole CP table in a dictionary:

class ConsultaDB {
  private static Dictionary<String, String> s_Data = new
    Dictionary<String, String>();

  private static void CoreFeedCache() {
    using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=CCPP.accdb")) {
      using (var comm = conn.CreateCommand()) {
        comm.CommandText = 
          @"select ID,
                   CP
              from CP";

        using (reader = comm.ExecuteReader()) {
          while (reader.Read()) {
            s_Data.Add(Convert.ToString(reader[0]), Convert.ToString(reader[1]));
          }  
        }
      }
    }
  } 

  static {
    CoreFeedCache();
  }

  public static string returnCP(string id) {
    String result;

    if (!s_Data.TryGetValue(id, out result))
      result = null;

    return result;
  } 
}

If CP has about 100K items as well, it will require MegaBytes of RAM.

How significant is the amount of queries used to site performance/speed?

Well quantity of queries is one factor. But you also have to consider each individual query, does it do joins? Lots of Math? Queries within queries? Do the tables have indexes etc.

Take a look at these links on optimisation, knowing how to optimise something can tell you what can cause it to slow down.

http://www.fiftyfoureleven.com/weblog/web-development/programming-and-scripts/mysql-optimization-tip

http://msdn.microsoft.com/en-us/library/ff650689.aspx

http://hungred.com/useful-information/ways-optimize-sql-queries/

What would be considered average or high load for a website using mysql on the backend?

More than the number of queries, what probably matters is what they do, and how : if you have millions of rows in your tables, and are not using the right indexes, you server will fall... If your queries are ultra-optimized, with the right indexes, and/or you don't have much data, you server will live.

You might want to use EXPLAIN on the queries that are the most used, to see if at least those are optimized / using indexes ;-)

Then, you will probably want to add so kind of caching mecanism, like APC or memcached ; at least if you can...

For instance, the lists states and countries probably never change : it could be cached, to not hit the database thousands of times, but just, say, once a day or once an hour.

---

Related Topics