Mobile DB Performance Testing: with Hardware

NOTE: this is the same post from a week ago, but now I added in performance times from actual devices.

1. Palm Treo 750 (300 Mhz)

2. Palm Treo Pro (600 Mhz)

3. HP iPAQ hw6945 (416 Mhz) – oldest device

First change for the previous listings: cpu is not everything.  The iPAQ has a faster cpu than the  Treo 750, but is slower on most tests (could be memory, or Windows Mobile 5 vs Windows Mobile 6).

A coworker and me were working on some database performance testing for mobile platforms recently.  Our goal was to get some metrics between SQL CE, SQLite, and UltraLite (a Sybase  product).  Actually, that was my goal, my coworker also wanted to test out Perst, which is an OO database – instead of a relational database.

Now a disclaimer: these tests are not authoritative.  This is not a test of everything you could ever want to test on a mobile database.  In fact, the tests are quite simple.  Also, we are not taking money from any group, so the idea is there is not bias either.  But if anyone would like more authoritative tests – send money.

Finally, performance is not the final statistic for any mobile project, so don’t base your decisions strictly on this.  Notably, how to synchronize the database and memory usage are both huge topics.

On running the test: the metrics I’m displaying are from a WinCE 6.0 emulator, running .net cf 3.5.  But not an actual physical device.  Times from a physical device also vary as there are wide differences between device hardware.  On one device I used, the times were 2-3 times faster on the physical device than the emulator.  But the other important info here is .net cf 3.5.  Microsoft reports a 30% performance increase in moving between .net cf 2.0 and .net cf 3.5.

Finally, all times are reported in milliseconds.  So 1000 is 1 second.

Create Database

This is the first performance category – and frankly, the one I care the least about.  In the code I write, you create the database once, and work with it from there.  But just in case this does matter to you, here it is.

The tests consists of creating a brand new database, along with a few tables.  Here is the SQL that was executed for SQL CE (and variations on this were used for each of the other databases):

   1: CREATE TABLE T1 (i bigint not null PRIMARY KEY, s nvarchar(50))

   2: CREATE UNIQUE INDEX s_idx ON T1 (s)

   3:

   4: CREATE TABLE Product (id bigint not null PRIMARY KEY, Name nvarchar(50))

   5: CREATE TABLE Customer (id bigint not null PRIMARY KEY, Name nvarchar(50))

   6: CREATE TABLE ProductOrder (id bigint not null PRIMARY KEY, ProductId bigint, CustomerId bigint, Price decimal, CreatedDate DateTime)

   7: CREATE INDEX poc_idx ON ProductOrder (CustomerId)

   8: CREATE INDEX pop_idx ON ProductOrder (ProductId)

None of them really stand out here.  The difference between 3.5 seconds and 4.7 seconds is negligible to me.

Insert

Now this one I care about.  Here we are attempting a bulk insert of 2000 records into the T1 table (see above).  This is a key task if you plan on synchronizing the database with a central server. If you want to see the general code for this, below is the SqlCe code.

   1: using (DbConnection conn = SqlCeCommon.CreateConnetion())

   2: {

   3:     conn.Open();

   4:

   5:     using (var txn = conn.BeginTransaction())

   6:     {

   7:         using (DbCommand cmd = conn.CreateCommand())

   8:         {

   9:             cmd.CommandText = "INSERT INTO T1 (i, s) VALUES(@i, @s)";

  10:             var p1 = cmd.CreateParameter();

  11:             var p2 = cmd.CreateParameter();

  12:             p1.ParameterName = "i";

  13:             p2.ParameterName = "s";

  14:             p1.DbType = DbType.Int64;

  15:             p2.DbType = DbType.String;

  16:             p2.Size = 50;

  17:             cmd.Parameters.Add(p1);

  18:             cmd.Parameters.Add(p2);

  19:             cmd.Prepare();

  20:             long key = 1999;

  21:             for (long i = 0; i < nRecords; i++)

  22:             {

  23:                 key = (3141592621L*key + 2718281829L)%1000000007L;

  24:                 p1.Value = key;

  25:                 p2.Value = System.Convert.ToString(key);

  26:                 cmd.ExecuteNonQuery();

  27:             }

  28:         }

  29:         txn.Commit();

  30:     }

  31: }

First off, SQLite wins this one hands down, nudging out UltraLite and handily beating Perst.  As for SQLCE, I don’t know what happened there.

Update

This time, take the text column from the T1 table and update it based on the index.  Again, we are executing these updates one at a time (2000 of them), not in bulk.

