| 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 < ?</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 <pid></code> or <code>kill -QUIT <pid></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> |