Troubleshooting Slow Transactions

If you find what looks like a particularly slow SQL transaction, you can configure the Content Store to generate additional diagnostic information. To do this, use the escenic-admin logging level editor (see Configure Logging Levels) to set the logging category com.escenic.sql.Logger to one of the following values:

INFO

Logs the SQL statements themselves before they are executed.

DEBUG

Additionally logs the positional parameters of the prepared statements, as they are set.

You can now see all the SQL statements executed in the log, but you still don't know which particular statement is slow, nor do you necessarily know exactly how or why the individual statements come to be executed. You may have suspicions regarding some of the statements, however. You can set up the connection wrapper to dump the call stacks of these statements to the log. You should then be able to find from the stack traces which template files are responsible for the statements.

To generate stack dumps in this way you need to set the /neo/io/connector/DebugConnection component's stackdumpRegExp property to a regular expression that matches the SQL statement(s) you are interested in. If, for example, you are interested in all statements involving the ArticleMetaContent table, then you can set it to /ArticleMetaContent/i (the "i" at the end indicates that the expression is case insensitive). Then any SQL statement containing the string "articlemetacontent" will trigger a stack dump of the current thread to standard error.