Skip to content

H
h2database
切换分支/标签
查找文件
Normal viewHistoryPermalink
performance.html 39.4 KB
Newer Older
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
1 2 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Thomas Mueller's avatar
License change to MPL 2.0 / EPL 1.0
08ec66d2
 
Thomas Mueller committed
3 
Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, Version 1.0,
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
4 5 6 7 
and under the Eclipse Public License, Version 1.0
Initial Developer: H2 Group
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
Thomas Mueller's avatar
Documentation (add "viewport")
f504aaa1
 
Thomas Mueller committed
8 9 
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
Thomas Mueller's avatar
Documentation
fe2cf166
 
Thomas Mueller committed
10 
<meta name="viewport" content="width=device-width, initial-scale=1" />
Thomas Mueller's avatar
Documentation (add "viewport")
f504aaa1
 
Thomas Mueller committed
11 
<title>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
12 
Performance
Thomas Mueller's avatar
Documentation (add "viewport")
f504aaa1
 
Thomas Mueller committed
13 14 
</title>
<link rel="stylesheet" type="text/css" href="stylesheet.css" />
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
15 16 17 18 19 20 21 22 23 24 25 26 
<!-- [search] { -->
<script type="text/javascript" src="navigation.js"></script>
</head><body onload="frameMe();">
<table class="content"><tr class="content"><td class="content"><div class="contentDiv">
<!-- } -->

<h1>Performance</h1>
<a href="#performance_comparison">
    Performance Comparison</a><br />
<a href="#poleposition_benchmark">
    PolePosition Benchmark</a><br />
<a href="#database_performance_tuning">
Thomas Mueller's avatar
--no commit message
a5505f83
 
Thomas Mueller committed
27 
    Database Performance Tuning</a><br />
Thomas Mueller's avatar
--no commit message
43bce1b4
 
Thomas Mueller committed
28 29 
<a href="#built_in_profiler">
    Using the Built-In Profiler</a><br />
Thomas Mueller's avatar
Documentation.
ee86e638
 
Thomas Mueller committed
30 31 32 33 34 35 
<a href="#application_profiling">
    Application Profiling</a><br />
<a href="#database_profiling">
    Database Profiling</a><br />
<a href="#explain_plan">
    Statement Execution Plans</a><br />
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
36 37 
<a href="#storage_and_indexes">
    How Data is Stored and How Indexes Work</a><br />
Thomas Mueller's avatar
Improved docs.
27d7b7d3
 
Thomas Mueller committed
38 39 
<a href="#fast_import">
    Fast Database Import</a><br />
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
40
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
41 
<h2 id="performance_comparison">Performance Comparison</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
42 
<p>
Thomas Mueller's avatar
--no commit message
b5045419
 
Thomas Mueller committed
43 
In many cases H2 is faster than other
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
44 
(open source and not open source) database engines.
Thomas Mueller's avatar
Documentation.
07b054c6
 
Thomas Mueller committed
45 46 47 48 
Please note this is mostly a single connection benchmark run on one computer,
with many very simple operations running against the database.
This benchmark does not include very complex queries.
The embedded mode of H2 is faster than the client-server mode because
Thomas Mueller's avatar
Documentation.
f1887961
 
Thomas Mueller committed
49 
the per-statement overhead is greatly reduced.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
50 51 52 
</p>

<h3>Embedded</h3>
Thomas Mueller's avatar
Documentation (round corners for pre, shadow for pre and table).
164f419c
 
Thomas Mueller committed
53 
<table class="main">
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
54 
<tr><th>Test Case</th><th>Unit</th><th>H2</th><th>HSQLDB</th><th>Derby</th></tr>
Thomas Mueller's avatar
Documentation / update performance results.
4ab7caf6
 
Thomas Mueller committed
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 
<tr><td>Simple: Init</td><td>ms</td><td>1019</td><td>1907</td><td>8280</td></tr>
<tr><td>Simple: Query (random)</td><td>ms</td><td>1304</td><td>873</td><td>1912</td></tr>
<tr><td>Simple: Query (sequential)</td><td>ms</td><td>835</td><td>1839</td><td>5415</td></tr>
<tr><td>Simple: Update (sequential)</td><td>ms</td><td>961</td><td>2333</td><td>21759</td></tr>
<tr><td>Simple: Delete (sequential)</td><td>ms</td><td>950</td><td>1922</td><td>32016</td></tr>
<tr><td>Simple: Memory Usage</td><td>MB</td><td>21</td><td>10</td><td>8</td></tr>
<tr><td>BenchA: Init</td><td>ms</td><td>919</td><td>2133</td><td>7528</td></tr>
<tr><td>BenchA: Transactions</td><td>ms</td><td>1219</td><td>2297</td><td>8541</td></tr>
<tr><td>BenchA: Memory Usage</td><td>MB</td><td>12</td><td>15</td><td>7</td></tr>
<tr><td>BenchB: Init</td><td>ms</td><td>905</td><td>1993</td><td>8049</td></tr>
<tr><td>BenchB: Transactions</td><td>ms</td><td>1091</td><td>583</td><td>1165</td></tr>
<tr><td>BenchB: Memory Usage</td><td>MB</td><td>17</td><td>11</td><td>8</td></tr>
<tr><td>BenchC: Init</td><td>ms</td><td>2491</td><td>4003</td><td>8064</td></tr>
<tr><td>BenchC: Transactions</td><td>ms</td><td>1979</td><td>803</td><td>2840</td></tr>
<tr><td>BenchC: Memory Usage</td><td>MB</td><td>19</td><td>22</td><td>9</td></tr>
<tr><td>Executed statements</td><td>#</td><td>1930995</td><td>1930995</td><td>1930995</td></tr>
<tr><td>Total time</td><td>ms</td><td>13673</td><td>20686</td><td>105569</td></tr>
<tr><td>Statements per second</td><td>#</td><td>141226</td><td>93347</td><td>18291</td></tr>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
73 74 75 
</table>

<h3>Client-Server</h3>
Thomas Mueller's avatar
Documentation (round corners for pre, shadow for pre and table).
164f419c
 
Thomas Mueller committed
76 
<table class="main">
Thomas Mueller's avatar
Documentation / update performance results.
4ab7caf6
 
