Still looking for a sponsor Max Paulousky is looking for a Silverlight/.Net job in the Commonwealth

Share to Facebook Tweet this! Share to MySpace Share to Google Share to Live   Share via AddThis

Windows Phone (Mango) DB Engines Performance Testing

Abstracts

I am working on a Windows Phone application that requires some database to store information. I know a couples of DB engines for Windows Phone (SQL CE, Sterling DB, Siaqodb, ) but I have not found any performance testing of such software. So, I decided to compare SQL CE and Sterling DB.

I am going to use set of test queries (insert/delete/select) to measure performance. I will perform three attempts and will calculate average value.

Database structure

 Databse structure for performance tests

 

The database structure is as simple as possible. Companies has orders and each order item relates to particular product and has number of products for each order item.

In the application I implement 1 to N relationship between companies and orders and  1 to 1 between orders and products tables.

The Name field in the Products table is indexed. The same situation with fields Name and FoundationYear in the Companies table.

Tests

First set of test is CreateDB/Insert ones.

In CreateDB test I create structure of database. Then I insert 100 records of Products (plain data) and 100 records of Companies (data with references). For each Company I insert 10 records of Orders.

The second set of tests is Delete data tests. I delete 10 records from Companies, delete the whole table and delete the database.

The third set of test is select tests. I perform following queries:

  1. Searching by substring by indexed field
  2. Search by key with data loading by foreign key
  3. Search by primary key
  4. Search by not indexed column
  5. Load the whole table
  6. Ordering by indexed column
  7. Paging imitation

For search operations I want to test real-life operations and their performance (search by indexes, non-indexed fields, ordering, paging etc).

Create DB / Insert queries

Sterling DB does not create any DB structure until we insert data. At the same time, SQL CE has appropriate command that creates a file and tables, indexes etc in it. Usually, it takes about 1-1.5 sec

Insert operations are weak point of Sterling DB. That DB performs these operations much more slower than SQL CE does. Operations with inserting referenced data are especially slow. It takes more than a minute to insert 100 companies and 10 orders for each company (1100 records). At the same time, SQL CE is fast on insert operations.

Delete queries

I tested three types of delete operations. First one is just deleting 10 records from a table. The second one should delete all records from the table. Last operation should delete the database itself. SQL CE does not have direct command to delete all items from a table, so, I did not test it.

Search queries

I perform great number of search queries against both databases.

  • Searching by substring by indexed field. I look for products that contain “at” string in the name
Products.Where(p => p.Name.Contains("at")).ToList();
  • Search by key with data loading by foreign key. I load companies that have FoundationYear > 1950. For SQL CE I specify options to load dependent items (Orders and Products). SterlingDB performs loading such dependent objects automatically.
Companies.Where(c => c.FoundationYear > 1950).ToList();

  • Search by primary key. I look for a product with Id = 50
Products.Single(p => p.Id == 50);
  • Search by not indexed column. I look for companies that had IPOs
Companies.Where(c => c.HadIpo).ToList();
  • Load the whole table without using indexes. I load products
Products.ToList();
  • Load data with ordering. I order companies by FoundationYear and take only the first one to avoid lack of performance.
Companies.OrderBy(c => c.FoundationYear).First();
  • Paging. I skip 50 companies and take 10
Companies.Skip(50).Take(10).ToList();

Results

I filled an excel spreadsheet with results of performance testing

Windows Phone (Mango) DB Engines Performance Testing Results

At the first glance, SQL CE wins more tests than Sterling DB. But if we go forward, I will explain in what cases Sterling DB is much better than any other DB. Let’s start!

All tests were performed on an emulator. the difference in performance between the emulator and HTC HD7 is not really big.

Backup DB. That operation is exclusive for Sterling users. It is important because Sterling DB’s structure is based on big number of files and folders that are created for each record, index; max 100 files per folder. As a result, archiving of lots of files takes time.

CreateDB. That operation is exclusive for SQL CE DB and takes about 1.5 seconds.

Insert 100 Items. That operation is faster on SQL CE. SterlingDB takes much more time (~36 times) to perform the operation.

Insert 100 items with dependent items. That operation for SterlingDB is even slower than simple inserting (~84 time). Below I will explain the situation with inserting and how it can be avoided.

Deleting 10 items. The speed of the operation is similar for both databases.

Deleting all items from table. There is no appropriate operation for SQL CE but it can be done by requesting all keys from the table and performing delete operation for each record.

Delete DB. That operation is much faster for SQL CE because it requires to delete just one file. At the same time, SterlingDB should delete all files with data (more than 1000) and it takes plenty of time.

Search by substring. That operation is performed faster by Sterling than SQL CE.

