<?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-B994E6F7-228A-5433-B87F-91857C5D93D6" xml:lang="en"><title>SQL Insertion

Tips</title><shortdesc>This guide provides some tips for using COMMIT statements.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

<section id="GUID-A3DC5F3F-92C6-4847-A9C5-A227F777D7D3"><title>Introduction</title> <p>INSERT, UPDATE and DELETE operations

are all very fast. However, COMMIT statements are very slow. You need to consider

several techniques to ensure you make the best use of COMMIT statements. </p> <p><b>Intended audience:</b> </p> <p>This document is intended to be used by

Symbian platfomr licensees and third party application developers. </p> </section>

<section id="GUID-C12416F0-87D5-59AA-A08F-A1741A3FD3ED"><title>INSERT and

UPDATE are Fast but COMMIT is Slow</title> <p>A programmer migrating to this

database engine might write a test program to see how many INSERT statements

per second it can do. They create an empty database with a single empty table.

Then they write a loop that runs a few thousand times and does a single INSERT

statement on each iteration. Upon timing this program they find that it appears

to only be doing a couple of dozen INSERTs per second. </p> <p>“Everybody

I talked to says SQLite is suppose to be really fast”, the new programmer

will typically complain, “But I'm only getting 20 or 30 INSERTs per second!” </p> <p>In

reality, SQLite can achieve around 50000 or more INSERTs per second on a modern

workstation, although less on a typical embedded platform. But the characteristics

of the underlying storage medium and the fact that the database engine guarantees

atomic updates to the database mean that it can only achieve a few dozen COMMIT

operations per second. </p> <p>Unless you take specific action to tell SQLite

to do otherwise, it will automatically insert a COMMIT operation after every

insert. So the programmers described above are really measuring the number

of transactions per second, not the number of INSERTs. This is a very important

distinction. </p> <p>Why is COMMIT so much slower than INSERT? SQLite guarantees

that changes to a database are ACID – Atomic, Consistent, Isolated, and Durable.

The Atomic and Durable parts are what take the time. </p> <p>In order to be

Atomic, the database engine has to go through an elaborate protocol with the

underlying file system, which ultimately means that every modified page of

the database file must be written twice. </p> <p>In order to be durable, the

COMMIT operation must not return until all content has been safely written

to nonvolatile media. At least two consecutive non-concurrent writes to flash

memory must occur in order to COMMIT. </p> <p>An atomic and durable COMMIT

is a very powerful feature that can help you to build a system that is resilient,

even in the face of unplanned system crashes or power failures. But the price

of this resilience is that COMMIT is a relatively slow operation. Hence if

performance is a priority you should strive to minimize the number of COMMITs. </p> <p>If

you need to do more than one INSERT or UPDATE or DELETE operation, you are

advised to put them all inside a single explicit transaction by running the

BEGIN statement prior to the first changes and executing COMMIT once all changes

have finished. In this way, all your changes occur within a single transaction

and only a single time-consuming COMMIT operation must occur. </p> <p>If you

omit the explicit BEGIN...COMMIT, then SQLite automatically inserts an implicit

BEGIN...COMMIT around each of your INSERT, UPDATE, and DELETE statements,

which means you end of doing many COMMITs which will always be much slower

than doing just one. </p> </section>

<section id="GUID-0E57010E-E3AB-573E-B550-72DE36725D1B"><title>Batch INSERT,

UPDATE, and DELETE Operations Using TEMP Tables</title> <p>As described above,

when you have many changes to make to a database, you are advised to make

all those changes within a single explicit transaction by preceding the first

change with a BEGIN statement and concluding the changes with a COMMIT statement. </p> <p>The

problem with BEGIN...COMMIT is that BEGIN acquires an exclusive lock on the

database file which is not released until the COMMIT completes. That means

that only a single connection to the database can be in the middle of a BEGIN...COMMIT

at one time. If another thread or process tries to start a BEGIN...COMMIT

while the first is busy, the second has to wait. To avoid holding up other

threads and processes, therefore, every BEGIN should be followed by a COMMIT

as quickly as possible. </p> <p>But sometimes you run into a situation where

you have to make periodic INSERTs or UPDATEs to a database based on timed

or external events. For example, you may want to do an INSERT into an event

log table once every 250 milliseconds or so. You could do a separate INSERT

for each event, but that would mean doing a separate COMMIT four times per

second, which is perhaps more overhead than you desire. On the other hand,

if you did a BEGIN and accumulated several seconds worth of INSERTs you could

avoid doing a COMMIT except for every 10<sup>th</sup> second or so. The trouble

there is that other threads and processes are unable to write to the database

while the event log is holding its transaction open. </p> <p>The usual method

for avoiding this dilemma is to store all of the INSERTs in a separate TEMP

table, then periodically flush the content of the TEMP table into the main