Thomas Mueller committed
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 
<tr><th>Test Case</th><th>Unit</th><th>H2 (Server)</th><th>HSQLDB</th><th>Derby</th><th>PostgreSQL</th><th>MySQL</th></tr>
<tr><td>Simple: Init</td><td>ms</td><td>16338</td><td>17198</td><td>27860</td><td>30156</td><td>29409</td></tr>
<tr><td>Simple: Query (random)</td><td>ms</td><td>3399</td><td>2582</td><td>6190</td><td>3315</td><td>3342</td></tr>
<tr><td>Simple: Query (sequential)</td><td>ms</td><td>21841</td><td>18699</td><td>42347</td><td>30774</td><td>32611</td></tr>
<tr><td>Simple: Update (sequential)</td><td>ms</td><td>6913</td><td>7745</td><td>28576</td><td>32698</td><td>11350</td></tr>
<tr><td>Simple: Delete (sequential)</td><td>ms</td><td>8051</td><td>9751</td><td>42202</td><td>44480</td><td>16555</td></tr>
<tr><td>Simple: Memory Usage</td><td>MB</td><td>22</td><td>11</td><td>9</td><td>0</td><td>1</td></tr>
<tr><td>BenchA: Init</td><td>ms</td><td>12996</td><td>14720</td><td>24722</td><td>26375</td><td>26060</td></tr>
<tr><td>BenchA: Transactions</td><td>ms</td><td>10134</td><td>10250</td><td>18452</td><td>21453</td><td>15877</td></tr>
<tr><td>BenchA: Memory Usage</td><td>MB</td><td>13</td><td>15</td><td>9</td><td>0</td><td>1</td></tr>
<tr><td>BenchB: Init</td><td>ms</td><td>15264</td><td>16889</td><td>28546</td><td>31610</td><td>29747</td></tr>
<tr><td>BenchB: Transactions</td><td>ms</td><td>3017</td><td>3376</td><td>1842</td><td>2771</td><td>1433</td></tr>
<tr><td>BenchB: Memory Usage</td><td>MB</td><td>17</td><td>12</td><td>11</td><td>1</td><td>1</td></tr>
<tr><td>BenchC: Init</td><td>ms</td><td>14020</td><td>10407</td><td>17655</td><td>19520</td><td>17532</td></tr>
<tr><td>BenchC: Transactions</td><td>ms</td><td>5076</td><td>3160</td><td>6411</td><td>6063</td><td>4530</td></tr>
<tr><td>BenchC: Memory Usage</td><td>MB</td><td>19</td><td>21</td><td>11</td><td>1</td><td>1</td></tr>
<tr><td>Executed statements</td><td>#</td><td>1930995</td><td>1930995</td><td>1930995</td><td>1930995</td><td>1930995</td></tr>
<tr><td>Total time</td><td>ms</td><td>117049</td><td>114777</td><td>244803</td><td>249215</td><td>188446</td></tr>
<tr><td>Statements per second</td><td>#</td><td>16497</td><td>16823</td><td>7887</td><td>7748</td><td>10246</td></tr>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
96 97 98 99 100 101 
</table>

<h3>Benchmark Results and Comments</h3>

<h4>H2</h4>
<p>
Thomas Mueller's avatar
Prepare release
932bbb55
 
Thomas Mueller committed
102 
Version 1.4.177 (2014-04-12) was used for the test.
Thomas Mueller's avatar
The benchmarks have been run using recent database versions.
502a8dbd
 
Thomas Mueller committed
103 104 
For most operations, the performance of H2 is about the same as for HSQLDB.
One situation where H2 is slow is large result sets, because they are buffered to
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
105 
disk if more than a certain number of records are returned.
Thomas Mueller's avatar
The benchmarks have been run using recent database versions.
502a8dbd
 
Thomas Mueller committed
106 
The advantage of buffering is: there is no limit on the result set size.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
107 108 109 110 
</p>

<h4>HSQLDB</h4>
<p>
Thomas Mueller's avatar
Documentation / update performance results.
4ab7caf6
 
Thomas Mueller committed
111 
Version 2.3.2 was used for the test.
Thomas Mueller's avatar
The benchmarks have been run using recent database versions.
502a8dbd
 
Thomas Mueller committed
112 
Cached tables are used in this test (<code>hsqldb.default_table_type=cached</code>),
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
113 
and the write delay is 1 second (<code>SET WRITE_DELAY 1</code>).
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
114 115 116 117 
</p>

<h4>Derby</h4>
<p>
Thomas Mueller's avatar
Documentation / update performance results.
4ab7caf6
 
Thomas Mueller committed
118 
Version 10.10.1.1 was used for the test. Derby is clearly the slowest embedded database in this test.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
119 
This seems to be a structural problem, because all operations are really slow.
Thomas Mueller's avatar
Improve docs
d22a1a41
 
Thomas Mueller committed
120 
It will be hard for the developers of Derby to improve the performance to a reasonable level.
Thomas Mueller's avatar
Improved docs.
27d7b7d3
 
Thomas Mueller committed
121 
A few problems have been identified: leaving autocommit on is a problem for Derby.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
122 
If it is switched off during the whole test, the results are about 20% better for Derby.
Thomas Mueller's avatar
Documentation.
b04bfcec
 
Thomas Mueller committed
123 124 125 
Derby calls <code>FileChannel.force(false)</code>, but only twice per log file (not on each commit).
Disabling this call improves performance for Derby by about 2%.
Unlike H2, Derby does not call <code>FileDescriptor.sync()</code> on each checkpoint.
Thomas Mueller's avatar
Documentation.
1df42bbd
 
Thomas Mueller committed
126 
Derby supports a testing mode (system property <code>derby.system.durability=test</code>)
Thomas Mueller's avatar
Documentation.
2a1f38f3
 
Thomas Mueller committed
127 
where durability is disabled. According to the documentation, this setting should be used for testing only,
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
128 129 130 131 132 133 134 
as the database may not recover after a crash. Enabling this setting improves performance
by a factor of 2.6 (embedded mode) or 1.4 (server mode). Even if enabled, Derby is still less
than half as fast as H2 in default mode.
</p>

<h4>PostgreSQL</h4>
<p>
Thomas Mueller's avatar
Documentation / update performance results.
4ab7caf6
 
Thomas Mueller committed
135 
Version 9.1.5 was used for the test.
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
136 
The following options where changed in <code>postgresql.conf:
Thomas Mueller's avatar
Documentation: added notranslate tags
a08877c0
 
Thomas Mueller committed
137 
fsync = off, commit_delay = 1000</code>.
Thomas Mueller's avatar
The benchmarks have been run using recent database versions.
502a8dbd
 
Thomas Mueller committed
138 
PostgreSQL is run in server mode.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
139 140 141 142 143 
The memory usage number is incorrect, because only the memory usage of the JDBC driver is measured.
</p>

<h4>MySQL</h4>
<p>
Thomas Mueller's avatar
Documentation / update performance results.
4ab7caf6
 
Thomas Mueller committed
144 
Version 5.1.65-log was used for the test.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
145 
MySQL was run with the InnoDB backend.
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
146 
The setting <code>innodb_flush_log_at_trx_commit</code>
Thomas Mueller's avatar
The benchmarks have been run using recent database versions.
502a8dbd
 
Thomas Mueller committed
147 
(found in the <code>my.ini / my.cnf</code> file) was set to 0. Otherwise (and by default), MySQL is slow
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
148 149 150 151 152 
(around 140 statements per second in this test) because it tries to flush the data to disk for each commit.
For small transactions (when autocommit is on) this is really slow.
But many use cases use small or relatively small transactions.
Too bad this setting is not listed in the configuration wizard,
and it always overwritten when using the wizard.
Thomas Mueller's avatar
The benchmarks have been run using recent database versions.
502a8dbd
 
Thomas Mueller committed
153 
You need to change this setting manually in the file <code>my.ini / my.cnf</code>, and then restart the service.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 
The memory usage number is incorrect, because only the memory usage of the JDBC driver is measured.
</p>

<h4>Firebird</h4>
<p>
Firebird 1.5 (default installation) was tested, but the results are not published currently.
It is possible to run the performance test with the Firebird database,
and any information on how to configure Firebird for higher performance are welcome.
</p>

