Comparing HyperXtremSQL™ with HSQLDB
HyperXtremeSQL™ is significantly faster than HSQLDB. Pure Memory Engine operations, already very fast in HSQLDB, are faster by tens of percentage points while essential Disk Engine operations are several times faster than HSQLDB.
Memory Engine Comparison
Pure Memory engine operations show a marked increase in speed in all common operations.
| HSQLDB timings | HXSQL timings | speedup | ||||
| OPERATION | rows | time (ms) | rows / s | time | rows / s | |
| insert | 1,024,000 | 1,3875 | 73,796 | 10,813 | 94,692 | 1.28 |
| count (index on id) | 1,024,000 | 750 | 1,363,515 | 671 | 1,523,809 | 1.12 |
| select random id | 1,024,000 | 9,032 | 113,362 | 5,063 | 202,211 | 1.78 |
| select random zip (zip table) | 1,024,000 | 3,812 | 268,554 | 3,516 | 291,157 | 1.08 |
| update with random id | 1,024,000 | 34,000 | 30,116 | 19,078 | 53,671 | 1.78 |
| count (index on id) | 1,024,000 | 734 | 1,393,197 | 593 | 1,723,905 | 1.24 |
| delete with random id | 128,000 | 1,860 | 68,780 | 1,141 | 112,084 | 1.63 |
| count (index on id) | 896,000 | 704 | 1,452,482 | 500 | 2,043,912 | 1.41 |
| total test time | 65,703 | 42,610 | 1.54 | |||
Disk Engine Comparison
Disk Engine operations are much faster than HSQLDB in all configurations.
In the test runs below, the operating system provides high-speed caching of data via a memory-mapped file for both HSQLDB and HXSQL. COUNT, SELECT, UPDATE and DELETE queries run at several times the speed of HSQLDB. The only exception in all Disk Engine tests is shutdown speed after extensive changes. This is due to the fact that HXSQL manages the memory cache more effectively and keeps modified rows much longer than HSQLDB, resulting in a larger number of rows to be saved at shutdown.
| HSQLDB timings | HXSQL timings | speedup | ||||
| OPERATION | rows | time (ms) | rows / s | time | rows / s | |
| insert | 256,000 | 15,953 | 16,046 | 11,422 | 22,410 | 1.40 |
| shutdown | 1,859 | 1,734 | 1.07 | |||
| reopen | 63 | 47 | 1.34 | |||
| count (index on id) | 256,000 | 6,484 | 39,475 | 547 | 467,153 | 11.85 |
| select random id | 256,000 | 54,641 | 4,685 | 4,703 | 54,421 | 11.62 |
| select random zip (zip table) | 256,000 | 1,062 | 240,827 | 922 | 277,356 | 1.15 |
| update with random id | 256,000 | 78,766 | 3,250 | 19,719 | 12,981 | 3.99 |
| count (index on id) | 256,000 | 6,609 | 38,729 | 422 | 605,200 | 15.66 |
| delete with random id | 32,000 | 8,266 | 3,870 | 1,187 | 26,936 | 6.96 |
| count (index on id) | 224,000 | 5,812 | 44,039 | 360 | 709,141 | 16.14 |
| shutdown | 2,328 | 3,046 | ||||
| total test time | 181,969 | 44,171 |
4.12
|
|||
In the second set of test runs with the same number of rows, the memory-mapped file is not used and there is less reliance on the operating system caching mechanisms. The speedup is generally higher than the previous test, with both SELECT and DELETE queries running at about 12 times the speed.
| HSQLDB timings | HXSQL timings | speedup | ||||
| OPERATION | rows | time (ms) | rows / s | time | rows / s | |
| insert | 256,000 | 19,360 | 13,222 | 11,375 | 22,503 | 1.70 |
| shutdown | 1,968 | 1,328 | 1.48 | |||
| reopen | 63 | 47 | 1.34 | |||
| count (index on id) | 256,000 | 15,672 | 16,333 | 594 | 430,252 | 26.38 |
| select random id | 256,000 | 131,640 | 1,944 | 10,625 | 24,091 | 12.39 |
| select random zip (zip table) | 256,000 | 1,032 | 247,821 | 907 | 281,938 | 1.14 |
| update with random id | 256,000 | 190,484 | 1,343 | 69,187 | 3,700 | 2.75 |
| count (index on id) | 256,000 | 16,031 | 15,968 | 469 | 544,680 | 34.18 |
| delete with random id | 32,000 | 22,625 | 1,414 | 1,906 | 16,780 | 11.87 |
| count (index on id) | 224,000 | 14,188 | 18,042 | 406 | 628,992 | 34.95 |
| shutdown | 1,281 | 3,485 | ||||
| total test time | 414,375 | 100,391 | 4.13 | |||
Tests with a larger set of data show similar speed gains. The first set of tests is with a memory-mapped file managed by the operating system. In this set of tests, more memory is allocated to the JVM and caching compared with the tests in the Speed Comparison page. This is to allow HSQLDB to perform better.
| HSQLDB timings | HXSQL timings | speedup | ||||
| OPERATION | rows | time (ms) | rows / s | time | rows / s | |
| insert | 1,024,000 | 52,594 | 19,469 | 44,344 | 23,091 | 1.19 |
| shutdown | 9,750 | 6,594 | 1.48 | |||
| reopen | 94 | 46 | 2.04 | |||
| count (index on id) | 1,024,000 | 23,734 | 43,143 | 1,891 | 541,226 | 12.55 |
| select random id | 1,024,000 | 137,609 | 7,441 | 20,109 | 50,919 | 6.84 |
| select random zip (zip table) | 1,024,000 | 4,000 | 255,936 | 3,594 | 284,840 | 1.11 |
| update with random id | 1,024,000 | 219,657 | 4,661 | 78,156 | 13,101 | 2.81 |
| count (index on id) | 1,024,000 | 18,250 | 56,106 | 1,563 | 654,731 | 11.68 |
| delete with random id | 128,000 | 20,781 | 6,159 | 4,578 | 27,953 | 4.54 |
| count (index on id) | 896,000 | 15,969 | 64,120 | 1,359 | 752,941 | 11.75 |
| shutdown | 8,875 | 8,844 | ||||
| total test time | 511,375 | 171,156 | 2.99 | |||
The second set of tests is without the memory-mapped file. Most operations show a bigger speed gain compared to the tests with the smaller set of data.
| HSQLDB timings | HXSQL timings | speedup | ||||
| OPERATION | rows | time (ms) | rows / s | time | rows / s | |
| insert | 1,024,000 | 66,907 | 15,304 | 43,328 | 23,633 | 1.54 |
| shutdown | 10,281 | 5,188 | 1.98 | |||
| reopen | 78 | 47 | 1.66 | |||
| count (index on id) | 1,024,000 | 54,797 | 18,686 | 2,015 | 507,936 | 27.19 |
| select random id | 1,024,000 | 390,641 | 2,621 | 42,094 | 24,325 | 9.28 |
| select random zip (zip table) | 1,024,000 | 3,921 | 261,091 | 3,578 | 286,113 | 1.10 |
| update with random id | 1,024,000 | 1,311,250 | 780 | 360,235 | 2,842 | 3.64 |
| count (index on id) | 1,024,000 | 66,204 | 15,467 | 1,562 | 655,150 | 42.38 |
| delete with random id | 128,000 | 169,375 | 755 | 7,406 | 17,280 | 22.87 |
| count (index on id) | 896,000 | 58,718 | 17,438 | 1,359 | 752,941 | 43.21 |
| shutdown | 4,703 | 17,282 | ||||
| total test time | 2,136,937 | 484,235 | 4.41 | |||
Summary
HyperXtremeSQL is significantly faster than HSQLDB while using less memory. When memory is limited, it can run in a much smaller memory allocation and still maintain better performance than HSQLDB.
In the above tests, all SELECT and UPDATE tests consist of 256K or 1M separate operations with auto commit, using a prepared statement but each operation with a different, randomly selected key value. DELETE operations are performed similarly but with only 1/8 of the rows deleted. Initial INSERT operations are also performed similarly but in sequential key order rather than randomly. The tests are intended to be tough and use a database access pattern that is similar to a heavily loaded application server but even more random.
Each tests was performed individually in the same environment. JRE 1.4 and Windows XP on a 3Ghz P4 with a 10,000 rpm disk and 512MB memory was used. Version 1.7.3 of each product was used.