database with a single operation. </p> <p>A TEMP table works just like a regular

database table except that a TEMP table is only visible to the database connection

that creates it, and the TEMP table is automatically dropped when the database

connection is closed. You create a TEMP table by inserting the “TEMP” or “TEMPORARY”

keyword in between “CREATE” and “TABLE”, like this: </p> <codeblock id="GUID-90068863-645B-551F-8A85-A12ED647F1AA" xml:space="preserve">

CREATE TEMP TABLE event_accumulator(

        eventId INTEGER,

        eventArg TEXT

    );

</codeblock> <p>Because TEMP tables are ephemeral (meaning that they do not

persist after the database connection closes) SQLite does not need to worry

about making writes to a TEMP table atomic or durable. Hence a COMMIT to a

TEMP table is very quick. </p> <p>A process can do multiple INSERTs into a

TEMP table without having to enclose those INSERTs within an explicit BEGIN...COMMIT

for efficiency. Writes to a TEMP table are always efficient regardless of

whether or not they are enclosed in an explicit transaction. </p> <p>So as

events arrive, they can be written into the TEMP table using isolated INSERT

statements. But because the TEMP table is ephemeral, one must take care to

periodically flush the contents of the TEMP table into the main database where

they will persist. So every 10 seconds or so (depending on the application

requirements) you can run code like this: </p> <codeblock id="GUID-F51D81FE-5F85-5C22-96AC-4CC69AF00BC4" xml:space="preserve">

BEGIN;

INSERT INTO event_log SELECT * FROM event_accumulator;

DELETE FROM event_accumulator;

COMMIT;

</codeblock> <p>These statements transfer the content of the ephemeral event_accumulator

table over to the persistent event_log table as a single atomic operation.

Since this transfer occurs relatively infrequently, minimal database overhead

is incurred. </p> </section>

<section id="GUID-83C876AB-7C3F-5BFC-8F02-6503A0B3D8D6"><title>Use Bound Parameters</title> <p>Suppose

you have a descriptor, nameDes, and you want to insert that value into the

namelist table of a database. One way to proceed is to construct an appropriate

INSERT statement that contains the desired string value as a SQL string literal,

then run that INSERT statement. Pseudo-code for this approach follows: </p> <codeblock id="GUID-B96D98B4-ED28-566D-A0AA-073E4BEC6954" xml:space="preserve">

_LIT(KSql, “INSERT INTO namelist VALUES('%S')”);

sqlBuf.Format(KSql, nameDes);

sqlDatabase.Execute(sql);

</codeblock> <p>The INSERT statement is constructed by the call to <codeph>Format()</codeph> on

the second line of the example above. The first argument is a template for

the SQL statement. The value of the nameDes descriptor is inserted where the

%S occurs in the template. Notice that the %S is surrounded by single quotes

so that the string will be properly contained in SQL standard quotes. </p> <p>This

approach works as long as the value in nameDes does not contain any single-quote

characters. If nameDes does contain one or more single-quotes, then the string

literal in the INSERT statement will not be well-formed and a syntax error

might occur. Or worse, if a hostile user is able to control the content of

nameDes, they might be able to put text in nameDes that looked something like

this: </p> <codeblock id="GUID-12627B2F-D98A-5170-9E6F-6C3EFA33EA87" xml:space="preserve">

hi'); DELETE FROM critical_table; SELECT 'hi

</codeblock> <p>This would result in the sqlBuf variable holding </p> <codeblock id="GUID-FD6A2B42-8260-535B-80D3-B99C726B02FC" xml:space="preserve">

INSERT INTO namelist VALUES('hi'); DELETE FROM critical_table; SELECT 'hi'

</codeblock> <p>Your adversary has managed to convert your single INSERT statement

into three separate SQL statements, one of which does things that you probably

do not want to happen. This is called an “SQL Injection Attack”. You want

to be very, very careful to avoid SQL injection attacks as they can seriously

compromise the security of your application. </p> <p>SQLite allows you to

specify parameters in SQL statements and then substitute values for those

parameters prior to running the SQL. Parameters can take several forms, including: </p> <codeblock id="GUID-8255AFE5-CFA2-5DAD-A168-2A7294021AC6" xml:space="preserve">

?

?NNN

:AAA

@AAA

$AAA

</codeblock> <p>In the above, NNN means any sequence of digits and AAA means

any sequence of alphanumeric characters and underscores. In this example we

will stick with the first and simplest form – the question mark. The operation

above would be rewritten as shown below. (Error checking is omitted from this

example for brevity.) </p> <codeblock id="GUID-292857E5-9219-5AD5-B05A-9C9A47055741" xml:space="preserve">

_LIT(KSql, “INSERT INTO namelist VALUES(?)”);

RSqlStatement s;

s.PrepareL(db, KSql);

s.BindText(1, nameDes);

s.Exec();

s.Close();