<h4>Why Oracle / MS SQL Server / DB2 are Not Listed</h4>
<p>
The license of these databases does not allow to publish benchmark results.
This doesn't mean that they are fast. They are in fact quite slow,
and need a lot of memory. But you will need to test this yourself.
SQLite was not tested because the JDBC driver doesn't support transactions.
</p>

<h3>About this Benchmark</h3>
Thomas Mueller's avatar
--no commit message
554a5c0f
 
Thomas Mueller committed
174 175 
<h4>How to Run</h4>
<p>
Thomas Mueller's avatar
The benchmarks have been run using recent database versions.
502a8dbd
 
Thomas Mueller committed
176 
This test was as follows:
Thomas Mueller's avatar
--no commit message
554a5c0f
 
Thomas Mueller committed
177 
</p>
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
178 
<pre>
Thomas Mueller's avatar
--no commit message
554a5c0f
 
Thomas Mueller committed
179 180 181 182 183 184 185 186 187 
build benchmark
</pre>

<h4>Separate Process per Database</h4>
<p>
For each database, a new process is started, to ensure the previous test does not impact
the current test.
</p>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
188 189 190 191 192 193 194 195 
<h4>Number of Connections</h4>
<p>
This is mostly a single-connection benchmark.
BenchB uses multiple connections; the other tests use one connection.
</p>

<h4>Real-World Tests</h4>
<p>
Thomas Mueller's avatar
Improved docs.
27d7b7d3
 
Thomas Mueller committed
196 197 
Good benchmarks emulate real-world use cases. This benchmark includes 4 test cases:
BenchSimple uses one table and many small updates / deletes.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
198 199 200 201 202 203 204 205 
BenchA is similar to the TPC-A test, but single connection / single threaded (see also: www.tpc.org).
BenchB is similar to the TPC-B test, using multiple connections (one thread per connection).
BenchC is similar to the TPC-C test, but single connection / single threaded.
</p>

<h4>Comparing Embedded with Server Databases</h4>
<p>
This is mainly a benchmark for embedded databases (where the application runs in the same
Thomas Mueller's avatar
Improve docs
d22a1a41
 
Thomas Mueller committed
206 
virtual machine as the database engine). However MySQL and PostgreSQL are not Java
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
207 208 209 210 211 212 
databases and cannot be embedded into a Java application.
For the Java databases, both embedded and server modes are tested.
</p>

<h4>Test Platform</h4>
<p>
Thomas Mueller's avatar
Documentation.
b04bfcec
 
Thomas Mueller committed
213 
This test is run on Mac OS X 10.6. No virus scanner was used, and disk indexing was disabled.
Thomas Mueller's avatar
Drop support for Java 6
b6487f6d
 
Thomas Mueller committed
214 
The JVM used is Sun JDK 1.6.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
215 216 217 218 219 220 221 222 223 224 225 226 227 
</p>

<h4>Multiple Runs</h4>
<p>
When a Java benchmark is run first, the code is not fully compiled and
therefore runs slower than when running multiple times. A benchmark
should always run the same test multiple times and ignore the first run(s).
This benchmark runs three times, but only the last run is measured.
</p>

<h4>Memory Usage</h4>
<p>
It is not enough to measure the time taken, the memory usage is important as well.
Thomas Mueller's avatar
Improve docs
d22a1a41
 
Thomas Mueller committed
228 
Performance can be improved by using a bigger cache, but the amount of memory is limited.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 
HSQLDB tables are kept fully in memory by default; this benchmark
uses 'disk based' tables for all databases.
Unfortunately, it is not so easy to calculate the memory usage of PostgreSQL
and MySQL, because they run in a different process than the test. This benchmark currently
does not print memory usage of those databases.
</p>

<h4>Delayed Operations</h4>
<p>
Some databases delay some operations (for example flushing the buffers)
until after the benchmark is run. This benchmark waits between
each database tested, and each database runs in a different process (sequentially).
</p>

<h4>Transaction Commit / Durability</h4>
<p>
Durability means transaction committed to the database will not be lost.
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
246 247 
Some databases (for example MySQL) try to enforce this by default by
calling <code>fsync()</code> to flush the buffers, but
Thomas Mueller's avatar
Documentation: added notranslate tags
a08877c0
 
Thomas Mueller committed
248 
most hard drives don't actually flush all data. Calling the method slows down transaction commit a lot,
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 
but doesn't always make data durable. When comparing the results, it is important to
think about the effect. Many database suggest to 'batch' operations when possible.
This benchmark switches off autocommit when loading the data, and calls commit after each 1000
inserts. However many applications need 'short' transactions at runtime (a commit after each update).
This benchmark commits after each update / delete in the simple benchmark, and after each
business transaction in the other benchmarks. For databases that support delayed commits,
a delay of one second is used.
</p>

<h4>Using Prepared Statements</h4>
<p>
Wherever possible, the test cases use prepared statements.
</p>

<h4>Currently Not Tested: Startup Time</h4>
<p>
The startup time of a database engine is important as well for embedded use.
This time is not measured currently.
Also, not tested is the time used to create a database and open an existing database.
Here, one (wrapper) connection is opened at the start,
and for each step a new connection is opened and then closed.
</p>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
272 
<h2 id="poleposition_benchmark">PolePosition Benchmark</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
273 274 275 
<p>
The PolePosition is an open source benchmark. The algorithms are all quite simple.
It was developed / sponsored by db4o.
Thomas Mueller's avatar
Documentation.
70b9adab
 
Thomas Mueller committed
276 277 
This test was not run for a longer time, so please be aware that the results below
are for older database versions (H2 version 1.1, HSQLDB 1.8, Java 1.4).
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
278 
</p>
Thomas Mueller's avatar
Documentation (round corners for pre, shadow for pre and table).
164f419c
 
Thomas Mueller committed
279 
<table class="main">
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 
<tr><th>Test Case</th><th>Unit</th><th>H2</th><th>HSQLDB</th><th>MySQL</th></tr>
<tr><td>Melbourne write</td><td>ms</td><td>369</td><td>249</td><td>2022</td></tr>
<tr><td>Melbourne read</td><td>ms</td><td>47</td><td>49</td><td>93</td></tr>
<tr><td>Melbourne read_hot</td><td>ms</td><td>24</td><td>43</td><td>95</td></tr>
<tr><td>Melbourne delete</td><td>ms</td><td>147</td><td>133</td><td>176</td></tr>
<tr><td>Sepang write</td><td>ms</td><td>965</td><td>1201</td><td>3213</td></tr>
<tr><td>Sepang read</td><td>ms</td><td>765</td><td>948</td><td>3455</td></tr>
<tr><td>Sepang read_hot</td><td>ms</td><td>789</td><td>859</td><td>3563</td></tr>
<tr><td>Sepang delete</td><td>ms</td><td>1384</td><td>1596</td><td>6214</td></tr>
<tr><td>Bahrain write</td><td>ms</td><td>1186</td><td>1387</td><td>6904</td></tr>
<tr><td>Bahrain query_indexed_string</td><td>ms</td><td>336</td><td>170</td><td>693</td></tr>
<tr><td>Bahrain query_string</td><td>ms</td><td>18064</td><td>39703</td><td>41243</td></tr>
<tr><td>Bahrain query_indexed_int</td><td>ms</td><td>104</td><td>134</td><td>678</td></tr>
<tr><td>Bahrain update</td><td>ms</td><td>191</td><td>87</td><td>159</td></tr>
<tr><td>Bahrain delete</td><td>ms</td><td>1215</td><td>729</td><td>6812</td></tr>
<tr><td>Imola retrieve</td><td>ms</td><td>198</td><td>194</td><td>4036</td></tr>
<tr><td>Barcelona write</td><td>ms</td><td>413</td><td>832</td><td>3191</td></tr>
<tr><td>Barcelona read</td><td>ms</td><td>119</td><td>160</td><td>1177</td></tr>
<tr><td>Barcelona query</td><td>ms</td><td>20</td><td>5169</td><td>101</td></tr>
<tr><td>Barcelona delete</td><td>ms</td><td>388</td><td>319</td><td>3287</td></tr>
<tr><td>Total</td><td>ms</td><td>26724</td><td>53962</td><td>87112</td></tr>
</table>
Thomas Mueller's avatar
new experimental page store
390ecb87
 
