How to Debug Slow Database Queries?

Introduction

If a SQL database is experiencing performance issues, you can use the Iguana logs to track the performance of your SQL Statements. Setting the logging level of the channel to Debug, will log all SQL statements that are being made by the channel.

Note: For information on using and viewing logs in Iguana, see the Logs section.

Issue [top]

A channel using SQL database queries is running slowly.

Cause [top]

SQL database performance can be slow for various reasons:

  • SQL queries are running slowly:

    Please speak to your DBA about optimization (as it is beyond the scope of this article). However you can try adding database indexes and/or redesigning the queries to return the minimum number of rows you require (by adding more specific selection criteria). Be aware that most SQL queries (particularly more complex ones) can usually be optimized by a DBA — and will often run much faster. It is not uncommon to see 10x or even a 1000x times improvement for some queries (your DBA also can also other techniques like stored procedures, and cached queries).

  • The application may be creating more queries than necessary:

    Review the code for the channel to check that it is not making unnecessary queries, like:  Reloading codes multiple times when once is sufficient, processing rows using single row queries when loading multiple (or even all) the rows into a Lua table is more efficient, querying a parent table and child separately instead of using a single join query (speak to your DBA), etc.

Solution [top]

  1. Set the logging level for your channel to Debug:

    When the logging level is set to Debug, the SQL statements that are sent to the database will show up as Debug entries in the Iguana log files.

  2. Identify slow queries:

    Each entry in the Iguana log files has a time stamp that you can use to estimate execution times. The difference between the time stamps for two SQL queries approximates execution time.

    1. Filter the logs to display Debug messages for your channel.
    2. Identify consecutive SQL queries.
    3. The difference between the time stamps gives the approximate execution time.
  3. Identify frequently repeated queries.
  4. Identify which queries have the most impact:

    This is a combination of the execution time multiplied by the number of times the query is repeated. Sometimes it is not the slowest queries that are the bottleneck.

  5. Optimize the most significant queries:

    Start by optimizing the queries (or query) that has the most impact. You will probably only need to optimize a few queries to resolve the performance issues. Once the channel is running fast enough it is (usually) not worth doing more optimization as the law of diminishing returns applies.

Note: SQL query performance tends to change over time for various reasons (like more data in the database) — this is normal behaviour for databases.

So the recommended approach is to optimize enough to resolve you performance issues, rather than trying to make every query perfect (you can’t). Then you can do more optimization later if/when it is needed.

How it Works

Because each entry in the Iguana log files has a time stamp, you can determine the approximate amount of time needed to execute an SQL statement. To do this, examine the time stamp for the statement and then examine the time stamp for the next SQL statement in the log. The difference between the two time stamps will give you an estimate of the time required for this particular SQL statement to execute.

You should be aware that the period between the time stamps also includes the processing time for code that the channel runs between executing the queries. Usually the processing time for code is much less than the query (usually by orders of magnitude) — so this is an acceptable approximation.

Prevention [top]

Design your code to minimize unnecessary queries. And try to write queries that run efficiently. It is worth checking performance during development and getting help from a DBA for queries that are too slow.