</codeblock> <p> <codeph> PrepareL()</codeph> compiles the SQL statement held

in the literal KSql. This statement contains a single parameter. The value

for this parameter is initially NULL. </p> <p>The <codeph>BindText()</codeph> sets

the value of this parameter to the content of the <codeph>nameDes</codeph> descriptor

and then <codeph>Exec()</codeph> executes the SQL statement with the bound

parameter value. </p> <p>There are variations of <codeph>BindXxx()</codeph> to

bind other kinds of values such as integers, floating point numbers, and binary

large objects (BLOBs). The key point to observe is that none of these values

need to be quoted or escaped in any way. And there is no possibility of being

vulnerable to an SQL injection attack. </p> <p>Besides reducing your vulnerability

to SQL injection attacks, the use of bound parameters also happens to be more

efficient that constructing SQL statements from scratch, especially when inserting

large strings or BLOBs. </p> </section>

<section id="GUID-3EC5A35D-67E0-5C85-9DA3-CD6AA40BB6A7"><title>Cache and Reuse

Prepared Statements</title> <p>Using <xref href="GUID-0176BF07-DF94-3259-8F90-DE030E35CE9A.dita"><apiname>RSqlStatement</apiname></xref> is a

two-step process. Firstly the statement must be compiled using <codeph>Prepare()</codeph>.

Then the resulting prepared statement is run using either <codeph>Exec()</codeph> or <codeph>Next()</codeph>. </p> <p>The

relative amount of time spent doing each of these steps depends on the nature

of the SQL statement. SELECT statements that return a large result set or

UPDATE or DELETE statements that touch many rows of a table will normally

spend most of their time in the Virtual Machine module and relatively little

time being compiled. But simple INSERT statements on the other hand, can take

twice as long to compile as they take to run in the virtual machine. </p> <p>A

simple way to reduce the CPU load of an application that uses SQLite is to

cache the prepared statements and reuse them. Of course, one rarely needs

to run the exact same SQL statement more than once. But if a statement contains

one or more bound parameters, you can bind new values to the parameters prior

to each run and thus accomplish something different with each invocation. </p> <p>This

technique is especially effective when doing multiple INSERTs into the same

table. Instead of preparing a separate insert for each row, create a single

generic insert statement like this: </p> <codeblock id="GUID-C66EEE5F-27C3-59A8-9DD4-2E32A49367AF" xml:space="preserve">

INSERT INTO important_table VALUES(?,?,?,?,?)

</codeblock> <p>Then for each row to be inserted, use one or more of the <codeph>BindXxx()</codeph> interfaces

to bind values to the parameters in the insert statement, and call <codeph>Exec()</codeph> to

do the insert, then call <codeph>Reset()</codeph> to rewind the program counter

of the internal bytecode in preparation for the next run. </p> <p>For INSERT

statements, reusing a single prepared statement in this way will typically

make your code run two or three times faster. </p> <p>You can manually manage

a cache of prepared statements, keeping around only those prepared statements

that you know will be needed again and closing prepared statements using <codeph>Close()</codeph> when

you are done with them or when they are about to fall out of scope. But depending

on the application, it can be more convenient to create a wrapper class around

the SQL interface that manages the cache automatically. </p> <p>A wrapper

class can keep around the 5 or 10 most recently used prepared statements and

reuse those statements if the same SQL is requested. Handling the prepared

statement cache automatically in a wrapper has the advantage that it frees

you to focus more mental energy on writing a great application and less effort

on operating the database interface. It also makes the programming task less

error prone since with an automatic class, there is no chance of accidentally

omitting a call to <codeph>Close()</codeph> and leaking prepared statements. </p> <p>The

downside is that a cache wrapper will not have the foresight of a human programmer

and will often cache prepared statements that are no longer needed, thus using

excess memory, or sometimes discard prepared statements just before they are

needed again. </p> <p>This is a classic ease-of-programming versus performance

trade-off. For applications that are intended for a high-power workstation,

it can be best to go with a wrapper class that handles the cache automatically.

But when designing an application for a resource constrained devices where

performance is critical and engineering design talent is plentiful, it may

be better to manage the cache manually. </p> <p>Regardless of whether or not

the prepared statement cache is managed manually or automatically using a

wrapper class, reusing prepared statements is always a good thing, and can

in some cases double or triple the performance of the application. </p> </section>

</conbody><related-links>

<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext>

</link>

<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server

Guide</linktext></link>

<link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext>

</link>

<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext>

</link>

<link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient

Tables</linktext></link>

<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile

Corruption</linktext></link>

<link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index

Tips</linktext></link>

<link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema

Tips</linktext></link>

<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext>

</link>

<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement

Tips</linktext></link>

<link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext>

</link>

<link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE

Command</linktext></link>

<link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE

CLause Tips</linktext></link>

</related-links></concept>