Search by key with loading dependent data. In that case SQL CE is much faster than Sterling. Also, SQL CE has one additional advantage – it allows specifying which objects exactly should be loaded with main entity. This test requires loading all dependent data and if I use lazy loading for products, it will allow saving additional seconds.

Search by primary key. Search by primary key is extremely fast on SterlingDB. At the same time, SQL CE spends a lot of time on that operation. This behaviour can be explained by huge overhead expenses on each request.

Search by non-indexed column. In that competition SterlingDB is much slower because it requires to load all records (load all files) of the table. I hope, there will be big performance improvement when Jeremy implement one-file strategy for SterlingDB.

Load the whole table. That query is performed for Products table that does not contain dependent records and column values can be got from index. So, SQL CE and Sterling DB has similar performance for that type of queries.

Search by indexed column with ordering. As I mentioned above, when SterlingDB performs queries by indexed fields, it works much faster. This type of query is exactly the same situation and it does not require to load all record values (SterlingDB lazy loading).

Search with paging. I do not know exactly how Skip and Take operations are implemented in SterlingDB but they are ~3 times slower than the same operations in SQL CE.

DB size. That parameter does not participate in competitions but I would like to mention it also. For set of records I mentioned above, size of SterlingDB backup is less than SQL CE DB size. I checked a situation with 10 times more records for each entity. In the last case size of DBs was comparatively equal (~ 800 kb).

Conclusion

I performed testing of SQL CE and Sterling DB engines. In most cases SQL CE was faster than SterlingDB. On the other hand, Jeremy Likness is doing great job and SterlingDB is faster for queries that does not required loading the whole record or all data can be got from indexed fields.

SterlingDB uses in-memory cache for all data that was requested. Because of that, all queries that are executed more than on time, will return data immediately. SQL CE performs repeating queries fast also, but not fast as SterlingDB.

Developers should pay attention to situation with inserting data into SterlingDB. This operation is extremely slow. If you store some predefined data in DB, you can fill it and supply with your application as resource.

Also, I need to mention that SQL CE has great ways to improve performance. You can use two DataContexts for different type of queries – update and read-only queries. It will improve performance of selects for read-only queries (ObjectTrackingEnabled parameter). Also, using of compiled queries can improve performance of queries that are executed with great frequency. Update operations can be improved by implementing INotifyPropertyChanging interface which allows reducing memory usage.

One more additional SQL CE benefit is ability to update DB schema. If you use SterlingDB, you have to create a new DB and copy updated data there.

SQL CE has maximal DB size which is 512 Mb. Sterling does not have such limitation but I don’t think your DB size will exceed more than 10 Mb

SQL CE and Sterling have similar approaches to describe DB structure and perform queries. The difference is Sterling does not require to specify columns attributes for classes that map to tables. On the other hand, SQL CE allows using shorter queries because does not require to specify using  indexes (it is detected automatically).

So, my conclusion is. You need to use SterlingDB if:

  • You don’t have a lot of data;
  • You use read-only data;
  • You do queries for the same data often;
  • Your data is plain and does not have referential integrity;
  • You do not use non-indexed fields in queries

You need to use SQL CE if:

  • You have lot of data;
  • You actively use referential integrity
  • You plan to update DB structure in the future
  • Users actively update DB data
  • You want to control DeleteRule for associated data (Cascade deleting, no action etc)

Hope, it helps!

Sources

I provide full sources of a test application. Also, I provide excel spreadsheet with testing results.

Sources requires Windows Phone 7.1/7.5 SDKs.

This work is licensed under a Creative Commons Attribution By license.

Leave a Comment [ RSS ]

  • re: Windows Phone (Mango) DB Engines Performance Testing

    Requesting Gravatar... Михаил says:

    Выложили на WP7Rocks:
    www.wp7rocks.com/.../...-7-sql-ce-sterling-db.html

  • re: Windows Phone (Mango) DB Engines Performance Testing

    Requesting Gravatar... erIKej says:

    Try to add these 2 lines before the //Selects in MainPage.xmal.cs and look at the numbers!

    SqlContext ctx = new SqlContext();
    ctx.Orders.Take(1).ToList();

    //Selects

  • re: Windows Phone (Mango) DB Engines Performance Testing

    Your SQL CE table classes you have are suboptimal. You should be implementing INotifyPropertyChanging and INotifyPropertyChanged to reduce the memory footprint and increase database perf on each entity object. (If these exists, the LINQ provider can use them for change management and goes through fewer code paths for inserts and selects). I'd love to see your examples with these optimizations added. See my book for more information on why this works if you're interested.

  • Wardrobe Designs

    Hi there,
    One of the best article l has come across on this valuable subject. I quite go along with your assumptions and will thirstily look forward to your future updates.

Comments have been closed on this topic.