This document describes how to use Symbian SQL trace data in Symbian platform applications.
Debugging and optimising database applications involves generic techniques which are not specific to Symbian implementation. This document does not aim to cover them all but illustrates ways of identifying common programming errors by means of SQL tracing.
Error traces
Error tracing identifies function leaves and
panics in the client and server. These can occur for numerous reasons. One
possible kind of error is KSqlErrSchema, which occurs when
the database schema has been changed between a call to RSqlStatement::Prepare() and RSqlStatement::Exec() or RSqlStatement::Next().
Function entry traces
A function entry trace generates timestamps indicating the length of time a function took to return.
The function entry trace
of RSqlStatement::Exec() with an SQL statement as argument
can be used to identify which SQL statements are executing slowly. Statements
can execute slowly for a great many reasons and tracing will not tell you
why. A well known example is the inefficiency of storing blobs in a database
table instead of storing the blobs elsewhere and their addresses in the database.
The purpose of function entry tracing is to give you the data you need to
identify cases of this kind.
The performance of RSqlStatement::Prepare() can
also be impacted by inefficient SQL statements. For instance, in some cases, Prepare() runs
in time O(N2) where N is the number of columns in the table. For
values of N less than 100 the impact is not significant, but for values above
1000 the effect is very noticeable and data tables with a large number of
columns are best avoided.
Key event traces
Key event traces identify events such as IPC calls,
startup and close of the SQL server, and the number of full event scans performed
by an RSqlStatement object.
Full table scans are
particularly important as they are computationally costly and ought to be
avoided. One reason for an unnecessary full table scan is the use of an expression
in a WHERE clause instead of a column name. If you have a table with an integer
primary key called id there is a big difference in performance
between these SELECT statements:
SELECT name FROM table WHERE id=?
SELECT name FROM table WHERE id*1=?
Although
the two statements are logically identical, the second statement is inefficient
because the use of an expression id*1 disables optimisation
and forces a full table scan for values satisfying it.
Another reason for a full table scan is incorrect use of indexes or simply failure to use them. The purpose of indexes is to search a table rapidly but there are pitfalls associated with their use. For instance, indexes will only be used if they contain 30 or fewer columns and they only speed up the evaluation of a WHERE clause if certain rules are obeyed.
A full table scan is also triggered by the use of the operator OR within a WHERE clause.