author | Dominic Pinkman <Dominic.Pinkman@Nokia.com> |
Wed, 31 Mar 2010 11:11:55 +0100 | |
changeset 7 | 51a74ef9ed63 |
permissions | -rw-r--r-- |
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> |