Symbian3/SDK/Source/GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita
author Dominic Pinkman <Dominic.Pinkman@Nokia.com>
Wed, 31 Mar 2010 11:11:55 +0100
changeset 7 51a74ef9ed63
permissions -rw-r--r--
Week 12 contribution of API Specs and fix SDK submission
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
7
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     1
<?xml version="1.0" encoding="utf-8"?>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     2
<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. -->
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     3
<!-- This component and the accompanying materials are made available under the terms of the License 
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     4
"Eclipse Public License v1.0" which accompanies this distribution, 
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     5
and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". -->
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     6
<!-- Initial Contributors:
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     7
    Nokia Corporation - initial contribution.
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     8
Contributors: 
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
     9
-->
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    10
<!DOCTYPE concept
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    11
  PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    12
<concept id="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04" xml:lang="en"><title>SQL Joins</title><shortdesc>This guide explains how to use CROSS JOIN phrases to override the
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    13
optimizer's ordering of tables.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    14
<section id="GUID-C6BDCE52-468D-459F-980C-A9041B56BDF1"><title>Introduction</title> <p>SQLite uses the “CROSS JOIN” phrase
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    15
as a means to override the table reordering decisions of the query optimizer.
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    16
The CROSS JOIN connector is rarely needed and should probably never be used
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    17
prior to the final performance tuning phase of application development. Even
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    18
then, SQLite usually gets the order of tables in a join right without any
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    19
extra help. But on those rare occasions when SQLite gets it wrong, the CROSS
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    20
JOIN connector is an invaluable way of tweaking the optimizer to do what you
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    21
want. </p> <p><b>Intended
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    22
audience:</b> </p> <p>This document is intended to be used by Symbian platform
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    23
licensees and third party application developers. </p> </section>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    24
<section id="GUID-8B5F579E-B94D-527C-9530-367B5B0729C9"><title>Use CROSS JOIN
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    25
to Force a Particular Join Ordering</title> <p>The SQLite query optimizer
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    26
will attempt to reorder the tables in a join in order to find the most efficient
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    27
way to evaluate the join. The optimizer usually does this job well, but occasionally
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    28
it will make a bad choice. When that happens, it might be necessary to override
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    29
the optimizer's choice by explicitly specifying the order of tables in the
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    30
SELECT statement. </p> <p>To illustrate the problem, consider the following
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    31
schema: </p> <codeblock id="GUID-8B2D4E3C-7231-5D63-B8CD-CD687750CD1E" xml:space="preserve">
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    32
CREATE TABLE node(
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    33
    id INTEGER PRIMARY KEY,
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    34
    name TEXT
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    35
);
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    36
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    37
CREATE INDEX node_idx ON node(name);
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    38
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    39
CREATE TABLE edge(
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    40
    orig INTEGER REFERENCES node,
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    41
    dest INTEGER REFERENCES node,
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    42
    PRIMARY KEY(orig, dest)
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    43
);
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    44
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    45
CREATE INDEX edge_idx ON edge(dest,orig);
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    46
</codeblock> <p>This schema defines a directed graph with the ability to store
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    47
a name on each node of the graph. Similar designs (though usually more complicated)
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    48
arise frequently in application development. Now consider a three-way join
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    49
against the above schema: </p> <codeblock id="GUID-61383F86-E84F-58F4-B2B1-81CD25635B88" xml:space="preserve">
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    50
SELECT e.*
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    51
FROM edge AS e,
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    52
 node AS n1,
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    53
 node AS n2
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    54
WHERE n1.name = 'alice'
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    55
 AND n2.name = 'bob'
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    56
 AND e.orig = n1.id
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    57
 AND e.dest = n2.id;
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    58
</codeblock> <p>This query asks for information about all edges that go from
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    59
nodes labelled “alice” over to nodes labelled “bob”. </p> <p>There are many
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    60
ways that the optimizer might choose to implement this query, but they all
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    61
boil down to two basic designs. The first option looks for edges between all
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    62
pairs of nodes. The following pseudocode illustrates: </p> <codeblock id="GUID-19A9F537-D791-5894-9FA9-E205B3768CF7" xml:space="preserve">
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    63
foreach n1 where n1.name='alice' do:
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    64
    foreach n2 where n2.name='bob' do:
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    65
        foreach e where e.orig=n1.id and e.dest=n2.id do:
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    66
            return e.*
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    67
        end
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    68
    end
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    69
end
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    70
</codeblock> <p>The second design is to loop over all 'alice' nodes and follow
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    71
edges off of those nodes looking for nodes named 'bob'. (The roles of 'alice'
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    72
and 'bob' might be reversed here without changing the fundamental character
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    73
or the algorithm): </p> <codeblock id="GUID-02A72F7B-D5AB-5306-95F9-9832EF05FB25" xml:space="preserve">
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    74
foreach n1 where n1.name='alice' do:
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    75
    foreach e where e.orig=n1.id do:
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    76
        foreach n2 where n2.id=e.dest and n2.name='bob' do:
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    77
            return e.*
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    78
        end
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    79
    end
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    80
end
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    81
</codeblock> <p>The first algorithm above corresponds to a join order of n1-n2-e.
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    82
The second algorithm corresponds to a join order of n1-e-n2. </p> <p>The question
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    83
the optimizer has to answer is which of these two algorithms is likely to
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    84
give the fastest result, and it turns out that the answer depends on the nature
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    85
of the data stored in the database. </p> <p>Let the number of alice nodes
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    86
be M and the number of bob nodes be N. Consider two scenarios: In the first
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    87
scenario, M and N are both 2 but there are thousands of edges on each node.
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    88
In this case, the first algorithm is preferred. With the first algorithm,
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    89
the inner loop checks for the existence of an edge between a pair of nodes
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    90
and outputs the result if found. But because there are only 2 alice and bob
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    91
nodes each, the inner loop only has to run 4 times and the query is very quick. </p> <p>The
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    92
second algorithm would take much longer here. The outer loop of the second
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    93
algorithm only executes twice, but because there are a large number of edges
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    94
leaving each 'alice' node, the middle loop has to iterate many thousands of
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    95
times. So in the first scenario, we prefer to use the first algorithm. </p> <p>Now
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    96
consider the case where M and N are both 3500. But suppose each of these nodes
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    97
is connected by only one or two edges. In this case, the second algorithm
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    98
is preferred. </p> <p>With the second algorithm, the outer loop still has
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
    99