Thomas Mueller committed
302 303 304 
<p>
There are a few problems with the PolePosition test:
</p>
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
305 306 307 
<ul><li>
HSQLDB uses in-memory tables by default while H2 uses persistent tables. The HSQLDB version
included in PolePosition does not support changing this, so you need to replace
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
308 309 
<code>poleposition-0.20/lib/hsqldb.jar</code> with a newer version (for example
<code>hsqldb-1.8.0.7.jar</code>),
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
310 
and then use the setting
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
311 312 
<code>hsqldb.connecturl=jdbc:hsqldb:file:data/hsqldb/dbbench2;hsqldb.default_table_type=cached;sql.enforce_size=true</code>
in the file <code>Jdbc.properties</code>.
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
313 
</li><li>HSQLDB keeps the database open between tests, while H2 closes the database (losing all the cache).
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
314 
To change that, use the database URL <code>jdbc:h2:file:data/h2/dbbench;DB_CLOSE_DELAY=-1</code>
Thomas Mueller's avatar
--no commit message
03d917b3
 
Thomas Mueller committed
315 
</li><li>The amount of cache memory is quite important, specially for the PolePosition test.
Thomas Mueller's avatar
new experimental page store
390ecb87
 
Thomas Mueller committed
316 317 
Unfortunately, the PolePosition test does not take this into account.
</li></ul>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
318
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
319 
<h2 id="database_performance_tuning">Database Performance Tuning</h2>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
320
Thomas Mueller's avatar
--no commit message
04ff535e
 
Thomas Mueller committed
321 322 
<h3>Keep Connections Open or Use a Connection Pool</h3>
<p>
Thomas Mueller's avatar
Documentation
8d64639e
 
Thomas Mueller committed
323 
If your application opens and closes connections a lot (for example, for each request),
Thomas Mueller's avatar
--no commit message
04ff535e
 
Thomas Mueller committed
324 325 326 327 
you should consider using a  <a href="tutorial.html#connection_pool">connection pool</a>.
Opening a connection using <code>DriverManager.getConnection</code> is specially slow
if the database is closed. By default the database is closed if the last connection is closed.
</p><p>
Thomas Mueller's avatar
Documentation
8d64639e
 
Thomas Mueller committed
328 329 330 
If you open and close connections a lot but don't want to use a connection pool,
consider keeping a 'sentinel' connection open for as long as the application runs,
or use delayed database closing. See also
Thomas Mueller's avatar
--no commit message
04ff535e
 
Thomas Mueller committed
331 332 333 
<a href="features.html#closing_a_database">Closing a database</a>.
</p>
Thomas Mueller's avatar
Shortened the paragraph, removed unnecessary information.
6061376c
 
Thomas Mueller committed
334 335 
<h3>Use a Modern JVM</h3>
<p>
Thomas Mueller's avatar
Formatting fixes.
0f61802b
 
Thomas Mueller committed
336 
Newer JVMs are faster. Upgrading to the latest version of your JVM can provide a "free" boost to performance.
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
337 
Switching from the default Client JVM to the Server JVM using the <code>-server</code> command-line
Thomas Mueller's avatar
--no commit message
7f75c8c0
 
Thomas Mueller committed
338 
option improves performance at the cost of a slight increase in start-up time.
buckyballs's avatar
Docs-Performance: JVM choice & Data Types
4c33d834
 
buckyballs committed
339 340 
</p>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
341 342 343 344 
<h3>Virus Scanners</h3>
<p>
Some virus scanners scan files every time they are accessed.
It is very important for performance that database files are not scanned for viruses.
Thomas Mueller's avatar
--no commit message
7f75c8c0
 
Thomas Mueller committed
345 
The database engine never interprets the data stored in the files as programs,
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
346 347 
that means even if somebody would store a virus in a database file, this would
be harmless (when the virus does not run, it cannot spread).
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
348 
Some virus scanners allow to exclude files by suffix. Ensure files ending with <code>.db</code> are not scanned.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
349 350 351 352 
</p>

<h3>Using the Trace Options</h3>
<p>
Thomas Mueller's avatar
--no commit message
7f75c8c0
 
Thomas Mueller committed
353 
If the performance hot spots are in the database engine, in many cases the performance
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
354 355 356 357 358 359 360 361 
can be optimized by creating additional indexes, or changing the schema. Sometimes the
application does not directly generate the SQL statements, for example if an O/R mapping tool
is used. To view the SQL statements and JDBC API calls, you can use the trace options.
For more information, see <a href="features.html#trace_options">Using the Trace Options</a>.
</p>

<h3>Index Usage</h3>
<p>
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
362 363 364 
This database uses indexes to improve the performance of
<code>SELECT, UPDATE, DELETE</code>.
If a column is used in the <code>WHERE</code> clause of a query, and if an index exists on this column,
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
365 366 
then the index can be used. Multi-column indexes are used if all or the first columns of the index are used.
Both equality lookup and range scans are supported.
Thomas Mueller's avatar
improve documentation
613ec072
 
Thomas Mueller committed
367 368 
Indexes are used to order result sets, but only if the condition uses the same index or no index at all.
The results are sorted in memory if required.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
369 370 
Indexes are created automatically for primary key and unique constraints.
Indexes are also created for foreign key constraints, if required.
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
371 
For other columns, indexes need to be created manually using the <code>CREATE INDEX</code> statement.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
372 373 
</p>
Thomas Mueller's avatar
Documentation.
5de35fa8
 
Thomas Mueller committed
374 375 376 377 378 379 380 381 382 
<h3>How Data is Stored Internally</h3>
<p>
For persistent databases, if a table is created with a single column primary key of type <code>BIGINT, INT, SMALLINT, TINYINT</code>,
then the data of the table is organized in this way. This is sometimes also called a "clustered index" or
"index organized table".
</p><p>
H2 internally stores table data and indexes in the form of b-trees.
Each b-tree stores entries as a list of unique keys (one or more columns) and data (zero or more columns).
The table data is always organized in the form of a "data b-tree" with a single column key of type <code>long</code>.
Thomas Mueller's avatar
Documentation.
9cbee66a
 
