<?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-43CA02E7-0101-5824-B91B-E15EE20C829A" xml:lang="en"><title>Avoid

Transient Tables</title><shortdesc>This guide gives advice for avoiding the use of transient tables.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

<section id="GUID-191FA663-E058-49EF-A514-008C2755CAC2"><title>Introduction</title> <p>Transient tables are nameless entities

that exist for the duration of a single SQL statement and are automatically

deleted at the conclusion of the statement. It is best to avoid queries that

need transient tables. </p> <p><b>Intended

audience:</b> </p> <p>This document is intended to be used by Symbian platform

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

<section id="GUID-62920E16-50D3-504A-9E8C-B835CF374A8B"><title>Avoid Queries

that Require Transient Tables</title> <p>Complex queries sometimes require

SQLite to compute intermediate results that are stored in <i>transient tables</i>.

Transient tables are nameless entities that exist for the duration of a single

SQL statement and are automatically deleted at the conclusion of the statement. </p> <p>We

use the term transient to describe these tables rather than temporary to avoid

confusion with TEMP tables. TEMP tables are tables that are private to a particular

database connection and which persist for the duration of that database connection.

TEMP tables have names and work like regular tables with the exceptions that

they are only visible to the current database connection and are automatically

deleted when the connection is closed. Transient tables, in contrast, are

invisible internal holding areas for intermediate results that exist only

until the end of the current SQL statement. </p> <p>Transient tables will

be stored either in RAM or on disk depending on device configuration. The

Symbian default configuration is to store transient tables on disk although

downstream device manufacturers may alter this. </p> <p>When configured to

store transient tables on disk, in reality a combination of memory and file

space is used to hold the table. Transient tables go through the same page

cache mechanism that regular database files go through. So as long as the

transient tables do not grow too large, they will always be held in memory

in the page cache rather than being written to disk. Information is only written

to disk when the cache overflows. </p> <p>When transient tables are configured

to be stored in memory rather than in a file, the cache is considered infinitely

large and will never overflow and thus nothing will ever be written out to

the file system. Storing transient tables in memory rather than in files avoids

the overhead of file I/O but requires potentially unbounded amounts of memory.

Using file-based transient tables puts an upper bound on the amount of memory

required but adds file I/O overhead. </p> <p>One strategy for dealing with

transient tables is to avoid them all together. If you never use transient

tables then it does not really matter if they are stored in memory or on disk.

The remainder of this section will enumerate all of the things that might

provoke SQLite to create a transient table. If you avoid all of these things

in your code, then you never need to worry about where your transient tables

are stored. </p> <p>A transient table is created whenever you use the DISTINCT

keyword in a query: </p> <codeblock id="GUID-EDCC26CB-7620-5F84-807C-736B2AB46CC7" xml:space="preserve">

SELECT DISTINCT name FROM artist;

</codeblock> <p>The DISTINCT keyword guarantees that each row of the result

set will be different from all other rows. In order to enforce this SQLite

creates a transient table that stores all prior output rows from the query.

If a new row can be found in the transient table then the new row is skipped.

The same situation occurs when DISTINCT is used within an aggregate function: </p> <codeblock id="GUID-6A14E006-3233-51E2-9FB1-CC26895F2452" xml:space="preserve">

SELECT avg(DISTINCT cnt) FROM playlist;

</codeblock> <p>In this context the DISTINCT keyword means that the aggregate

function is only applied to distinct elements of the result. As before, a

transient table is used to record prior values of the result so that SQLite

can tell if new results have been seen before. </p> <p>The UNION, INTERSECT,

and EXCEPT operators used to create compound queries always generate a distinct

set of rows. Even though the DISTINCT keyword does not appear, it is implied

for these connectors, and a transient table is used to enforce the distinctness.

In contrast, the UNION ALL operator does not require a transient table. </p> <p>A

transient table might be used to implement an ORDER BY or GROUP BY clause.

SQLite always tries to use an index to satisfy an ORDER BY or GROUP BY clause

if it can. But if no indexes are available which can satisfy the ORDER BY

or GROUP BY, then the entire results set is loaded into a transient table

and sorted there. </p> <p>Subqueries on the right-hand side of the IN operator

use a transient table. Consider an example: </p> <codeblock id="GUID-D6162DA3-7C9D-5C4E-ABF4-E83209354B79" xml:space="preserve">

SELECT * FROM ex334a WHERE id IN (SELECT id FROM ex334b WHERE amt>7);

</codeblock> <p>The results of the (SELECT id FROM ex334b WHERE amt&gt;7) subquery

are stored in a transient table. Then the value of the <i>id</i> column is

checked against this table for each row of the outer query to determine if

that row should be included in the result set. </p> <p>Sometimes subqueries

in the FROM clause of a query will result in a transient table. This is not

always the case because SQLite tries very hard to convert subqueries in the

FROM clause into a join that does not use subqueries. SQLite uses the term <i>flattening</i> to

describe the conversion of FROM clause subqueries into joins. Flattening is

an optimization that makes queries run faster. But in some cases, flattening

cannot occur. When the flattening optimization is inhibited, the results of

the subqueries are stored in transient tables and then a separate query is

run against those transient tables to generate the final results. </p> <p>Consider

the following schema and query: </p> <codeblock id="GUID-F8CD6651-09CA-52FD-AAF5-799B087A0456" xml:space="preserve">

CREATE TABLE t1(a,b,c);

CREATE TABLE t2(x,y,z);

SELECT * FROM t1 JOIN (SELECT x,y FROM t2);

