12 <concept xml:lang="en" id="GUID-321F406B-2C77-5F2B-9FDA-F25726188CE2"><title>Using Symbian SQL Trace Data Guide</title><prolog><metadata><keywords/></metadata></prolog><conbody><p>This document describes how to use Symbian SQL trace data in Symbian platform applications. </p> <section><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><title>See Also</title> <p><xref href="GUID-EC33FF66-F15E-5316-8828-C6CFD57DB9E5.dita">Symbian SQL Tracing Guide</xref>  </p> </section> </conbody></concept>

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

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

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

    15 <section id="GUID-3A16065A-34CA-4ABD-AAA8-FC92A88B826F"><title>Analysing Symbian SQL</title> <p>Debugging and optimising

    16 database applications involves generic techniques which are not specific to

    17 Symbian implementation. This document does not aim to cover them all but illustrates

    18 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

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

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

    21 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

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

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

    24 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

    25 can be used to identify which SQL statements are executing slowly. Statements

    26 can execute slowly for a great many reasons and tracing will not tell you

    27 why. A well known example is the inefficiency of storing blobs in a database

    28 table instead of storing the blobs elsewhere and their addresses in the database.

    29 The purpose of function entry tracing is to give you the data you need to

    30 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

    31 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

    32 in time O(N<sup>2</sup>) where N is the number of columns in the table. For

    33 values of N less than 100 the impact is not significant, but for values above

    34 1000 the effect is very noticeable and data tables with a large number of

    35 columns are best avoided. </p> <p><b>Key

    36 event traces</b> </p> <p>Key event traces identify events such as IPC calls,

    37 startup and close of the SQL server, and the number of full event scans performed

    38 by an <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref> object. </p> <p>Full table scans are

    39 particularly important as they are computationally costly and ought to be

    40 avoided. One reason for an unnecessary full table scan is the use of an expression

    41 in a WHERE clause instead of a column name. If you have a table with an integer

    42 primary key called <codeph>id</codeph> there is a big difference in performance

    43 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

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

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

    46 and forces a full table scan for values satisfying it. </p> <p>Another reason

    47 for a full table scan is incorrect use of indexes or simply failure to use

    48 them. The purpose of indexes is to search a table rapidly but there are pitfalls

    49 associated with their use. For instance, indexes will only be used if they

    50 contain 30 or fewer columns and they only speed up the evaluation of a WHERE

    51 clause if certain rules are obeyed. </p> <p>A full table scan is also triggered

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

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

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

    55 </conbody></concept>