Introduction
The HyperXtremeSQL™ core uses advanced coding techniques and fast algorithms for maximum speed. Furthermore, it can be configured to use memory effectively: large amounts of memory for maximum speed, or very small amounts where resources are limited. This allows it to perform different roles in different deployment scenarios.
Pure Memory Engine for Sheer Performance
This configuration allows all the data to reside in memory without any disk access. This mode can be used inside an application to process SQL queries on internal data. A typical use in J2EE servers is to provide a fast local database serving the front-end while changed data is synchronized with a conventional RDBMS or a remote backend. Data can be inserted, queried and modified using standard SQL and JDBC.
In the test below 1 million rows were inserted into one table and 4,000 rows into another at over 90,000 rows per second. Evenly-distributed random, single-row SELECT queries were performed at over 200,000 operations per second. Random single-row UPDATE and DELETE queries performed at over 50,000 and 110,000 operations per second. Counts with conditions showed a table traversal speed of 1 million - 2 million rows per second. This traversal speed is the main speed indicator for aggregate queries, both simple and complex.
| OPERATION | ROWS | TIME (ms) | ROWS / s |
| insert | 1,024,000 | 10,813 | 94,692 |
| count (index on id) | 1,024,000 | 671 | 1,523,809 |
| select random id | 1,024,000 | 5,063 | 202,211 |
| select random zip (zip table) | 1,024,000 | 3,516 | 291,157 |
| update with random id | 1,024,000 | 19,078 | 53,671 |
| count (index on id) | 1,024,000 | 593 | 1,723,905 |
| delete with random id | 128,000 | 1,141 | 112,084 |
| count (index on id) | 896,000 | 500 | 2,043,912 |
| total test time | 42,610 |
Memory Engine with Data Persistence
In the persistent, all-memory configuration, all data still resides in memory while any changes are written to the disk. SELECT query speed is broadly the same, but INSERT, UPDATE and DELETE queries are slower due to the time spent writing the changes to disk.
In the test below, the time taken to shutdown and reopen the database are included. Opening the database is very fast, at the rate of about one second per 80,000 rows. Shutdown takes about a second per 100,000 rows. The size of the data on disk is over 105MB, which translates into 8-10 MB per second for file open/close operations.
| OPERATION | ROWS | TIME (ms) | ROWS / s |
| insert | 1,024,000 | 33,641 | 30,438 |
| shutdown | 10,391 | ||
| reopen | 13,218 | ||
| count (index on id) | 1,024,000 | 735 | 1,391,304 |
| select random id | 1,024,000 | 5,109 | 200,391 |
| select random zip (zip table) | 1,024,000 | 3,594 | 284,840 |
| update with random id | 1,024,000 | 54,859 | 18,665 |
| count (index on id) | 1,024,000 | 610 | 1,675,941 |
| delete with random id | 128,000 | 3,171 | 40,353 |
| count (index on id) | 896,000 | 610 | 1,675,941 |
| shutdown | 7,516 | ||
| total test time | 133,547 |
Disk Engine
The Disk engine configuration of HyperXtremeSQL is similar to traditional database engines where data is constantly exchanged between memory and disk storage. This configuration allows access to large data stores with limited amounts of memory.
What sets HXSQL apart is its ability to use the Disk Engine configuration selectively for individual tables. Super-fast memory access to smaller tables can be maintained while larger tables are accessed from disk.
In the first test below, 32MB is allocated to the JVM, 1 million and 4,000 rows are inserted into the large and small table respectively. Re-opening the database is instant, as the data for the large table is not read into memory. SELECT query speed on the smaller table (zip table) is the same as the previous test because the Memory Engine configuration is used for this table. Counts with conditions on an indexed column perform at near memory speed because whole rows are not read. Other operations on the large table are slower than their Memory Engine equivalents due to disk access.
In the second test, exactly the same operations are performed but only 8MB is allocated to the JVM and a smaller buffer size is used, resulting in slower SELECT, UPDATE and DELETE operations but without negative effect on the rest of the tests. In both tests, operating system file caching helps with the speed of queries to varying degrees.
| OPERATION | ROWS | TIME (ms) | ROWS / s |
| insert | 1,024,000 | 44,500 | 23,010 |
| shutdown | 6,375 | ||
| reopen | 46 | ||
| count (index on id) | 1,024,000 | 3,875 | 264,189 |
| select random id | 1,024,000 | 46,329 | 22,102 |
| select random zip (zip table) | 1,024,000 | 3,593 | 284,919 |
| update with random id | 1,024,000 | 133,922 | 7,646 |
| count (index on id) | 1,024,000 | 2,000 | 511,744 |
| delete with random id | 128,000 | 11,063 | 11,569 |
| count (index on id) | 896,000 | 1,735 | 589,861 |
| shutdown | 8,109 |
| OPERATION | ROWS | TIME (ms) | ROWS / s |
| insert | 1,024,000 | 44,985 | 22,762 |
| shutdown | 6,219 | ||
| reopen | 46 | ||
| count (index on id) | 1,024,000 | 2,313 | 442,523 |
| select random id | 1,024,000 | 78,703 | 13,010 |
| select random zip (zip table) | 1,024,000 | 3,688 | 277,582 |
| update with random id | 1,024,000 | 235,953 | 4,339 |
| count (index on id) | 1,024,000 | 2,140 | 478,281 |
| delete with random id | 128,000 | 23,094 | 5,542 |
| count (index on id) | 896,000 | 1,891 | 541,226 |
| shutdown | 8,578 |
Summary
HyperXtremeSQL can be configured to use different amounts of memory and storage resources. Smaller tables can reside totally in memory for maximum query speed, while the memory used for larger tables can be adjusted to as little as 8MB of JVM space.
In the above tests, all SELECT and UPDATE tests consist of 1Million separate operations with auto commit, using a prepared statement but each 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.
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 the product was used.