</codeblock> <p>The subquery in the FROM clause is plainly visible in the

SELECT statement above. But if the subquery were disguised as a view, it might

be less noticeable. A view is really just a macro that serves as a place-holder

for a subquery. So the SELECT statement above is equivalent to the following: </p> <codeblock id="GUID-B5DE2E2A-3034-534C-B808-E68B3086F071" xml:space="preserve">

CREATE VIEW v2 AS SELECT x, y FROM t2;

SELECT * FROM t1 JOIN v2;

</codeblock> <p>In either case above, whether the subquery is stated explicitly

or is implied by the use of a view, flattening occurs and the query is converted

into this: </p> <codeblock id="GUID-0B933985-4520-5E0E-9762-13CCAB3D186A" xml:space="preserve">

SELECT a,b,c,x,y FROM t1 JOIN t2;

</codeblock> <p>Had flattening not occurred, it would have been necessary

to evaluate the v2 view or the subquery into a transient table then execute

the outer query using the transient table as one of the two tables in the

join. SQLite prefers to flatten the query because a flattened query generally

uses fewer resources and is better able to take advantage of indexes. The

rules for determining when flattening occurs and when it does not are complex.

Flattening occurs if all of the following conditions in the outer query and

in the subquery are satisfied: </p> <ul>

<li id="GUID-C2006064-D688-50F2-BDAA-0903746BBDCA"><p>The subquery and the

outer query do not both use aggregates. </p> </li>

<li id="GUID-8B0A124C-D2A1-51C3-80B2-37E53A22BEA8"><p>The subquery is not

an aggregate or the outer query is not a join. </p> </li>

<li id="GUID-265A05AB-6C73-598E-9B5E-D602E3ACF399"><p>The subquery is not

the right operand of a left outer join, or the subquery is not itself a join. </p> </li>

<li id="GUID-F85FC64A-22E9-550C-B24D-7A8D52AC39E0"><p>The subquery is not

DISTINCT or the outer query is not a join. </p> </li>

<li id="GUID-A78B6644-6DBE-584B-9216-3415ADA1E35B"><p>The subquery is not

DISTINCT or the outer query does not use aggregates. </p> </li>

<li id="GUID-B86B1D2C-CC0D-5C04-A2C5-BC3D26069964"><p>The subquery does not

use aggregates or the outer query is not DISTINCT. </p> </li>

<li id="GUID-D89420C6-6AD0-5B4A-9A1D-DF9CE690BA7D"><p>The subquery has a FROM

clause. </p> </li>

<li id="GUID-671D51CA-0C05-5610-8A66-0A7B795270B6"><p>The subquery does not

use LIMIT or the outer query is not a join. </p> </li>

<li id="GUID-81632F3C-3E30-53B8-94C4-214498C11A34"><p>The subquery does not

use LIMIT or the outer query does not use aggregates. </p> </li>

<li id="GUID-6E30A9D8-F758-58D8-8A60-2D1F98DC571D"><p>The subquery does not

use aggregates or the outer query does not use LIMIT. </p> </li>

<li id="GUID-55523D18-9421-582C-8CB4-601BC6BBA8A0"><p>The subquery and the

outer query do not both have ORDER BY clauses. </p> </li>

<li id="GUID-12AD267C-F596-5188-8075-14D29CAB66DF"><p>The subquery is not

the right term of a LEFT OUTER JOIN or the subquery has no WHERE clause. </p> </li>

<li id="GUID-1E253D10-A3B3-528A-A599-6E000040FAF3"><p>The subquery and outer

query do not both use LIMIT </p> </li>

<li id="GUID-1FC29A55-C941-59CE-AE16-23C5F867069C"><p>The subquery does not

use OFFSET </p> </li>

</ul> <p>Nobody really expects a programmer to memorize or even understand

the above set of flattening rules. As a short-cut, perhaps it is best to remember

that a complicated subquery or view in the FROM clause of a complicated query

might defeat the flattening optimization and thus require the use of transient

tables. </p> <p>One other obscure use of transient tables is when there is

an INSTEAD OF DELETE or INSTEAD OF UPDATE trigger on a view. When such triggers

exists and a DELETE or an UPDATE is executed against that view then a transient

table is created which stores copies of the rows to be deleted or updated.

Since it is unusual to have INSTEAD OF triggers in the first place this case

rarely arises. </p> <p>In summary, transient tables are used to implement

the following features: </p> <ul>

<li id="GUID-AA436587-D5B8-57A4-A256-AA255B316086"><p>The DISTINCT keyword

or other situations where distinct results are required such as compound queries

using UNION, INTERSECT, or EXCEPT. </p> </li>

<li id="GUID-84FC9506-4E85-5F7E-A96A-45EC239360DA"><p>ORDER BY or GROUP BY

clauses that cannot be satisfied by indexes. </p> </li>

<li id="GUID-59EAEE54-98A0-5647-B4E4-9A213342D46D"><p>Subqueries on the right-hand

side of the IN operator. </p> </li>

<li id="GUID-BCB93FF3-F959-5292-A3D7-C23CD436DC4E"><p>Subqueries or views

in the FROM clause of a query that cannot be flattened. </p> </li>

<li id="GUID-552BE4BC-2242-5E26-85C0-20CB99C188A1"><p>DELETE or UPDATE against

a view with INSTEAD OF triggers. </p> </li>

</ul> <p>The resource-limited nature of the environment, where file I/O is

expensive and memory is scarce, means you will be well served to avoid these

constructs and thus avoid the need for transient tables. </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-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-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion

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>