Perst blows the competition away in this test, with SQLite not far behind.  But I would say that SQLCE also gave a respectable showing in this test.  Also, and perhaps more interestingly, the performance times between the emulator and the physical devices are not that different – and in the case of SQLCE, the physical device is SLOWER!

Search Tests

The next set of test are to perform a search based on Primary Key and by a text column (indexed).  We are searching for all 2000 records that were inserted (from the insert test), but one record at a time.

Primary Key Column

Indexed Text Column

Here you can really see Perst come out into the lead.  But it is the SQLCE times that are rather disheartening.  Problem is they used to be WORSE!  The SQLCE Text is the one test were I did some real performance tuning.  The biggest difference maker was being VERY specific about parameter types and sizes.  (Note: once I did that for SQLCE, I also did the same for the other providers).  Considering where SQLCE came out in this test, I give SQLite and UltraLite a pass – but I was surprised at the (lack of) performance of SQLite in this case.

Also, since both columns were indexed, it is nice to see there was not a noticeable difference between selecting on the primary key verses on an indexed text column.

Iterate

This is the most straight forward of the tests.  Make a query that grabs all of the records out of a table, and get the data out of them.  Nice simple loop, no filters.

Here again the differences are negligible between the different databases.  UltaLite and Perst are the slowest, SQLite is the fastest with SQLCE close behind.  But all are between 1 and 2 seconds so who cares.

Delete

Again, a simple test.  Delete everything in the table – one record at a time.

Some might be surprised by the Perst numbers here.  But after talking it over, this is somewhat expected if you look at the INSERT times.  The INSERT nearly matches the DELETE.  So if it takes a long to to allocate space, it takes a long to remove it as well.  So no supprise that SQLite runs away with this one.

Join

OK, this is actually one of the more significant tests.  There is an issue with joins on mobile database – they stink.  And the more you add, the worse they get.  Side issue is you get a VERY limited number of index types (do you want ascending or descending).

Anyway, the test is simple.  If you look at the Create test you will see the tables we are dealing with.  I add  100 rows into the Product table and 50 records into the Customer table, then 5000 records (each customer has order one of each product) into the order table.

Then the test (and what is timed) is to execute the following query and extract the results.

   1: SELECT c.Name as Customer, p.Name as Product, o.Price

   2: FROM ProductOrder o

   3: LEFT OUTER JOIN Product p on p.ID = o.ProductId

   4: LEFT OUTER JOIN Customer c on c.ID = o.CustomerID

OK, if you are looking at the results, you see a cop-out.  I didn’t get the Perst code written.  Hopefully this will shame my coworker into getting this done.  🙂 (hi Priyesh).  But you have the other three results.

Here you see again, SQLite as run into the lead.  I would also say that UltraLite and SQLCE give a very respectable showing.

Database size

These are mobile devices, space is very limited.  You laptop toting developer all want to talk about gigabytes,  I’m happy to be talking about megabytes in tens – not even hundreds.  So final database size is important.

* Incomplete result

Because I did not complete the Perst tests, take that number with a grain of salt.  All of the other are reasonably accurate though.  Amazingly, SQLite came out ahead again.

Conclusion

Again, these are not conclusive tests.  Mileage may vary, other factors may apply in choosing one over the other.  But, if you don’t have a synchronizing application, or you are willing to handle that yourself, then SQLite is an excellent option.  If you are willing to go non-conventional for even more speed, then Perst looks to have some good advantages as well.

SQLite also is not issue free.  One problem I encountered from time to time was a “Database locked” error.  I usually got this when executing large numbers of transactions.  As luck would have it, I got a new Ado.Net Provider (065) and the problem went away.

I will also throw out some reasons for Ultalite while I’m at it.  UltaLite has an excellent sync engine to go with it, and also includes device management software.  A downside it that UltraLite is the one database you can not deploy for free.  (Hopefully, in the near future we will also have some tests for Sybase SQLAnywhere as well, it is a more powerful database than UltraLite, but is also non-free).

Some defense for SQLCE.  SQLCE actually has some very good integration with SQL Server, including some of the new column date/time types from SQL Server 2008, as well as a serviceable synchronization engine in Microsoft Sync Services.  Unfortunately, I can’t whole heartedly get behind it – even the Sync Framework for Devices is still in CTP.  The main reason to go with SQLCE is because the SQL used in SQLCE is closest to what is used in SQL Server (or you have a client that insists).  Either way, there is technically wrong with SQL CE.

As for Perst, it is just too knew for me yet.  I’ll have to do a post on how you interact with the api, for instance, as it is not an Ado.Net provider.  But the performance for most tests was very good.  I just have to play with the API a bit more.  Also, the most glowing thing I can say about Perst, the Perst team has been EXTREEMLY responsive to requests.