Debian Codegraph @

src/docsrc/html/performance.html

raw
1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
2 <!--
3 Copyright 2004-2019 H2 Group. Multiple-Licensed under the MPL 2.0,
4 and the EPL 1.0 (http://h2database.com/html/license.html).
5 Initial Developer: H2 Group
6 -->
7 <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
8 <head>
9 <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
10 <meta name="viewport" content="width=device-width, initial-scale=1" />
11 <title>
12 Performance
13 </title>
14 <link rel="stylesheet" type="text/css" href="stylesheet.css" />
15 <!-- [search] { -->
16 <script type="text/javascript" src="navigation.js"></script>
17 </head><body onload="frameMe();">
18 <table class="content"><tr class="content"><td class="content"><div class="contentDiv">
19 <!-- } -->
20
21 <h1>Performance</h1>
22 <a href="#performance_comparison">
23 Performance Comparison</a><br />
24 <a href="#poleposition_benchmark">
25 PolePosition Benchmark</a><br />
26 <a href="#database_performance_tuning">
27 Database Performance Tuning</a><br />
28 <a href="#built_in_profiler">
29 Using the Built-In Profiler</a><br />
30 <a href="#application_profiling">
31 Application Profiling</a><br />
32 <a href="#database_profiling">
33 Database Profiling</a><br />
34 <a href="#explain_plan">
35 Statement Execution Plans</a><br />
36 <a href="#storage_and_indexes">
37 How Data is Stored and How Indexes Work</a><br />
38 <a href="#fast_import">
39 Fast Database Import</a><br />
40
41 <h2 id="performance_comparison">Performance Comparison</h2>
42 <p>
43 In many cases H2 is faster than other
44 (open source and not open source) database engines.
45 Please note this is mostly a single connection benchmark run on one computer,
46 with many very simple operations running against the database.
47 This benchmark does not include very complex queries.
48 The embedded mode of H2 is faster than the client-server mode because
49 the per-statement overhead is greatly reduced.
50 </p>
51
52 <h3>Embedded</h3>
53 <table class="main">
54 <tr><th>Test Case</th><th>Unit</th><th>H2</th><th>HSQLDB</th><th>Derby</th></tr>
55 <tr><td>Simple: Init</td><td>ms</td><td>1019</td><td>1907</td><td>8280</td></tr>
56 <tr><td>Simple: Query (random)</td><td>ms</td><td>1304</td><td>873</td><td>1912</td></tr>
57 <tr><td>Simple: Query (sequential)</td><td>ms</td><td>835</td><td>1839</td><td>5415</td></tr>
58 <tr><td>Simple: Update (sequential)</td><td>ms</td><td>961</td><td>2333</td><td>21759</td></tr>
59 <tr><td>Simple: Delete (sequential)</td><td>ms</td><td>950</td><td>1922</td><td>32016</td></tr>
60 <tr><td>Simple: Memory Usage</td><td>MB</td><td>21</td><td>10</td><td>8</td></tr>
61 <tr><td>BenchA: Init</td><td>ms</td><td>919</td><td>2133</td><td>7528</td></tr>
62 <tr><td>BenchA: Transactions</td><td>ms</td><td>1219</td><td>2297</td><td>8541</td></tr>
63 <tr><td>BenchA: Memory Usage</td><td>MB</td><td>12</td><td>15</td><td>7</td></tr>
64 <tr><td>BenchB: Init</td><td>ms</td><td>905</td><td>1993</td><td>8049</td></tr>
65 <tr><td>BenchB: Transactions</td><td>ms</td><td>1091</td><td>583</td><td>1165</td></tr>
66 <tr><td>BenchB: Memory Usage</td><td>MB</td><td>17</td><td>11</td><td>8</td></tr>
67 <tr><td>BenchC: Init</td><td>ms</td><td>2491</td><td>4003</td><td>8064</td></tr>
68 <tr><td>BenchC: Transactions</td><td>ms</td><td>1979</td><td>803</td><td>2840</td></tr>
69 <tr><td>BenchC: Memory Usage</td><td>MB</td><td>19</td><td>22</td><td>9</td></tr>
70 <tr><td>Executed statements</td><td>#</td><td>1930995</td><td>1930995</td><td>1930995</td></tr>
71 <tr><td>Total time</td><td>ms</td><td>13673</td><td>20686</td><td>105569</td></tr>
72 <tr><td>Statements per second</td><td>#</td><td>141226</td><td>93347</td><td>18291</td></tr>
73 </table>
74
75 <h3>Client-Server</h3>
76 <table class="main">
77 <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>
78 <tr><td>Simple: Init</td><td>ms</td><td>16338</td><td>17198</td><td>27860</td><td>30156</td><td>29409</td></tr>
79 <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>
80 <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>
81 <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>
82 <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>
83 <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>
84 <tr><td>BenchA: Init</td><td>ms</td><td>12996</td><td>14720</td><td>24722</td><td>26375</td><td>26060</td></tr>
85 <tr><td>BenchA: Transactions</td><td>ms</td><td>10134</td><td>10250</td><td>18452</td><td>21453</td><td>15877</td></tr>
86 <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>
87 <tr><td>BenchB: Init</td><td>ms</td><td>15264</td><td>16889</td><td>28546</td><td>31610</td><td>29747</td></tr>
88 <tr><td>BenchB: Transactions</td><td>ms</td><td>3017</td><td>3376</td><td>1842</td><td>2771</td><td>1433</td></tr>
89 <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>
90 <tr><td>BenchC: Init</td><td>ms</td><td>14020</td><td>10407</td><td>17655</td><td>19520</td><td>17532</td></tr>
91 <tr><td>BenchC: Transactions</td><td>ms</td><td>5076</td><td>3160</td><td>6411</td><td>6063</td><td>4530</td></tr>
92 <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>
93 <tr><td>Executed statements</td><td>#</td><td>1930995</td><td>1930995</td><td>1930995</td><td>1930995</td><td>1930995</td></tr>
94 <tr><td>Total time</td><td>ms</td><td>117049</td><td>114777</td><td>244803</td><td>249215</td><td>188446</td></tr>
95 <tr><td>Statements per second</td><td>#</td><td>16497</td><td>16823</td><td>7887</td><td>7748</td><td>10246</td></tr>
96 </table>
97
98 <h3>Benchmark Results and Comments</h3>
99
100 <h4>H2</h4>
101 <p>
102 Version 1.4.177 (2014-04-12) was used for the test.
103 For most operations, the performance of H2 is about the same as for HSQLDB.
104 One situation where H2 is slow is large result sets, because they are buffered to
105 disk if more than a certain number of records are returned.
106 The advantage of buffering is: there is no limit on the result set size.
107 </p>
108
109 <h4>HSQLDB</h4>
110 <p>
111 Version 2.3.2 was used for the test.
112 Cached tables are used in this test (<code>hsqldb.default_table_type=cached</code>),
113 and the write delay is 1 second (<code>SET WRITE_DELAY 1</code>).
114 </p>
115
116 <h4>Derby</h4>
117 <p>
118 Version 10.10.1.1 was used for the test. Derby is clearly the slowest embedded database in this test.
119 This seems to be a structural problem, because all operations are really slow.
120 It will be hard for the developers of Derby to improve the performance to a reasonable level.
121 A few problems have been identified: leaving autocommit on is a problem for Derby.
122 If it is switched off during the whole test, the results are about 20% better for Derby.
123 Derby calls <code>FileChannel.force(false)</code>, but only twice per log file (not on each commit).
124 Disabling this call improves performance for Derby by about 2%.
125 Unlike H2, Derby does not call <code>FileDescriptor.sync()</code> on each checkpoint.
126 Derby supports a testing mode (system property <code>derby.system.durability=test</code>)
127 where durability is disabled. According to the documentation, this setting should be used for testing only,
128 as the database may not recover after a crash. Enabling this setting improves performance
129 by a factor of 2.6 (embedded mode) or 1.4 (server mode). Even if enabled, Derby is still less
130 than half as fast as H2 in default mode.
131 </p>
132
133 <h4>PostgreSQL</h4>
134 <p>
135 Version 9.1.5 was used for the test.
136 The following options where changed in <code>postgresql.conf:
137 fsync = off, commit_delay = 1000</code>.
138 PostgreSQL is run in server mode.
139 The memory usage number is incorrect, because only the memory usage of the JDBC driver is measured.
140 </p>
141
142 <h4>MySQL</h4>
143 <p>
144 Version 5.1.65-log was used for the test.
145 MySQL was run with the InnoDB backend.
146 The setting <code>innodb_flush_log_at_trx_commit</code>
147 (found in the <code>my.ini / my.cnf</code> file) was set to 0. Otherwise (and by default), MySQL is slow
148 (around 140 statements per second in this test) because it tries to flush the data to disk for each commit.
149 For small transactions (when autocommit is on) this is really slow.
150 But many use cases use small or relatively small transactions.
151 Too bad this setting is not listed in the configuration wizard,
152 and it always overwritten when using the wizard.
153 You need to change this setting manually in the file <code>my.ini / my.cnf</code>, and then restart the service.
154 The memory usage number is incorrect, because only the memory usage of the JDBC driver is measured.
155 </p>
156
157 <h4>Firebird</h4>
158 <p>
159 Firebird 1.5 (default installation) was tested, but the results are not published currently.
160 It is possible to run the performance test with the Firebird database,
161 and any information on how to configure Firebird for higher performance are welcome.
162 </p>
163
164 <h4>Why Oracle / MS SQL Server / DB2 are Not Listed</h4>
165 <p>
166 The license of these databases does not allow to publish benchmark results.
167 This doesn't mean that they are fast. They are in fact quite slow,
168 and need a lot of memory. But you will need to test this yourself.
169 SQLite was not tested because the JDBC driver doesn't support transactions.
170 </p>
171
172 <h3>About this Benchmark</h3>
173
174 <h4>How to Run</h4>
175 <p>
176 This test was as follows:
177 </p>
178 <pre>
179 build benchmark
180 </pre>
181
182 <h4>Separate Process per Database</h4>
183 <p>
184 For each database, a new process is started, to ensure the previous test does not impact
185 the current test.
186 </p>
187
188 <h4>Number of Connections</h4>
189 <p>
190 This is mostly a single-connection benchmark.
191 BenchB uses multiple connections; the other tests use one connection.
192 </p>
193
194 <h4>Real-World Tests</h4>
195 <p>
196 Good benchmarks emulate real-world use cases. This benchmark includes 4 test cases:
197 BenchSimple uses one table and many small updates / deletes.
198 BenchA is similar to the TPC-A test, but single connection / single threaded (see also: www.tpc.org).
199 BenchB is similar to the TPC-B test, using multiple connections (one thread per connection).
200 BenchC is similar to the TPC-C test, but single connection / single threaded.
201 </p>
202
203 <h4>Comparing Embedded with Server Databases</h4>
204 <p>
205 This is mainly a benchmark for embedded databases (where the application runs in the same
206 virtual machine as the database engine). However MySQL and PostgreSQL are not Java
207 databases and cannot be embedded into a Java application.
208 For the Java databases, both embedded and server modes are tested.
209 </p>
210
211 <h4>Test Platform</h4>
212 <p>
213 This test is run on Mac OS X 10.6. No virus scanner was used, and disk indexing was disabled.
214 The JVM used is Sun JDK 1.6.
215 </p>
216
217 <h4>Multiple Runs</h4>
218 <p>
219 When a Java benchmark is run first, the code is not fully compiled and
220 therefore runs slower than when running multiple times. A benchmark
221 should always run the same test multiple times and ignore the first run(s).
222 This benchmark runs three times, but only the last run is measured.
223 </p>
224
225 <h4>Memory Usage</h4>
226 <p>
227 It is not enough to measure the time taken, the memory usage is important as well.
228 Performance can be improved by using a bigger cache, but the amount of memory is limited.
229 HSQLDB tables are kept fully in memory by default; this benchmark
230 uses 'disk based' tables for all databases.
231 Unfortunately, it is not so easy to calculate the memory usage of PostgreSQL
232 and MySQL, because they run in a different process than the test. This benchmark currently
233 does not print memory usage of those databases.
234 </p>
235
236 <h4>Delayed Operations</h4>
237 <p>
238 Some databases delay some operations (for example flushing the buffers)
239 until after the benchmark is run. This benchmark waits between
240 each database tested, and each database runs in a different process (sequentially).
241 </p>
242
243 <h4>Transaction Commit / Durability</h4>
244 <p>
245 Durability means transaction committed to the database will not be lost.
246 Some databases (for example MySQL) try to enforce this by default by
247 calling <code>fsync()</code> to flush the buffers, but
248 most hard drives don't actually flush all data. Calling the method slows down transaction commit a lot,
249 but doesn't always make data durable. When comparing the results, it is important to
250 think about the effect. Many database suggest to 'batch' operations when possible.
251 This benchmark switches off autocommit when loading the data, and calls commit after each 1000
252 inserts. However many applications need 'short' transactions at runtime (a commit after each update).
253 This benchmark commits after each update / delete in the simple benchmark, and after each
254 business transaction in the other benchmarks. For databases that support delayed commits,
255 a delay of one second is used.
256 </p>
257
258 <h4>Using Prepared Statements</h4>
259 <p>
260 Wherever possible, the test cases use prepared statements.
261 </p>
262
263 <h4>Currently Not Tested: Startup Time</h4>
264 <p>
265 The startup time of a database engine is important as well for embedded use.
266 This time is not measured currently.
267 Also, not tested is the time used to create a database and open an existing database.
268 Here, one (wrapper) connection is opened at the start,
269 and for each step a new connection is opened and then closed.
270 </p>
271
272 <h2 id="poleposition_benchmark">PolePosition Benchmark</h2>
273 <p>
274 The PolePosition is an open source benchmark. The algorithms are all quite simple.
275 It was developed / sponsored by db4o.
276 This test was not run for a longer time, so please be aware that the results below
277 are for older database versions (H2 version 1.1, HSQLDB 1.8, Java 1.4).
278 </p>
279 <table class="main">
280 <tr><th>Test Case</th><th>Unit</th><th>H2</th><th>HSQLDB</th><th>MySQL</th></tr>
281 <tr><td>Melbourne write</td><td>ms</td><td>369</td><td>249</td><td>2022</td></tr>
282 <tr><td>Melbourne read</td><td>ms</td><td>47</td><td>49</td><td>93</td></tr>
283 <tr><td>Melbourne read_hot</td><td>ms</td><td>24</td><td>43</td><td>95</td></tr>
284 <tr><td>Melbourne delete</td><td>ms</td><td>147</td><td>133</td><td>176</td></tr>
285 <tr><td>Sepang write</td><td>ms</td><td>965</td><td>1201</td><td>3213</td></tr>
286 <tr><td>Sepang read</td><td>ms</td><td>765</td><td>948</td><td>3455</td></tr>
287 <tr><td>Sepang read_hot</td><td>ms</td><td>789</td><td>859</td><td>3563</td></tr>
288 <tr><td>Sepang delete</td><td>ms</td><td>1384</td><td>1596</td><td>6214</td></tr>
289 <tr><td>Bahrain write</td><td>ms</td><td>1186</td><td>1387</td><td>6904</td></tr>
290 <tr><td>Bahrain query_indexed_string</td><td>ms</td><td>336</td><td>170</td><td>693</td></tr>
291 <tr><td>Bahrain query_string</td><td>ms</td><td>18064</td><td>39703</td><td>41243</td></tr>
292 <tr><td>Bahrain query_indexed_int</td><td>ms</td><td>104</td><td>134</td><td>678</td></tr>
293 <tr><td>Bahrain update</td><td>ms</td><td>191</td><td>87</td><td>159</td></tr>
294 <tr><td>Bahrain delete</td><td>ms</td><td>1215</td><td>729</td><td>6812</td></tr>
295 <tr><td>Imola retrieve</td><td>ms</td><td>198</td><td>194</td><td>4036</td></tr>
296 <tr><td>Barcelona write</td><td>ms</td><td>413</td><td>832</td><td>3191</td></tr>
297 <tr><td>Barcelona read</td><td>ms</td><td>119</td><td>160</td><td>1177</td></tr>
298 <tr><td>Barcelona query</td><td>ms</td><td>20</td><td>5169</td><td>101</td></tr>
299 <tr><td>Barcelona delete</td><td>ms</td><td>388</td><td>319</td><td>3287</td></tr>
300 <tr><td>Total</td><td>ms</td><td>26724</td><td>53962</td><td>87112</td></tr>
301 </table>
302 <p>
303 There are a few problems with the PolePosition test:
304 </p>
305 <ul><li>
306 HSQLDB uses in-memory tables by default while H2 uses persistent tables. The HSQLDB version
307 included in PolePosition does not support changing this, so you need to replace
308 <code>poleposition-0.20/lib/hsqldb.jar</code> with a newer version (for example
309 <code>hsqldb-1.8.0.7.jar</code>),
310 and then use the setting
311 <code>hsqldb.connecturl=jdbc:hsqldb:file:data/hsqldb/dbbench2;hsqldb.default_table_type=cached;sql.enforce_size=true</code>
312 in the file <code>Jdbc.properties</code>.
313 </li><li>HSQLDB keeps the database open between tests, while H2 closes the database (losing all the cache).
314 To change that, use the database URL <code>jdbc:h2:file:data/h2/dbbench;DB_CLOSE_DELAY=-1</code>
315 </li><li>The amount of cache memory is quite important, specially for the PolePosition test.
316 Unfortunately, the PolePosition test does not take this into account.
317 </li></ul>
318
319 <h2 id="database_performance_tuning">Database Performance Tuning</h2>
320
321 <h3>Keep Connections Open or Use a Connection Pool</h3>
322 <p>
323 If your application opens and closes connections a lot (for example, for each request),
324 you should consider using a <a href="tutorial.html#connection_pool">connection pool</a>.
325 Opening a connection using <code>DriverManager.getConnection</code> is specially slow
326 if the database is closed. By default the database is closed if the last connection is closed.
327 </p><p>
328 If you open and close connections a lot but don't want to use a connection pool,
329 consider keeping a 'sentinel' connection open for as long as the application runs,
330 or use delayed database closing. See also
331 <a href="features.html#closing_a_database">Closing a database</a>.
332 </p>
333
334 <h3>Use a Modern JVM</h3>
335 <p>
336 Newer JVMs are faster. Upgrading to the latest version of your JVM can provide a "free" boost to performance.
337 Switching from the default Client JVM to the Server JVM using the <code>-server</code> command-line
338 option improves performance at the cost of a slight increase in start-up time.
339 </p>
340
341 <h3>Virus Scanners</h3>
342 <p>
343 Some virus scanners scan files every time they are accessed.
344 It is very important for performance that database files are not scanned for viruses.
345 The database engine never interprets the data stored in the files as programs,
346 that means even if somebody would store a virus in a database file, this would
347 be harmless (when the virus does not run, it cannot spread).
348 Some virus scanners allow to exclude files by suffix. Ensure files ending with <code>.db</code> are not scanned.
349 </p>
350
351 <h3>Using the Trace Options</h3>
352 <p>
353 If the performance hot spots are in the database engine, in many cases the performance
354 can be optimized by creating additional indexes, or changing the schema. Sometimes the
355 application does not directly generate the SQL statements, for example if an O/R mapping tool
356 is used. To view the SQL statements and JDBC API calls, you can use the trace options.
357 For more information, see <a href="features.html#trace_options">Using the Trace Options</a>.
358 </p>
359
360 <h3>Index Usage</h3>
361 <p>
362 This database uses indexes to improve the performance of
363 <code>SELECT, UPDATE, DELETE</code>.
364 If a column is used in the <code>WHERE</code> clause of a query, and if an index exists on this column,
365 then the index can be used. Multi-column indexes are used if all or the first columns of the index are used.
366 Both equality lookup and range scans are supported.
367 Indexes are used to order result sets, but only if the condition uses the same index or no index at all.
368 The results are sorted in memory if required.
369 Indexes are created automatically for primary key and unique constraints.
370 Indexes are also created for foreign key constraints, if required.
371 For other columns, indexes need to be created manually using the <code>CREATE INDEX</code> statement.
372 </p>
373
374 <h3>Index Hints</h3>
375 <p>
376 If you have determined that H2 is not using the optimal index for your query, you can use index hints to force
377 H2 to use specific indexes.
378 </p>
379 <pre>
380 SELECT * FROM TEST USE INDEX (index_name_1, index_name_2) WHERE X=1
381 </pre>
382 <p>Only indexes in the list will be used when choosing an index to use on the given table. There
383 is no significance to order in this list.
384 </p><p>
385 It is possible that no index in the list is chosen, in which case a full table scan will be used.
386 </p>
387 <p>An empty list of index names forces a full table scan to be performed.</p>
388 <p>Each index in the list must exist.</p>
389
390 <h3>How Data is Stored Internally</h3>
391 <p>
392 For persistent databases, if a table is created with a single column primary key of type <code>BIGINT, INT, SMALLINT, TINYINT</code>,
393 then the data of the table is organized in this way. This is sometimes also called a "clustered index" or
394 "index organized table".
395 </p><p>
396 H2 internally stores table data and indexes in the form of b-trees.
397 Each b-tree stores entries as a list of unique keys (one or more columns) and data (zero or more columns).
398 The table data is always organized in the form of a "data b-tree" with a single column key of type <code>long</code>.
399 If a single column primary key of type <code>BIGINT, INT, SMALLINT, TINYINT</code> is specified when creating the table
400 (or just after creating the table, but before inserting any rows),
401 then this column is used as the key of the data b-tree.
402 If no primary key has been specified, if the primary key column is of another data type,
403 or if the primary key contains more than one column,
404 then a hidden auto-increment column of type <code>BIGINT</code> is added to the table,
405 which is used as the key for the data b-tree.
406 All other columns of the table are stored within the data area of this data b-tree
407 (except for large <code>BLOB, CLOB</code> columns, which are stored externally).
408 </p><p>
409 For each additional index, one new "index b-tree" is created. The key of this b-tree consists of the indexed columns,
410 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
411 contains multiple column, or if the primary key is not of the data types listed above, then the primary key
412 is stored in a new index b-tree.
413 </p>
414
415 <h3>Optimizer</h3>
416 <p>
417 This database uses a cost based optimizer. For simple and queries and queries with medium complexity
418 (less than 7 tables in the join), the expected cost (running time) of all possible plans is calculated,
419 and the plan with the lowest cost is used. For more complex queries, the algorithm first tries
420 all possible combinations for the first few tables, and the remaining tables added using a greedy algorithm
421 (this works well for most joins). Afterwards a genetic algorithm is used to test at most 2000 distinct plans.
422 Only left-deep plans are evaluated.
423 </p>
424
425 <h3>Expression Optimization</h3>
426 <p>
427 After the statement is parsed, all expressions are simplified automatically if possible. Operations
428 are evaluated only once if all parameters are constant. Functions are also optimized, but only
429 if the function is constant (always returns the same result for the same parameter values).
430 If the <code>WHERE</code> clause is always false, then the table is not accessed at all.
431 </p>
432
433 <h3>COUNT(*) Optimization</h3>
434 <p>
435 If the query only counts all rows of a table, then the data is not accessed.
436 However, this is only possible if no <code>WHERE</code> clause is used, that means it only works for
437 queries of the form <code>SELECT COUNT(*) FROM table</code>.
438 </p>
439
440 <h3>Updating Optimizer Statistics / Column Selectivity</h3>
441 <p>
442 When executing a query, at most one index per join can be used.
443 If the same table is joined multiple times, for each join only one index is used
444 (the same index could be used for both joins, or each join could use a different index).
445 Example: for the query
446 <code>SELECT * FROM TEST T1, TEST T2 WHERE T1.NAME='A' AND T2.ID=T1.ID</code>,
447 two index can be used, in this case the index on NAME for T1 and the index on ID for T2.
448 </p><p>
449 If a table has multiple indexes, sometimes more than one index could be used.
450 Example: if there is a table <code>TEST(ID, NAME, FIRSTNAME)</code> and an index on each column,
451 then two indexes could be used for the query <code>SELECT * FROM TEST WHERE NAME='A' AND FIRSTNAME='B'</code>,
452 the index on NAME or the index on FIRSTNAME. It is not possible to use both indexes at the same time.
453 Which index is used depends on the selectivity of the column. The selectivity describes the 'uniqueness' of
454 values in a column. A selectivity of 100 means each value appears only once, and a selectivity of 1 means
455 the same value appears in many or most rows. For the query above, the index on NAME should be used
456 if the table contains more distinct names than first names.
457 </p><p>
458 The SQL statement <code>ANALYZE</code> can be used to automatically estimate the selectivity of the columns in the tables.
459 This command should be run from time to time to improve the query plans generated by the optimizer.
460 </p>
461
462 <h3>In-Memory (Hash) Indexes</h3>
463 <p>
464 Using in-memory indexes, specially in-memory hash indexes, can speed up
465 queries and data manipulation.
466 </p>
467 <p>In-memory indexes are automatically used
468 for in-memory databases, but can also be created for persistent databases
469 using <code>CREATE MEMORY TABLE</code>. In many cases,
470 the rows itself will also be kept in-memory. Please note this may cause memory
471 problems for large tables.
472 </p>
473 <p>
474 In-memory hash indexes are backed by a hash table and are usually faster than
475 regular indexes. However, hash indexes only supports direct lookup (<code>WHERE ID = ?</code>)
476 but not range scan (<code>WHERE ID &lt; ?</code>). To use hash indexes, use HASH as in:
477 <code>CREATE UNIQUE HASH INDEX</code> and
478 <code>CREATE TABLE ...(ID INT PRIMARY KEY HASH,...)</code>.
479 </p>
480
481 <h3>Use Prepared Statements</h3>
482 <p>
483 If possible, use prepared statements with parameters.
484 </p>
485
486 <h3>Prepared Statements and IN(...)</h3>
487 <p>
488 Avoid generating SQL statements with a variable size IN(...) list.
489 Instead, use a prepared statement with arrays as in the following example:
490 </p>
491 <pre>
492 PreparedStatement prep = conn.prepareStatement(
493 "SELECT * FROM TEST WHERE ID = ANY(?)");
494 prep.setObject(1, new Object[] { "1", "2" });
495 ResultSet rs = prep.executeQuery();
496 </pre>
497
498 <h3>Optimization Examples</h3>
499 <p>
500 See <code>src/test/org/h2/samples/optimizations.sql</code> for a few examples of queries
501 that benefit from special optimizations built into the database.
502 </p>
503
504 <h3>Cache Size and Type</h3>
505 <p>
506 By default the cache size of H2 is quite small. Consider using a larger cache size, or enable
507 the second level soft reference cache. See also <a href="features.html#cache_settings">Cache Settings</a>.
508 </p>
509
510 <h3>Data Types</h3>
511 <p>
512 Each data type has different storage and performance characteristics:
513 </p>
514 <ul><li>The <code>DECIMAL/NUMERIC</code> type is slower
515 and requires more storage than the <code>REAL</code> and <code>DOUBLE</code> types.
516 </li><li>Text types are slower to read, write, and compare than numeric types and generally require more storage.
517 </li><li>See <a href="advanced.html#large_objects">Large Objects</a> for information on
518 <code>BINARY</code> vs. <code>BLOB</code>
519 and <code>VARCHAR</code> vs. <code>CLOB</code> performance.
520 </li><li>Parsing and formatting takes longer for the
521 <code>TIME</code>, <code>DATE</code>, and
522 <code>TIMESTAMP</code> types than the numeric types.
523 </li><li><code>SMALLINT/TINYINT/BOOLEAN</code> are not significantly smaller or faster
524 to work with than <code>INTEGER</code> in most modes.
525 </li></ul>
526
527 <h3>Sorted Insert Optimization</h3>
528 <p>
529 To reduce disk space usage and speed up table creation, an
530 optimization for sorted inserts is available. When used, b-tree pages
531 are split at the insertion point. To use this optimization, add <code>SORTED</code>
532 before the <code>SELECT</code> statement:
533 </p>
534 <pre>
535 CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR) AS
536 SORTED SELECT X, SPACE(100) FROM SYSTEM_RANGE(1, 100);
537 INSERT INTO TEST
538 SORTED SELECT X, SPACE(100) FROM SYSTEM_RANGE(101, 200);
539 </pre>
540
541 <h2 id="built_in_profiler">Using the Built-In Profiler</h2>
542 <p>
543 A very simple Java profiler is built-in. To use it, use the following template:
544 </p>
545 <pre>
546 import org.h2.util.Profiler;
547 Profiler prof = new Profiler();
548 prof.startCollecting();
549 // .... some long running process, at least a few seconds
550 prof.stopCollecting();
551 System.out.println(prof.getTop(3));
552 </pre>
553
554 <h2 id="application_profiling">Application Profiling</h2>
555
556 <h3>Analyze First</h3>
557 <p>
558 Before trying to optimize performance, it is important to understand where the problem is (what part of the application is slow).
559 Blind optimization or optimization based on guesses should be avoided, because usually it is not an efficient strategy.
560 There are various ways to analyze an application. Sometimes two implementations can be compared using
561 <code>System.currentTimeMillis()</code>. But this does not work for complex applications with many modules, and for memory problems.
562 </p>
563 <p>
564 A simple way to profile an application is to use the built-in profiling tool of java. Example:
565 </p>
566 <pre>
567 java -Xrunhprof:cpu=samples,depth=16 com.acme.Test
568 </pre>
569 <p>
570 Unfortunately, it is only possible to profile the application from start to end. Another solution is to create
571 a number of full thread dumps. To do that, first run <code>jps -l</code> to get the process id, and then
572 run <code>jstack &lt;pid&gt;</code> or <code>kill -QUIT &lt;pid&gt;</code> (Linux) or press
573 Ctrl+C (Windows).
574 </p>
575 <p>
576 A simple profiling tool is included in H2. To use it, the application needs to be changed slightly. Example:
577 </p>
578 <pre>
579 import org.h2.util;
580 ...
581 Profiler profiler = new Profiler();
582 profiler.startCollecting();
583 // application code
584 System.out.println(profiler.getTop(3));
585 </pre>
586 <p>
587 The profiler is built into the H2 Console tool, to analyze databases that open slowly.
588 To use it, run the H2 Console, and then click on 'Test Connection'.
589 Afterwards, click on "Test successful" and you get the most common stack traces,
590 which helps to find out why it took so long to connect. You will only get the stack traces
591 if opening the database took more than a few seconds.
592 </p>
593
594 <h2 id="database_profiling">Database Profiling</h2>
595 <p>
596 The <code>ConvertTraceFile</code> tool generates SQL statement statistics at the end of the SQL script file.
597 The format used is similar to the profiling data generated when using <code>java -Xrunhprof</code>.
598 For this to work, the trace level needs to be 2 or higher (<code>TRACE_LEVEL_FILE=2</code>).
599 The easiest way to set the trace level is to append the setting to the database URL, for example:
600 <code>jdbc:h2:~/test;TRACE_LEVEL_FILE=2</code> or <code>jdbc:h2:tcp://localhost/~/test;TRACE_LEVEL_FILE=2</code>.
601 As an example, execute the following script using the H2 Console:
602 </p>
603 <pre>
604 SET TRACE_LEVEL_FILE 2;
605 DROP TABLE IF EXISTS TEST;
606 CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
607 @LOOP 1000 INSERT INTO TEST VALUES(?, ?);
608 SET TRACE_LEVEL_FILE 0;
609 </pre>
610 <p>
611 After running the test case, convert the <code>.trace.db</code> file using the <code>ConvertTraceFile</code> tool.
612 The trace file is located in the same directory as the database file.
613 </p>
614 <pre>
615 java -cp h2*.jar org.h2.tools.ConvertTraceFile
616 -traceFile "~/test.trace.db" -script "~/test.sql"
617 </pre>
618 <p>
619 The generated file <code>test.sql</code> will contain the SQL statements as well as the
620 following profiling data (results vary):
621 </p>
622 <pre>
623 -----------------------------------------
624 -- SQL Statement Statistics
625 -- time: total time in milliseconds (accumulated)
626 -- count: how many times the statement ran
627 -- result: total update count or row count
628 -----------------------------------------
629 -- self accu time count result sql
630 -- 62% 62% 158 1000 1000 INSERT INTO TEST VALUES(?, ?);
631 -- 37% 100% 93 1 0 CREATE TABLE TEST(ID INT PRIMARY KEY...
632 -- 0% 100% 0 1 0 DROP TABLE IF EXISTS TEST;
633 -- 0% 100% 0 1 0 SET TRACE_LEVEL_FILE 3;
634 </pre>
635
636 <h2 id="explain_plan">Statement Execution Plans</h2>
637 <p>
638 The SQL statement <code>EXPLAIN</code> displays the indexes and optimizations the database uses for a statement.
639 The following statements support <code>EXPLAIN</code>: <code>SELECT, UPDATE, DELETE, MERGE, INSERT</code>.
640 The following query shows that the database uses the primary key index to search for rows:
641 </p>
642 <pre>
643 EXPLAIN SELECT * FROM TEST WHERE ID=1;
644 SELECT
645 TEST.ID,
646 TEST.NAME
647 FROM PUBLIC.TEST
648 /* PUBLIC.PRIMARY_KEY_2: ID = 1 */
649 WHERE ID = 1
650 </pre>
651 <p>
652 For joins, the tables in the execution plan are sorted in the order they are processed.
653 The following query shows the database first processes the table <code>INVOICE</code> (using the primary key).
654 For each row, it will additionally check that the value of the column <code>AMOUNT</code> is larger than zero,
655 and for those rows the database will search in the table <code>CUSTOMER</code> (using the primary key).
656 The query plan contains some redundancy so it is a valid statement.
657 </p>
658 <pre>
659 CREATE TABLE CUSTOMER(ID IDENTITY, NAME VARCHAR);
660 CREATE TABLE INVOICE(ID IDENTITY,
661 CUSTOMER_ID INT REFERENCES CUSTOMER(ID),
662 AMOUNT NUMBER);
663
664 EXPLAIN SELECT I.ID, C.NAME FROM CUSTOMER C, INVOICE I
665 WHERE I.ID=10 AND AMOUNT>0 AND C.ID=I.CUSTOMER_ID;
666
667 SELECT
668 I.ID,
669 C.NAME
670 FROM PUBLIC.INVOICE I
671 /* PUBLIC.PRIMARY_KEY_9: ID = 10 */
672 /* WHERE (I.ID = 10)
673 AND (AMOUNT > 0)
674 */
675 INNER JOIN PUBLIC.CUSTOMER C
676 /* PUBLIC.PRIMARY_KEY_5: ID = I.CUSTOMER_ID */
677 ON 1=1
678 WHERE (C.ID = I.CUSTOMER_ID)
679 AND ((I.ID = 10)
680 AND (AMOUNT > 0))
681 </pre>
682
683 <h3>Displaying the Scan Count</h3>
684 <p>
685 <code>EXPLAIN ANALYZE</code> additionally shows the scanned rows per table and pages read from disk per table or index.
686 This will actually execute the query, unlike <code>EXPLAIN</code> which only prepares it.
687 The following query scanned 1000 rows, and to do that had to read 85 pages from the data area of the table.
688 Running the query twice will not list the pages read from disk, because they are now in the cache.
689 The <code>tableScan</code> means this query doesn't use an index.
690 </p>
691 <pre>
692 EXPLAIN ANALYZE SELECT * FROM TEST;
693 SELECT
694 TEST.ID,
695 TEST.NAME
696 FROM PUBLIC.TEST
697 /* PUBLIC.TEST.tableScan */
698 /* scanCount: 1000 */
699 /*
700 total: 85
701 TEST.TEST_DATA read: 85 (100%)
702 */
703 </pre>
704 <p>
705 The cache will prevent the pages are read twice. H2 reads all columns of the row
706 unless only the columns in the index are read. Except for large CLOB and BLOB, which are not store in the table.
707 </p>
708
709 <h3>Special Optimizations</h3>
710 <p>
711 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.
712 </p><p>
713 For queries of the form <code>SELECT COUNT(*), MIN(ID), MAX(ID) FROM TEST</code>, the query plan includes the line
714 <code>/* direct lookup */</code> if the data can be read from an index.
715 </p><p>
716 For queries of the form <code>SELECT DISTINCT CUSTOMER_ID FROM INVOICE</code>, the query plan includes the line
717 <code>/* distinct */</code> if there is an non-unique or multi-column index on this column, and if this column has a low selectivity.
718 </p><p>
719 For queries of the form <code>SELECT * FROM TEST ORDER BY ID</code>, the query plan includes the line
720 <code>/* index sorted */</code> to indicate there is no separate sorting required.
721 </p><p>
722 For queries of the form <code>SELECT * FROM TEST GROUP BY ID ORDER BY ID</code>, the query plan includes the line
723 <code>/* group sorted */</code> to indicate there is no separate sorting required.
724 </p>
725
726 <h2 id="storage_and_indexes">How Data is Stored and How Indexes Work</h2>
727 <p>
728 Internally, each row in a table is identified by a unique number, the row id.
729 The rows of a table are stored with the row id as the key.
730 The row id is a number of type long.
731 If a table has a single column primary key of type <code>INT</code> or <code>BIGINT</code>,
732 then the value of this column is the row id, otherwise the database generates the row id automatically.
733 There is a (non-standard) way to access the row id: using the <code>_ROWID_</code> pseudo-column:
734 </p>
735 <pre>
736 CREATE TABLE ADDRESS(FIRST_NAME VARCHAR,
737 NAME VARCHAR, CITY VARCHAR, PHONE VARCHAR);
738 INSERT INTO ADDRESS VALUES('John', 'Miller', 'Berne', '123 456 789');
739 INSERT INTO ADDRESS VALUES('Philip', 'Jones', 'Berne', '123 012 345');
740 SELECT _ROWID_, * FROM ADDRESS;
741 </pre>
742 <p>
743 The data is stored in the database as follows:
744 </p>
745 <table>
746 <tr><th>_ROWID_</th><th>FIRST_NAME</th><th>NAME</th><th>CITY</th><th>PHONE</th></tr>
747 <tr><td>1</td><td>John</td><td>Miller</td><td>Berne</td><td>123 456 789</td></tr>
748 <tr><td>2</td><td>Philip</td><td>Jones</td><td>Berne</td><td>123 012 345</td></tr>
749 </table>
750 <p>
751 Access by row id is fast because the data is sorted by this key.
752 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).
753 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.
754 A table scan iterates over all rows in the table, in the order of the row id.
755 To find out what strategy the database uses to retrieve the data, use <code>EXPLAIN SELECT</code>:
756 </p>
757 <pre>
758 SELECT * FROM ADDRESS WHERE NAME = 'Miller';
759
760 EXPLAIN SELECT PHONE FROM ADDRESS WHERE NAME = 'Miller';
761 SELECT
762 PHONE
763 FROM PUBLIC.ADDRESS
764 /* PUBLIC.ADDRESS.tableScan */
765 WHERE NAME = 'Miller';
766 </pre>
767
768 <h3>Indexes</h3>
769 <p>
770 An index internally is basically just a table that contains the indexed column(s), plus the row id:
771 </p>
772 <pre>
773 CREATE INDEX INDEX_PLACE ON ADDRESS(CITY, NAME, FIRST_NAME);
774 </pre>
775 <p>
776 In the index, the data is sorted by the indexed columns.
777 So this index contains the following data:
778 </p>
779 <table>
780 <tr><th>CITY</th><th>NAME</th><th>FIRST_NAME</th><th>_ROWID_</th></tr>
781 <tr><td>Berne</td><td>Jones</td><td>Philip</td><td>2</td></tr>
782 <tr><td>Berne</td><td>Miller</td><td>John</td><td>1</td></tr>
783 </table>
784 <p>
785 When the database uses an index to query the data, it searches the index for the given data,
786 and (if required) reads the remaining columns in the main data table (retrieved using the row id).
787 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.
788 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).
789 This index is also used when reading all rows, sorted by the indexed columns.
790 However, if only the first name is known, then this index is not used:
791 </p>
792 <pre>
793 EXPLAIN SELECT PHONE FROM ADDRESS
794 WHERE CITY = 'Berne' AND NAME = 'Miller'
795 AND FIRST_NAME = 'John';
796 SELECT
797 PHONE
798 FROM PUBLIC.ADDRESS
799 /* PUBLIC.INDEX_PLACE: FIRST_NAME = 'John'
800 AND CITY = 'Berne'
801 AND NAME = 'Miller'
802 */
803 WHERE (FIRST_NAME = 'John')
804 AND ((CITY = 'Berne')
805 AND (NAME = 'Miller'));
806
807 EXPLAIN SELECT PHONE FROM ADDRESS WHERE CITY = 'Berne';
808 SELECT
809 PHONE
810 FROM PUBLIC.ADDRESS
811 /* PUBLIC.INDEX_PLACE: CITY = 'Berne' */
812 WHERE CITY = 'Berne';
813
814 EXPLAIN SELECT * FROM ADDRESS ORDER BY CITY, NAME, FIRST_NAME;
815 SELECT
816 ADDRESS.FIRST_NAME,
817 ADDRESS.NAME,
818 ADDRESS.CITY,
819 ADDRESS.PHONE
820 FROM PUBLIC.ADDRESS
821 /* PUBLIC.INDEX_PLACE */
822 ORDER BY 3, 2, 1
823 /* index sorted */;
824
825 EXPLAIN SELECT PHONE FROM ADDRESS WHERE FIRST_NAME = 'John';
826 SELECT
827 PHONE
828 FROM PUBLIC.ADDRESS
829 /* PUBLIC.ADDRESS.tableScan */
830 WHERE FIRST_NAME = 'John';
831 </pre>
832 <p>
833 If your application often queries the table for a phone number, then it makes sense to create
834 an additional index on it:
835 </p>
836 <pre>
837 CREATE INDEX IDX_PHONE ON ADDRESS(PHONE);
838 </pre>
839 <p>
840 This index contains the phone number, and the row id:
841 </p>
842 <table>
843 <tr><th>PHONE</th><th>_ROWID_</th></tr>
844 <tr><td>123 012 345</td><td>2</td></tr>
845 <tr><td>123 456 789</td><td>1</td></tr>
846 </table>
847
848 <h3>Using Multiple Indexes</h3>
849 <p>
850 Within a query, only one index per logical table is used.
851 Using the condition <code>PHONE = '123 567 789' OR CITY = 'Berne'</code>
852 would use a table scan instead of first using the index on the phone number and then the index on the city.
853 It makes sense to write two queries and combine then using <code>UNION</code>.
854 In this case, each individual query uses a different index:
855 </p>
856 <pre>
857 EXPLAIN SELECT NAME FROM ADDRESS WHERE PHONE = '123 567 789'
858 UNION SELECT NAME FROM ADDRESS WHERE CITY = 'Berne';
859
860 (SELECT
861 NAME
862 FROM PUBLIC.ADDRESS
863 /* PUBLIC.IDX_PHONE: PHONE = '123 567 789' */
864 WHERE PHONE = '123 567 789')
865 UNION
866 (SELECT
867 NAME
868 FROM PUBLIC.ADDRESS
869 /* PUBLIC.INDEX_PLACE: CITY = 'Berne' */
870 WHERE CITY = 'Berne')
871 </pre>
872
873 <h2 id="fast_import">Fast Database Import</h2>
874 <p>
875 To speed up large imports, consider using the following options temporarily:
876 </p>
877 <ul><li><code>SET LOG 0</code> (disabling the transaction log)
878 </li><li><code>SET CACHE_SIZE</code> (a large cache is faster)
879 </li><li><code>SET LOCK_MODE 0</code> (disable locking)
880 </li><li><code>SET UNDO_LOG 0</code> (disable the session undo log)
881 </li></ul>
882 <p>
883 These options can be set in the database URL:
884 <code>jdbc:h2:~/test;LOG=0;CACHE_SIZE=65536;LOCK_MODE=0;UNDO_LOG=0</code>.
885 Most of those options are not recommended for regular use, that means you need to reset them after use.
886 </p>
887 <p>
888 If you have to import a lot of rows, use a PreparedStatement or use CSV import.
889 Please note that <code>CREATE TABLE(...) ... AS SELECT ...</code>
890 is faster than <code>CREATE TABLE(...); INSERT INTO ... SELECT ...</code>.
891 </p>
892
893 <!-- [close] { --></div></td></tr></table><!-- } --><!-- analytics --></body></html>