Using +Symbian SQL Trace Data Guide

Analysing Symbian SQL

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(N²) 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.