Thomas Mueller committed
383 384 
If a single column primary key of type <code>BIGINT, INT, SMALLINT, TINYINT</code> is specified when creating the table
(or just after creating the table, but before inserting any rows),
Thomas Mueller's avatar
Documentation.
5de35fa8
 
Thomas Mueller committed
385 386 387 388 389 390 391 392 393 394 395 396 397 398 
then this column is used as the key of the data b-tree.
If no primary key has been specified, if the primary key column is of another data type,
or if the primary key contains more than one column,
then a hidden auto-increment column of type <code>BIGINT</code> is added to the table,
which is used as the key for the data b-tree.
All other columns of the table are stored within the data area of this data b-tree
(except for large <code>BLOB, CLOB</code> columns, which are stored externally).
</p><p>
For each additional index, one new "index b-tree" is created. The key of this b-tree consists of the indexed columns,
plus the key of the data b-tree. If a primary key is created after the table has been created, or if the primary key
contains multiple column, or if the primary key is not of the data types listed above, then the primary key
is stored in a new index b-tree.
</p>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 
<h3>Optimizer</h3>
<p>
This database uses a cost based optimizer. For simple and queries and queries with medium complexity
(less than 7 tables in the join), the expected cost (running time) of all possible plans is calculated,
and the plan with the lowest cost is used. For more complex queries, the algorithm first tries
all possible combinations for the first few tables, and the remaining tables added using a greedy algorithm
(this works well for most joins). Afterwards a genetic algorithm is used to test at most 2000 distinct plans.
Only left-deep plans are evaluated.
</p>

<h3>Expression Optimization</h3>
<p>
After the statement is parsed, all expressions are simplified automatically if possible. Operations
are evaluated only once if all parameters are constant. Functions are also optimized, but only
if the function is constant (always returns the same result for the same parameter values).
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
414 
If the <code>WHERE</code> clause is always false, then the table is not accessed at all.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
415 416 417 418 419 
</p>

<h3>COUNT(*) Optimization</h3>
<p>
If the query only counts all rows of a table, then the data is not accessed.
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
420 421 
However, this is only possible if no <code>WHERE</code> clause is used, that means it only works for
queries of the form <code>SELECT COUNT(*) FROM table</code>.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
422 423 424 425 
</p>

<h3>Updating Optimizer Statistics / Column Selectivity</h3>
<p>
Thomas Mueller's avatar
Documentation.
17ee5b61
 
Thomas Mueller committed
426 427 428 
When executing a query, at most one index per join can be used.
If the same table is joined multiple times, for each join only one index is used
(the same index could be used for both joins, or each join could use a different index).
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
429 430 
Example: for the query
<code>SELECT * FROM TEST T1, TEST T2 WHERE T1.NAME='A' AND T2.ID=T1.ID</code>,
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
431 432 433 
two index can be used, in this case the index on NAME for T1 and the index on ID for T2.
</p><p>
If a table has multiple indexes, sometimes more than one index could be used.
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
434 435 
Example: if there is a table <code>TEST(ID, NAME, FIRSTNAME)</code> and an index on each column,
then two indexes could be used for the query <code>SELECT * FROM TEST WHERE NAME='A' AND FIRSTNAME='B'</code>,
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
436 437 438 439 440 441 
the index on NAME or the index on FIRSTNAME. It is not possible to use both indexes at the same time.
Which index is used depends on the selectivity of the column. The selectivity describes the 'uniqueness' of
values in a column. A selectivity of 100 means each value appears only once, and a selectivity of 1 means
the same value appears in many or most rows. For the query above, the index on NAME should be used
if the table contains more distinct names than first names.
</p><p>
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
442 
The SQL statement <code>ANALYZE</code> can be used to automatically estimate the selectivity of the columns in the tables.
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
443 444 445 
This command should be run from time to time to improve the query plans generated by the optimizer.
</p>
Thomas Mueller's avatar
--no commit message
a5505f83
 
Thomas Mueller committed
446 447 448 449 450 451 452 
<h3>In-Memory (Hash) Indexes</h3>
<p>
Using in-memory indexes, specially in-memory hash indexes, can speed up
queries and data manipulation.
</p>
<p>In-memory indexes are automatically used
for in-memory databases, but can also be created for persistent databases
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
453 
using <code>CREATE MEMORY TABLE</code>. In many cases,
Thomas Mueller's avatar
--no commit message
a5505f83
 
Thomas Mueller committed
454 455 456 457 458 
the rows itself will also be kept in-memory. Please note this may cause memory
problems for large tables.
</p>
<p>
In-memory hash indexes are backed by a hash table and are usually faster than
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
459 460 461 462 
regular indexes. However, hash indexes only supports direct lookup (<code>WHERE ID = ?</code>)
but not range scan (<code>WHERE ID &lt; ?</code>). To use hash indexes, use HASH as in:
<code>CREATE UNIQUE HASH INDEX</code> and
<code>CREATE TABLE ...(ID INT PRIMARY KEY HASH,...)</code>.
Thomas Mueller's avatar
--no commit message
a5505f83
 
Thomas Mueller committed
463 464 
</p>
Thomas Mueller's avatar
Remove unused code.
e3fce563
 
Thomas Mueller committed
465 466 467 
<h3>Use Prepared Statements</h3>
<p>
If possible, use prepared statements with parameters.
Thomas Mueller's avatar
Documentation.
2a1f38f3
 
Thomas Mueller committed
468 469 470 471 
</p>

<h3>Prepared Statements and IN(...)</h3>
<p>
Thomas Mueller's avatar
Remove unused code.
e3fce563
 
Thomas Mueller committed
472 
Avoid generating SQL statements with a variable size IN(...) list.
Thomas Mueller's avatar
Documentation.
2a1f38f3
 
Thomas Mueller committed
473 
Instead, use a prepared statement with arrays as in the following example:
Thomas Mueller's avatar
Remove unused code.
e3fce563
 
Thomas Mueller committed
474 475 476 477 478 479 480 481 
</p>
<pre>
PreparedStatement prep = conn.prepareStatement(
    "SELECT * FROM TABLE(X INT=?) T INNER JOIN TEST ON T.X=TEST.ID");
prep.setObject(1, new Object[] { "1", "2" });
ResultSet rs = prep.executeQuery();
</pre>
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
482 483 
<h3>Optimization Examples</h3>
<p>
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
484 
See <code>src/test/org/h2/samples/optimizations.sql</code> for a few examples of queries
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
485 486 487 
that benefit from special optimizations built into the database.
</p>
Thomas Mueller's avatar
--no commit message
c7038f81
 
Thomas Mueller committed
488 489 490 491 492 493 
<h3>Cache Size and Type</h3>
<p>
By default the cache size of H2 is quite small. Consider using a larger cache size, or enable
the second level soft reference cache. See also <a href="features.html#cache_settings">Cache Settings</a>.
</p>
buckyballs's avatar
Docs-Performance: JVM choice & Data Types
4c33d834
 
buckyballs committed
494 495 496 
<h3>Data Types</h3>
<p>
Each data type has different storage and performance characteristics:
Thomas Mueller's avatar
Formatting fixes.
0f61802b
 
Thomas Mueller committed
497 
</p>
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
498 499 
<ul><li>The <code>DECIMAL/NUMERIC</code> type is slower
    and requires more storage than the <code>REAL</code> and <code>DOUBLE</code> types.
