Saturday, December 22, 2012

Embedded DB for .NET: SQL CE 4.0 vs SQLite

Benchmarking the Performance of Embedded DB for .NET: SQL CE 4.0 vs SQLite - CodeProject



WinRT (Windows 8 Store Apps) SDK does not include ANY database tool.
Maybe Microsoft wants to promote saving data to "cloud".
Problem is that devices are not always connected to cloud,
and speed of local storage is usually much better than online.

So people are using same SQLite open source db
that is included in both iOS and Android,
as well as Firefox and Chrome browsers.
And while SQLite is in fact quite fast,
SQL CE 4.0 embedded database is apparently faster.
SQLite can be "tuned" to be faster for insert/update,
by disabling auto-transaction on each record insert/update.
After this adjustment, insert speed then becomes faster than SQL CE
So, using SQLite makes sense for WinRT, same as for iOS and Android...
SQLite does not have db size limit (Max DB size is 4 GB with SQL CE)
Here are some results of my tests.
The test is simplistic, inserting one integer id and one random string (guid)
to a database table, and then reading same number of records in random order by id.
Test computer is 3.2+ GHz, Windows 8, with SATA3 SSD, .NET 4.5, C#, x86 (DLLs are 32-bit).
Total number of records is 1000000 (to get meaningful time)

Data StoreInsertSelectFile Size
SQLite 3.600:00:13.936797400:00:47.372360146 MB
SQLCE 4.000:01:07.223511100:02:36.730071363 MB
CSV (UTF8)00:00:01.461184400:00:01.4281937
00:00:01.9542602
44 MB
XML (XmlDocument)00:00:05.715725400:00:04.0695283
00:00:04.4985845
66 MB

With auto-transactions for individual inserts turned of, SQLite is significantly faster than SQL CE, both for inserts (5 times) and selects (3.5 times).
But "raw" text write and read (CSV file) is order of magnitude faster.
Read and write of XML is in the middle between just text and DB
. CSV and XML take some time to be loaded in memory Dictionary (first "select" time),
and with random read from memory takes just a little bit more time.
So, maybe Microsoft designers are right not to include "classic" db API to WinRT.
XML (and JSON) are much faster, and still flexible enough...
For typical apps that may be just what is needed...
@ StackOverflow and here
ADO.NET 2.0 Provider for SQLite
www.sqlite.org






No comments: