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.
A channel using SQL database queries is running slowly.
SQL database performance can be slow for various reasons:
- SQL queries are running slowly:
- The application may be creating more queries than necessary:
- Set the logging level for your channel to Debug:
- Identify slow queries:
- Filter the logs to display Debug messages for your channel.
- Identify consecutive SQL queries.
- The difference between the time stamps gives the approximate execution time.
- Identify frequently repeated queries.
- Identify which queries have the most impact:
- Optimize the most significant queries:
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.
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.
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.