buckyballs's avatar
Docs-Performance: JVM choice & Data Types
4c33d834
 
buckyballs committed
500 
</li><li>Text types are slower to read, write, and compare than numeric types and generally require more storage.
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
501 502 503 504 505 506 507 508 
</li><li>See <a href="advanced.html#large_objects">Large Objects</a> for information on
    <code>BINARY</code> vs. <code>BLOB</code>
    and <code>VARCHAR</code> vs. <code>CLOB</code> performance.
</li><li>Parsing and formatting takes longer for the
    <code>TIME</code>, <code>DATE</code>, and
    <code>TIMESTAMP</code> types than the numeric types.
</li><li><code>SMALLINT/TINYINT/BOOLEAN</code> are not significantly smaller or faster
    to work with than <code>INTEGER</code> in most modes.
Thomas Mueller's avatar
Formatting fixes.
0f61802b
 
Thomas Mueller committed
509 
</li></ul>
buckyballs's avatar
Docs-Performance: JVM choice & Data Types
4c33d834
 
buckyballs committed
510
Thomas Mueller's avatar
Documentation
8d64639e
 
Thomas Mueller committed
511 512 513 514 515 516 517 518 519 520 521 522 523 524 
<h3>Sorted Insert Optimization</h3>
<p>
To reduce disk space usage and speed up table creation, an
optimization for sorted inserts is available. When used, b-tree pages
are split at the insertion point. To use this optimization, add <code>SORTED</code>
before the <code>SELECT</code> statement:
</p>
<pre>
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR) AS
    SORTED SELECT X, SPACE(100) FROM SYSTEM_RANGE(1, 100);
INSERT INTO TEST
    SORTED SELECT X, SPACE(100) FROM SYSTEM_RANGE(101, 200);
</pre>
Thomas Mueller's avatar
--no commit message
43bce1b4
 
Thomas Mueller committed
525 526 527 528 529 530 531 532 533 534 535 536 537 
<h2 id="built_in_profiler">Using the Built-In Profiler</h2>
<p>
A very simple Java profiler is built-in. To use it, use the following template:
</p>
<pre>
import org.h2.util.Profiler;
Profiler prof = new Profiler();
prof.startCollecting();
// .... some long running process, at least a few seconds
prof.stopCollecting();
System.out.println(prof.getTop(3));
</pre>
Thomas Mueller's avatar
Documentation.
ee86e638
 
Thomas Mueller committed
538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 
<h2 id="application_profiling">Application Profiling</h2>

<h3>Analyze First</h3>
<p>
Before trying to optimize performance, it is important to understand where the problem is (what part of the application is slow).
Blind optimization or optimization based on guesses should be avoided, because usually it is not an efficient strategy.
There are various ways to analyze an application. Sometimes two implementations can be compared using
<code>System.currentTimeMillis()</code>. But this does not work for complex applications with many modules, and for memory problems.
</p>
<p>
A simple way to profile an application is to use the built-in profiling tool of java. Example:
</p>
<pre>
java -Xrunhprof:cpu=samples,depth=16 com.acme.Test
</pre>
<p>
Unfortunately, it is only possible to profile the application from start to end. Another solution is to create
a number of full thread dumps. To do that, first run <code>jps -l</code> to get the process id, and then
run <code>jstack &lt;pid&gt;</code> or <code>kill -QUIT &lt;pid&gt;</code> (Linux) or press
Ctrl+C (Windows).
</p>
<p>
A simple profiling tool is included in H2. To use it, the application needs to be changed slightly. Example:
</p>
<pre>
import org.h2.util;
...
Thomas Mueller's avatar
Documentation.
f3f6c2a9
 
Thomas Mueller committed
565 
Profiler profiler = new Profiler();
Thomas Mueller's avatar
Documentation.
ee86e638
 
Thomas Mueller committed
566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 
profiler.startCollecting();
// application code
System.out.println(profiler.getTop(3));
</pre>
<p>
The profiler is built into the H2 Console tool, to analyze databases that open slowly.
To use it, run the H2 Console, and then click on 'Test Connection'.
Afterwards, click on "Test successful" and you get the most common stack traces,
which helps to find out why it took so long to connect. You will only get the stack traces
if opening the database took more than a few seconds.
</p>

<h2 id="database_profiling">Database Profiling</h2>
<p>
The <code>ConvertTraceFile</code> tool generates SQL statement statistics at the end of the SQL script file.
The format used is similar to the profiling data generated when using <code>java -Xrunhprof</code>.
For this to work, the trace level needs to be 2 or higher (<code>TRACE_LEVEL_FILE=2</code>).
The easiest way to set the trace level is to append the setting to the database URL, for example:
<code>jdbc:h2:~/test;TRACE_LEVEL_FILE=2</code> or <code>jdbc:h2:tcp://localhost/~/test;TRACE_LEVEL_FILE=2</code>.
As an example, execute the the following script using the H2 Console:
</p>
<pre>
SET TRACE_LEVEL_FILE 2;
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
@LOOP 1000 INSERT INTO TEST VALUES(?, ?);
SET TRACE_LEVEL_FILE 0;
</pre>
<p>
After running the test case, convert the <code>.trace.db</code> file using the <code>ConvertTraceFile</code> tool.
The trace file is located in the same directory as the database file.
</p>
<pre>
java -cp h2*.jar org.h2.tools.ConvertTraceFile
    -traceFile "~/test.trace.db" -script "~/test.sql"
</pre>
<p>
The generated file <code>test.sql</code> will contain the SQL statements as well as the
following profiling data (results vary):
</p>
<pre>
-----------------------------------------
-- SQL Statement Statistics
-- time: total time in milliseconds (accumulated)
-- count: how many times the statement ran
-- result: total update count or row count
-----------------------------------------
-- self accu    time   count  result sql
--  62%  62%     158    1000    1000 INSERT INTO TEST VALUES(?, ?);
Thomas Mueller's avatar
Documentation (add "viewport")
f504aaa1
 