to run 3500 times, but the middle loop only runs once or twice for each outer
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   100
loop and the inner loop will only run once for each middle loop, if at all.
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   101
So the total number of iterations of the inner loop is around 7000. </p> <p>The
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   102
first algorithm, on the other hand, has to run both its outer loop and its
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   103
middle loop 3500 times each, resulting in 12 million iterations of the middle
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   104
loop. Thus in the second scenario, second algorithm is nearly 2000 times faster
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   105
than the first. </p> <p>In this particular example, if you run ANALYZE on
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   106
your database to collect statistics on the tables, the optimizer will be able
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   107
to figure out the best algorithm to use. But if you do not want to run ANALYZE
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   108
or if you do not want to waste database space storing the SQLITE_STAT1 statistics
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   109
table that ANALYZE generates, you can manually override the decision of the
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   110
optimizer by specifying a particular order for tables in a join. You do this
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   111
by substituting the keyword phrase “CROSS JOIN” in place of commas in the
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   112
FROM clause. </p> <p>The “CROSS JOIN” phrase forces the table to the left
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   113
to be used before the table to the right. For example, to force the first
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   114
algorithm, write the query this way: </p> <codeblock id="GUID-E0FE82CE-0D32-535F-A4F7-EF582BFA7AE9" xml:space="preserve">
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   115
SELECT *
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   116
FROM node AS n1 CROSS JOIN
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   117
 node AS n2 CROSS JOIN
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   118
 edge AS e
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   119
WHERE n1.name = 'alice'
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   120
 AND n2.name = 'bob'
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   121
 AND e.orig = n1.id
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   122
 AND e.dest = n2.id;
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   123
</codeblock> <p>And to force the second algorithm, write the query like this: </p> <codeblock id="GUID-33AD25F2-E58E-5018-BE0C-025FC2116643" xml:space="preserve">
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   124
SELECT *
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   125
FROM node AS n1 CROSS JOIN
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   126
 edge AS e CROSS JOIN
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   127
 node AS n2
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   128
WHERE n1.name = 'alice'
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   129
 AND n2.name = 'bob'
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   130
 AND e.orig = n1.id
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   131
 AND e.dest = n2.id;
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   132
</codeblock> <p>The CROSS JOIN keyword phrase is perfectly valid SQL syntax
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   133
according to the SQL standard, but it is syntax that is rarely if ever used
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   134
in real-world SQL statements. Because it is so rarely used otherwise, SQLite
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   135
has appropriated the phrase as a means to override the table reordering decisions
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   136
of the query optimizer. </p> <p>The CROSS JOIN connector is rarely needed
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   137
and should probably never be used prior to the final performance tuning phase
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   138
of application development. Even then, SQLite usually gets the order of tables
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   139
in a join right without any extra help. But on those rare occasions when SQLite
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   140
gets it wrong, the CROSS JOIN connector is an invaluable way of tweaking the
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   141
optimizer to do what you want. </p> </section>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   142
</conbody><related-links>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   143
<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   144
</link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   145
<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   146
Guide</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   147
<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   148
</link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   149
<link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   150
Tables</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   151
<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   152
Corruption</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   153
<link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   154
Tips</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   155
<link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   156
Tips</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   157
<link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   158
Tips</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   159
<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   160
</link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   161
<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   162
Tips</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   163
<link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   164
Command</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   165
<link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   166
CLause Tips</linktext></link>
51a74ef9ed63 Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff changeset
   167
</related-links></concept>