<?xml version="1.0" encoding="utf-8"?>

<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. -->

<!-- This component and the accompanying materials are made available under the terms of the License 

"Eclipse Public License v1.0" which accompanies this distribution, 

and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". -->

<!-- Initial Contributors:

    Nokia Corporation - initial contribution.

Contributors: 

-->

<!DOCTYPE concept

  PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">

<concept id="GUID-321F406B-2C77-5F2B-9FDA-F25726188CE2" xml:lang="en"><title>Using

Symbian SQL Trace Data Guide</title><shortdesc>This document describes how to use Symbian SQL trace data in Symbian

platform applications. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

<section id="GUID-3A16065A-34CA-4ABD-AAA8-FC92A88B826F"><title>Analysing Symbian SQL</title> <p>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. </p> <p><b>Error traces</b> </p> <p>Error tracing identifies function leaves and

panics in the client and server. These can occur for numerous reasons. One

possible kind of error is <xref href="GUID-1BCF6DC7-8419-3399-9890-627048A74F52.dita"><apiname>KSqlErrSchema</apiname></xref>, which occurs when

the database schema has been changed between a call to <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita#GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A/GUID-DF9C2258-9BAA-38F0-9B11-21CD00316912"><apiname>RSqlStatement::Prepare()</apiname></xref> and <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita#GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A/GUID-52E3CE72-D495-388F-8829-952E32F0F37D"><apiname>RSqlStatement::Exec()</apiname></xref> or <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita#GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A/GUID-C8BBAAA8-0468-3330-B601-391443C1C410"><apiname>RSqlStatement::Next()</apiname></xref>. </p> <p><b>Function

entry traces</b> </p> <p>A function entry trace generates timestamps indicating

the length of time a function took to return. </p> <p>The function entry trace

of <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita#GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A/GUID-52E3CE72-D495-388F-8829-952E32F0F37D"><apiname>RSqlStatement::Exec()</apiname></xref> 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. </p> <p>The performance of <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita#GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A/GUID-DF9C2258-9BAA-38F0-9B11-21CD00316912"><apiname>RSqlStatement::Prepare()</apiname></xref> can

also be impacted by inefficient SQL statements. For instance, in some cases, <xref href="GUID-2337F32D-8BC3-33BA-9437-E6505314FF08.dita"><apiname>Prepare()</apiname></xref> runs

in time O(N<sup>2</sup>) 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. </p> <p><b>Key

event traces</b> </p> <p>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 <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref> object. </p> <p>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 <codeph>id</codeph> there is a big difference in performance

between these SELECT statements: </p> <codeblock id="GUID-17F44865-4AEE-5FCC-A199-D9DB83CC394E" xml:space="preserve">SELECT name FROM table WHERE id=?</codeblock> <codeblock id="GUID-F60C0E48-AC75-5603-BA13-A59B54033DEF" xml:space="preserve">SELECT name FROM table WHERE id*1=?</codeblock> <p>Although

the two statements are logically identical, the second statement is inefficient

because the use of an expression <codeph>id*1</codeph> disables optimisation

and forces a full table scan for values satisfying it. </p> <p>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. </p> <p>A full table scan is also triggered

by the use of the operator OR within a WHERE clause. </p> </section>

<section id="GUID-61A56F50-90B5-4BE0-A618-3B275DB4784C"><title>See Also</title> <p><xref href="GUID-EC33FF66-F15E-5316-8828-C6CFD57DB9E5.dita">Symbian

SQL Tracing Guide</xref>  </p> </section>

</conbody></concept>