How Raima Database Manager beats SQLite
4 top challenges with SQLite and how Raima Database Manager avoids them
Challenge 1: Scalable Performance
SQLite faces performance issues as more users and/or additional hardware is added to the system due to the "one write at a time" and single-thread design.
RDM has no limits
RDM does not have this issue as its performance gets better as you add better hardware and users. RDM has been optimized to scale and utilize any additional hardware added to the RDM system. Additional users also have a very stable and consistent set of performance. SQLite, by design, gives priority to a random user. This makes the performance of some users much better than others.
Challenge 3: Flash Media Lifetime
SQLite is not optimized for flash media storage.
Optimized for flash media
RDM optimizes and minimizes the number of writes to the storage medium, prolonging the lifetime of the device. This means less maintenance is needed and fewer replacements over the lifetime of the application. Additionally, RDM performs better on these devices due to the optimizations it has.
Challenge 2: Platform Support
SQLite does not support as many platforms as RDM does.
RDM supports nearly every hardware combination and operating system. It has been optimized to run in any environment. RDM can even run without an OS in a bare-bones configuration.
Challenge 4: Development Environment Integration
SQL is not packaged with built in project files and support for all the standard development environments.
Out of the box
RDM supports Visual Studio, XCode, Makefiles, CMake, Wind River Workbench, Green Hills MULTI, etc with prepackaged project files and development environment integrations.
Switch from SQLite to RDM for scalability and performance
Raima Database Manager supports nearly the same level of SQL that SQLite does. Thus, the user would just need to export the contents of their database to a CSV, XML or SQL format, run rdm-create on their database schema file, then run rdm-import on the CSV, XML or SQL format file and the user will have an RDM equivalent to the SQLite database. From there, the user may have to port your application, but RDM supports the ODBC SQL interface, JDBC Java interface and the ADO.NET C# interface so there may not have to be substantial code changes. See migration guide.
Performance Comparison Example - Raima Database Manager (RDM) vs. SQLite
The majority of today’s operating system and hardware support multithreading. SQLite does not take advantage of this opportunity because of the design in SQLite. When there are many concurrent writes to a SQLite database, application users experience a significant reduction in speed and the application may not meet the users performance expectations. It is well known that the write access to the SQLite database can only be granted if no other requests are being serviced. It is the "one write at a time" design within SQLite that slows down the throughput. Thus, many application owners are forced to look for alternative embedded database options to resolve their performance bottleneck.
Raima has put together a test that proves the differences between the RDM database solution and SQLite. In this test, we demonstrate that RDM is a good alternative to SQLite.
How we did the test
In this performance comparison, we used a standard TPC-B test framework from www.tpc.org. The TPC-B measures throughput in terms of how many transactions per second the system can perform. The test has been modified to allow for comparisons where multiple clients are doing parallel work.
We used the same test environment and framework for both SQLite and RDM. In the test, RDM proved to be significantly faster than SQLite. See illustration below. The platform used for the test was RDM release 14.1 running against SQLite v3.23 on a standard Windows 10, Intel i7 processor machine with 16GB's of RAM and standard SATA hard drive.
Raima Database Manager has a design that is optimized for embedded systems with a modern and efficient set of API's, along with a well-designed database storage file format. RDM allows for concurrent writes and reads and therefore can do many more times the amount of work that SQLite can do.
Because of RDM´s multithread support, RDM will execute a substantial higher amount of transactions when compared with SQLite.
In the diagram below RDM push over four times the number of transactions that SQLite can within the same time frame and hardware constraints. If this test is done using an in-memory design, the difference is even more noticeable.
Breaking down the test above further shows the number of transactions done per second on each client of the database.
As proven in the illustration above, due to SQLite's design, it has a variable number of transactions it can do per second on each client of the database. RDM delivers up to four times increase in the number of transactions per second per client. If stability and consistency are important, RDM is a great fit.
RDM is designed to deliver consistent and dependable transactions per second while being multi-user friendly and efficient.
For more performance testing benchmarks click here
Ready to get started?
How to migrate from SQLite to RDM for scalability and performance
How to migrate to RDM in order to try out the benefits? The answer is more straightforward than you may expect. RDM supports nearly the same level of SQL that SQLite does. Thus, the user would just need to export the contents of their database to a CSV, XML or SQL format, run rdm-create on their database schema file, then run rdm-import on the CSV, XML or SQL format file and the user will have an RDM equivalent to the SQLite database. From there, the user may have to port your application, but RDM supports the ODBC SQL interface, JDBC Java interface and the ADO.NET C# interface so there may not have to be substantial code changes.
|Functions||SQLite Syntax/Logic||Raima Syntax/Logic||Notes|
|Initialization||int sqlite3_config(int, ...) - used to make global configuration changes to SQLite in order to tune SQLite to the specific needs of the application.||SQLSetConnectAttr() and use our driver-defined attributes to set connection-based configuration options.||SQLite List of config options|
Raima List of options
|Open DB connection||sqlite3_open("test.db", &db);||SQLAllocHandle(), followed by SQLConnect(). Once a connection has been established, issue the "USE ||SQLite allows multiple database files with single connection via ATTACH DATABASE statement|
|IN-MEMORY databases||Use ":memory: " option ;|
|Set the SQL_ATTR_RDM_STORAGE_MEDIA connection attribute to "INMEMORY_KEEP" by calling SQLSetConnectAttr().||SQLite will not save in-memory database to Disk!|
Raima gives you the option of saving to Disk.
|Execute SQL||(i) First prepare the statement|
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded*/
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
(ii) Then Execute using
|(i) Execute with SQLExecDirect(SQLHSTMT StatementHandle, SQLCHAR * StatementText, SQLINTEGER TextLength).
(ii) Prepare with SQLPrepare(SQLHSTMT StatementHandle, SQLCHAR *StatementText, SQLINTEGER TextLength) and then execute with SQLExecute(SQLHSTMT StatementHandle).
|Depending on encoding, SQLite may use sqlite3_prepare16() for UTF-16 encoding and int sqlite3_prepare16_v3()|
|Binding Parameters||sqlite3_bind_* family of functions is used ; e.g. |
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
int sqlite3_bind_pointer(sqlite3_stmt*, int, void*, const char*,void(*)(void*));
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
Call SQLParamData() and SQLPutData() to process data-at-exec blob parameters specified in insert and update statements.
|Raima uses SQLBindCol() to bind an application variable to the column. |
In SQLite , to RESET a prepared statement to initial state use - sqlite3_reset(sqlite3_stmt *pStmt)
|Transactions||Uses BEGIN-TRANSACTION, DEFERRED/IMMEDIATE/EXCLUSIVE, END-TRANSACTION or COMMIT , SAVEPOINT and ROLLBACK commands||(i) Use "START TRANSACTION", "COMMIT," "ROLLBACK," "SAVEPOINT" and "ROLLBACK" commands.|
(ii) Call SQLEndTran() to commit or rollback."
|Fetch result set||int sqlite3_step(sqlite3_stmt*)||SQLFetch() or SQLFetchScroll() to fetch the next set of rows from the result set. To retrieve BLOB data in chunks, call SQLGetData().||Both DBs can return next-ROW or Error codes ( detailed below)|
|Access a column in a row||sqlite3_column family of functions||(i) Access the column values retrieved into application variables set with SQLBindCol() prior to fetching.|
(ii) Call SQLGetData() on the desired column(s).
|SQLite uses functions based on column type; double sqlite3_column_double(sqlite3_stmt*, int iCol) or int sqlite3_column_int(..) or int sqlite3_column_bytes(..)|
|Close Statement||sqlite3_finalize(sqlite3_stmt *pStmt) destroys a prepared statement and releases its resources||Call SQLFreeHandle() on the statement handle to release all its resources.|
|Close a Database Connection/Handle||int sqlite3_close(sqlite3*) and int sqlite3_close_v2(sqlite3*)||SQLFreeHandle() to free the connection and environment handles. This frees all the database handles associated with the connection.||In SQLite is better to use sqlite3_close_v2 as its intended for use with host languages that are garbage collected, and where the order in which destructors are called is arbitrary.|
|Configuration||The sqlite3_config() interface is used to make global configuration changes to SQLite in order to tune SQLite to the specific needs of the application.||Uses a configuration script to set environment variables.|
|Encryption||int sqlite3_key( sqlite3 *db, const void *pKey, int nKey);|
int sqlite3_rekey( sqlite *db,
const void *pKey, int nKey /* The new key */
const char *passcode,
) to create an encyprtion object.
Pass the encryption object to SQLSetConnectAttr() using the SQL_ATTR_RDM_ENCRYPT attribute.
|SQLite uses the SQLite Encryption Extension (SEE), which is paid. |
Raima uses the AES encryption cypher with support for several key sizes (128, 192, and 256 bits)
|Isolation||Supports "Serializable Isolation" , But in Write ahead WAL mode, it provides "Snapshot Isolation".||Supports "Serializable Isolation"|