Thomas Mueller committed
615 
--  37% 100%      93       1       0 CREATE TABLE TEST(ID INT PRIMARY KEY...
Thomas Mueller's avatar
Documentation.
ee86e638
 
Thomas Mueller committed
616 617 618 619 
--   0% 100%       0       1       0 DROP TABLE IF EXISTS TEST;
--   0% 100%       0       1       0 SET TRACE_LEVEL_FILE 3;
</pre>
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 
<h2 id="explain_plan">Statement Execution Plans</h2>
<p>
The SQL statement <code>EXPLAIN</code> displays the indexes and optimizations the database uses for a statement.
The following statements support <code>EXPLAIN</code>: <code>SELECT, UPDATE, DELETE, MERGE, INSERT</code>.
The following query shows that the database uses the primary key index to search for rows:
</p>
<pre>
EXPLAIN SELECT * FROM TEST WHERE ID=1;
SELECT
    TEST.ID,
    TEST.NAME
FROM PUBLIC.TEST
    /* PUBLIC.PRIMARY_KEY_2: ID = 1 */
WHERE ID = 1
</pre>
<p>
For joins, the tables in the execution plan are sorted in the order they are processed.
The following query shows the database first processes the table <code>INVOICE</code> (using the primary key).
For each row, it will additionally check that the value of the column <code>AMOUNT</code> is larger than zero,
and for those rows the database will search in the table <code>CUSTOMER</code> (using the primary key).
The query plan contains some redundancy so it is a valid statement.
</p>
<pre>
CREATE TABLE CUSTOMER(ID IDENTITY, NAME VARCHAR);
CREATE TABLE INVOICE(ID IDENTITY,
    CUSTOMER_ID INT REFERENCES CUSTOMER(ID),
    AMOUNT NUMBER);

EXPLAIN SELECT I.ID, C.NAME FROM CUSTOMER C, INVOICE I
WHERE I.ID=10 AND AMOUNT>0 AND C.ID=I.CUSTOMER_ID;

SELECT
    I.ID,
    C.NAME
FROM PUBLIC.INVOICE I
    /* PUBLIC.PRIMARY_KEY_9: ID = 10 */
    /* WHERE (I.ID = 10)
        AND (AMOUNT > 0)
    */
INNER JOIN PUBLIC.CUSTOMER C
    /* PUBLIC.PRIMARY_KEY_5: ID = I.CUSTOMER_ID */
    ON 1=1
WHERE (C.ID = I.CUSTOMER_ID)
    AND ((I.ID = 10)
    AND (AMOUNT > 0))
</pre>

<h3>Displaying the Scan Count</h3>
<p>
<code>EXPLAIN ANALYZE</code> additionally shows the scanned rows per table and pages read from disk per table or index.
This will actually execute the query, unlike <code>EXPLAIN</code> which only prepares it.
The following query scanned 1000 rows, and to do that had to read 85 pages from the data area of the table.
Running the query twice will not list the pages read from disk, because they are now in the cache.
The <code>tableScan</code> means this query doesn't use an index.
</p>
<pre>
EXPLAIN ANALYZE SELECT * FROM TEST;
SELECT
    TEST.ID,
    TEST.NAME
FROM PUBLIC.TEST
    /* PUBLIC.TEST.tableScan */
    /* scanCount: 1000 */
/*
total: 85
TEST.TEST_DATA read: 85 (100%)
*/
</pre>
Thomas Mueller's avatar
Documentation.
a24fe37c
 
Thomas Mueller committed
688 
<p>
Thomas Mueller's avatar
Documentation.
f6c0a827
 
Thomas Mueller committed
689 690 
The cache will prevent the pages are read twice. H2 reads all columns of the row
unless only the columns in the index are read. Except for large CLOB and BLOB, which are not store in the table.
Thomas Mueller's avatar
Documentation.
a24fe37c
 
Thomas Mueller committed
691 
</p>
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 

<h3>Special Optimizations</h3>
<p>
For certain queries, the database doesn't need to read all rows, or doesn't need to sort the result even if <code>ORDER BY</code> is used.
</p><p>
For queries of the form <code>SELECT COUNT(*), MIN(ID), MAX(ID) FROM TEST</code>, the query plan includes the line
<code>/* direct lookup */</code> if the data can be read from an index.
</p><p>
For queries of the form <code>SELECT DISTINCT CUSTOMER_ID FROM INVOICE</code>, the query plan includes the line
<code>/* distinct */</code> if there is an non-unique or multi-column index on this column, and if this column has a low selectivity.
</p><p>
For queries of the form <code>SELECT * FROM TEST ORDER BY ID</code>, the query plan includes the line
<code>/* index sorted */</code> to indicate there is no separate sorting required.
</p><p>
For queries of the form <code>SELECT * FROM TEST GROUP BY ID ORDER BY ID</code>, the query plan includes the line
<code>/* group sorted */</code> to indicate there is no separate sorting required.
</p>
Thomas Mueller's avatar
Documentation.
fa2f2473
 
Thomas Mueller committed
710 711 712 713 714 715 716 717 718 719 
<h2 id="storage_and_indexes">How Data is Stored and How Indexes Work</h2>
<p>
Internally, each row in a table is identified by a unique number, the row id.
The rows of a table are stored with the row id as the key.
The row id is a number of type long.
If a table has a single column primary key of type <code>INT</code> or <code>BIGINT</code>,
then the value of this column is the row id, otherwise the database generates the row id automatically.
There is a (non-standard) way to access the row id: using the <code>_ROWID_</code> pseudo-column:
</p>
<pre>
Thomas Mueller's avatar
Documentation
fe2cf166
 
Thomas Mueller committed
720 
CREATE TABLE ADDRESS(FIRST_NAME VARCHAR,
Thomas Mueller's avatar
Documentation (add "viewport")
f504aaa1
 
Thomas Mueller committed
721 
    NAME VARCHAR, CITY VARCHAR, PHONE VARCHAR);
Thomas Mueller's avatar
Documentation.
fa2f2473
 
Thomas Mueller committed
722 723 724 725 726 727 728 729 730 731 732 733 734 735 
INSERT INTO ADDRESS VALUES('John', 'Miller', 'Berne', '123 456 789');
INSERT INTO ADDRESS VALUES('Philip', 'Jones', 'Berne', '123 012 345');
SELECT _ROWID_, * FROM ADDRESS;
</pre>
<p>
The data is stored in the database as follows:
</p>
<table>
<tr><th>_ROWID_</th><th>FIRST_NAME</th><th>NAME</th><th>CITY</th><th>PHONE</th></tr>
<tr><td>1</td><td>John</td><td>Miller</td><td>Berne</td><td>123 456 789</td></tr>
<tr><td>2</td><td>Philip</td><td>Jones</td><td>Berne</td><td>123 012 345</td></tr>
</table>
<p>
Access by row id is fast because the data is sorted by this key.
Thomas Mueller's avatar
Documentation.
15310cdb
 
Thomas Mueller committed
736 
Please note the row id is not available until after the row was added (that means, it can not be used in computed columns or constraints).
Thomas Mueller's avatar
Documentation.
fa2f2473
 
Thomas Mueller committed
737 738 739 740 741 742 743 744 745 746 747 748 749 750 
If the query condition does not contain the row id (and if no other index can be used), then all rows of the table are scanned.
A table scan iterates over all rows in the table, in the order of the row id.
To find out what strategy the database uses to retrieve the data, use <code>EXPLAIN SELECT</code>:
</p>
<pre>
SELECT * FROM ADDRESS WHERE NAME = 'Miller';

EXPLAIN SELECT PHONE FROM ADDRESS WHERE NAME = 'Miller';
SELECT
    PHONE
FROM PUBLIC.ADDRESS
    /* PUBLIC.ADDRESS.tableScan */
WHERE NAME = 'Miller';
</pre>
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
751 752 

<h3>Indexes</h3>
Thomas Mueller's avatar
Documentation.
fa2f2473
 
Thomas Mueller committed
753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 
<p>
An index internally is basically just a table that contains the indexed column(s), plus the row id:
</p>
<pre>
CREATE INDEX INDEX_PLACE ON ADDRESS(CITY, NAME, FIRST_NAME);
</pre>
<p>
In the index, the data is sorted by the indexed columns.
So this index contains the following data:
</p>
<table>
<tr><th>CITY</th><th>NAME</th><th>FIRST_NAME</th><th>_ROWID_</th></tr>
<tr><td>Berne</td><td>Jones</td><td>Philip</td><td>2</td></tr>
<tr><td>Berne</td><td>Miller</td><td>John</td><td>1</td></tr>
</table>
<p>
When the database uses an index to query the data, it searches the index for the given data,
and (if required) reads the remaining columns in the main data table (retrieved using the row id).
Thomas Mueller's avatar
Documentation.
4485dc9f
 
Thomas Mueller committed
771 772 
An index on city, name, and first name (multi-column index) allows to quickly search for rows when the city, name, and first name are known.
If only the city and name, or only the city is known, then this index is also used (so creating an additional index on just the city is not needed).
Thomas Mueller's avatar
Documentation.
fa2f2473
 
Thomas Mueller committed
773 774 775 776 
This index is also used when reading all rows, sorted by the indexed columns.
However, if only the first name is known, then this index is not used:
</p>
<pre>
Thomas Mueller's avatar
Documentation
fe2cf166
 
Thomas Mueller committed
777 778 
EXPLAIN SELECT PHONE FROM ADDRESS
    WHERE CITY = 'Berne' AND NAME = 'Miller'
Thomas Mueller's avatar
Documentation (add "viewport")
f504aaa1
 
Thomas Mueller committed
779 
    AND FIRST_NAME = 'John';
Thomas Mueller's avatar
Documentation.
fa2f2473
 
Thomas Mueller committed
780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 
SELECT
    PHONE
FROM PUBLIC.ADDRESS
    /* PUBLIC.INDEX_PLACE: FIRST_NAME = 'John'
        AND CITY = 'Berne'
        AND NAME = 'Miller'
     */
WHERE (FIRST_NAME = 'John')
    AND ((CITY = 'Berne')
    AND (NAME = 'Miller'));

EXPLAIN SELECT PHONE FROM ADDRESS WHERE CITY = 'Berne';
SELECT
    PHONE
FROM PUBLIC.ADDRESS
    /* PUBLIC.INDEX_PLACE: CITY = 'Berne' */
WHERE CITY = 'Berne';

EXPLAIN SELECT * FROM ADDRESS ORDER BY CITY, NAME, FIRST_NAME;
SELECT
    ADDRESS.FIRST_NAME,
    ADDRESS.NAME,
    ADDRESS.CITY,
    ADDRESS.PHONE
FROM PUBLIC.ADDRESS
    /* PUBLIC.INDEX_PLACE */
ORDER BY 3, 2, 1
/* index sorted */;

EXPLAIN SELECT PHONE FROM ADDRESS WHERE FIRST_NAME = 'John';
SELECT
    PHONE
FROM PUBLIC.ADDRESS
    /* PUBLIC.ADDRESS.tableScan */
WHERE FIRST_NAME = 'John';
</pre>
<p>
If your application often queries the table for a phone number, then it makes sense to create
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
818 819 820 821 822 823 824 
an additional index on it:
</p>
<pre>
CREATE INDEX IDX_PHONE ON ADDRESS(PHONE);
</pre>
<p>
This index contains the phone number, and the row id:
Thomas Mueller's avatar
Documentation.
fa2f2473
 
Thomas Mueller committed
825 826 827 828 829 830 831 
</p>
<table>
<tr><th>PHONE</th><th>_ROWID_</th></tr>
<tr><td>123 012 345</td><td>2</td></tr>
<tr><td>123 456 789</td><td>1</td></tr>
</table>
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
832 
<h3>Using Multiple Indexes</h3>
Thomas Mueller's avatar
Documentation.
ee86e638
 
Thomas Mueller committed
833 
<p>
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
834 835 836 837 838 
Within a query, only one index per logical table is used.
Using the condition <code>PHONE = '123 567 789' OR CITY = 'Berne'</code>
would use a table scan instead of first using the index on the phone number and then the index on the city.
It makes sense to write two queries and combine then using <code>UNION</code>.
In this case, each individual query uses a different index:
Thomas Mueller's avatar
Documentation.
ee86e638
 
Thomas Mueller committed
839 840 
</p>
<pre>
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
841 842 
EXPLAIN SELECT NAME FROM ADDRESS WHERE PHONE = '123 567 789'
UNION SELECT NAME FROM ADDRESS WHERE CITY = 'Berne';
Thomas Mueller's avatar
Documentation.
ee86e638
 
Thomas Mueller committed
843
Thomas Mueller's avatar
Documentation.
1f12ebf1
 
Thomas Mueller committed
844 845 846 847 848 849 850 851 852 853 854 
(SELECT
    NAME
FROM PUBLIC.ADDRESS
    /* PUBLIC.IDX_PHONE: PHONE = '123 567 789' */
WHERE PHONE = '123 567 789')
UNION
(SELECT
    NAME
FROM PUBLIC.ADDRESS
    /* PUBLIC.INDEX_PLACE: CITY = 'Berne' */
WHERE CITY = 'Berne')
Thomas Mueller's avatar
Documentation.
ee86e638
 
Thomas Mueller committed
855 856 
</pre>
Thomas Mueller's avatar
docs
da30c87b
 
Thomas Mueller committed
857 
<h2 id="fast_import">Fast Database Import</h2>
Thomas Mueller's avatar
Improved docs.
27d7b7d3
 
Thomas Mueller committed
858 859 860 
<p>
To speed up large imports, consider using the following options temporarily:
</p>
Thomas Mueller's avatar
Documentation.
70b9adab
 
Thomas Mueller committed
861 862 
<ul><li><code>SET LOG 0</code> (disabling the transaction log)
</li><li><code>SET CACHE_SIZE</code> (a large cache is faster)
Thomas Mueller's avatar
Documentation: improved website translation
20294dfb
 
Thomas Mueller committed
863 864 
</li><li><code>SET LOCK_MODE 0</code> (disable locking)
</li><li><code>SET UNDO_LOG 0</code> (disable the session undo log)
Thomas Mueller's avatar
Improved docs.
27d7b7d3
 
Thomas Mueller committed
865 866 867 
</li></ul>
<p>
These options can be set in the database URL:
Thomas Mueller's avatar
Documentation.
70b9adab
 
Thomas Mueller committed
868 
<code>jdbc:h2:~/test;LOG=0;CACHE_SIZE=65536;LOCK_MODE=0;UNDO_LOG=0</code>.
Thomas Mueller's avatar
Improved docs.
27d7b7d3
 
Thomas Mueller committed
869 870 
Most of those options are not recommended for regular use, that means you need to reset them after use.
</p>
Thomas Mueller's avatar
Documentation.
f8ee8f43
 
Thomas Mueller committed
871 872 873 874 875 
<p>
If you have to import a lot of rows, use a PreparedStatement or use CSV import.
Please note that <code>CREATE TABLE(...) ... AS SELECT ...</code>
is faster than <code>CREATE TABLE(...); INSERT INTO ... SELECT ...</code>.
</p>
Thomas Mueller's avatar
Improved docs.
27d7b7d3
 
Thomas Mueller committed
876
Thomas Mueller's avatar
use unix newline, no trailing spaces
1e7406b5
 
Thomas Mueller committed
877 
<!-- [close] { --></div></td></tr></table><!-- } --><!-- analytics --></body></html>