Mobile DB Performance Testing: with Hardware

Category:UncategorizedTag: , , :

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)
Emulator Treo 750 Treo Pro HP iPAQ
Perst 3500 952 861 925
SQLite 3031 1530 970 1730
UltraLite 3507 2210 1964 3198
SQLCE 4768 2428 2083 4047

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: }
Emulator Treo 750 Treo Pro HP iPAQ
Perst 5948 3890 2877 6454
SQLite 3012 2735 1845 2810
UltraLite 4235 3485 3157 5359
SQLCE 11413 9212 8586 12051

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.

Emulator Treo 750 Treo Pro HP iPAQ
Perst 1650 1386 1719 1741
SQLite 3000 2897 2696 3775
UltraLite 9047 6309 5815 6739
SQLCE 5851 6556 6222 8215

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

Emulator Treo 750 Treo Pro HP iPAQ
Perst 1809 1602 1507 2048
SQLite 9868 5926 5442 7980
UltraLite 5637 4143 3829 5541
SQLCE 17898 14330 15219 15586

Indexed Text Column

Emulator Treo 750 Treo Pro HP iPAQ
Perst 2435 2903 2243 3016
SQLite 9838 6015 5700 6470
UltraLite 5698 4552 3904 5290
SQLCE 16022 14435 15703 15929

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.

Emulator Treo 750 Treo Pro HP iPAQ
Perst 1946 1807 2225 2988
SQLite 1081 685 557 880
UltraLite 1945 1588 1146 1666
SQLCE 1233 965 688 1096

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.

Emulator Treo 750 Treo Pro HP iPAQ
Perst 6341 4905 4379 7038
SQLite 2481 2137 1520 2131
UltraLite 5488 3704 3580 4089
SQLCE 8807 8931 7816 11439

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.

Emulator Treo 750 Treo Pro HP iPAQ
Perst
SQLite 3948 3171 2355 3480
UltraLite 8780 6651 5747 6683
SQLCE 5618 6113 5365 7079

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.

Perst 408k *
SQLite 333k
UltraLite 480k
SQLCE 640k

* 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.

10 thoughts on “Mobile DB Performance Testing: with Hardware

  1. Did you run all the tests on a mobile device? I think that running on an actual device may provide different results. In particular, the relative performance of CPU vs ram vs Disk/flash may. I.e. when focusing on CPU, the emulator may be 3 times faster but when it comes disk IO it may be 2 times slower. (Arbitrary numbers). Different optimizations may yield different results here.

    Greets

  2. The difference between the emulator and the hardware can be substantial as we all know. What I’d be interested in is what ‘kind’ of differences are there? It’s not just about cpu or i/o speed because what we are effectively testing in the emulator is the quality of the emulation code.
    I don’t think that this invalidates the tests done here for which many thanks Chris. They provide an interesting insight into relative performance issues but as you raise in the article issues such as ‘sync’ and backend compatibility are often far more important. We’re moving to SQL Anywhere (along with the EntitySpaces persistence layer) for those reasons and if our first impressions are anything to go by it’s faster than anything we’ve used before.

    MM2

  3. Hi,

    Just wanted to give another option.
    VistaDB (this one closely matches SQLCE for compatibility, but has many other features not present in SQLCE).
    I’m not working for them. Also I have no real experience with it.
    Just thought you might want to check that one out too.

    Best regards
    Ike

  4. Hey everyone, seems there is interest in two things: more databases, and actual device times. I can add device times, and I’ll work on more databases. If that happens, expect this to get republished.

  5. Some comments about the Perst performance results from McObject (you can download the Perst embedded database http://www.mcobject.com/perst )

    First, thanks for the kind words about the support you received from us. Our commitment to excellent customer support is a great source of pride for us, so the positive feedback is greatly appreciated.

    The results are more or less what we would have expected – Perst shows good search performance and not-as-good performance of update requests and iteration. Lower performance of insert/update is caused by short transactions; i.e. a commit is performed after each update. Perst doesn’t like short transactions. If you group multiple changes into each update transaction (which probably more accurately reflects real-world transactions more often than not) then Perst’s performance should be better. For a quick and easy experiment, just try performing a commit after each N-th transaction, and experiment with the value of N.

    As to why SQLite and other databases perform better in insert/update, first of all some might implicitly be using delayed or asynchronous commit (changes are written to the database by a separate thread, so commit() actually doesn’t mean that results of this transaction cannot be lost in case of power failure). This is just speculation; someone would need to dig into the internals of the systems to determine if, in fact, that is the case. Unless you explicitly relax it, Perst enforces the ACID properties of transactions.

    Another reason can be the log-less nature of the Perst transaction mechanism. Perst has no log file; instead, shadow copies of objects
    are created. When the transaction is committed, all modified pages have to be flushed to the disk (the shadow pages become the pages of record). The total size of these pages can be larger than just the modified objects written to the transaction log (i.e. more bytes have to be written because any given page has >1 object on it). Further, random modified pages in Perst can be (and probably are) located in different parts of the disk. Which will require additional disk head positioning (which is the slowest operation – 10msec average disk access time for modern disks) and as a result – worse performance compared to a sequential write to append to a transaction log file.

    Concerning iteration speed – RDBMS table records are located together and so makes it possible to scan them very efficiently
    (disks perform sequential reads much, much faster than random reads). Remember, tables are organized as rows, so scanning down a table’s rows is very fast. Perst has to deal with much more complex object structure and is oriented to a different access pattern: it is intended that an index is used to locate an object by some key and then some cluster of related objects is accessed using references. So Perst makes an assumption that objects that were created together are also accessed together and it tries to allocate these objects sequentially on the disk (locality of reference). Consequently, objects of different classes are interspersed in the storage, so sequential search will be less efficient in this case since it will require a lot of disk head repositioning.

    I hope this helps put some perspective on your results. Feel free to contact us.

    regards,
    McObject Support

  6. I’ve made some comments about the Sybase results at http://iablog.sybase.com/tslee/2009/08/elegantcode-on-mobile-databases/.

    Some of what I say is similar to Steve Graves’ comment above – some of these operations will be I/O bound and as a result it really matters what kind of guarantees the different databases are offering when you commit changes, for example. It’s valuable to see comparisons, but it is important that they not just be comparisons of some of the default settings in the different products (cache size, for example).

  7. Hi,did you made the tests using the ported c# version of Sqlite? It should be interesting to make more tests with more databases 🙂

Comments are closed.