Silverlight driven blogging

Windows Phone (Mango) DB Engines Performance Testing

Max Paulousky — Wed, 27 Jul 2011 01:24:32 GMT

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

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:

Searching by substring by indexed field
Search by key with data loading by foreign key
Search by primary key
Search by not indexed column
Load the whole table
Ordering by indexed column
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

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.

Tagged: Windows Phone 7, Mango, SterlingDB, SQL CE, Performance, Optimization

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

The Second meet-up of Minsk Silverlight/Windows Phone User Group

Max Paulousky — Fri, 08 Jul 2011 17:20:03 GMT

On Saturday, July 2nd, we had the second meet-up of Minsk’s Silverlight / Windows Phone user group. That time about 55 IT specialists registered on the event. The number of participants increased for two times against the first meet-up

That time we had planned three sessions and understood that it was the optimal number of activities.

The first session was dedicated for XNA. Ivan Suhinin presented ‘XNA and physical engines’ where he showed demo XNA application, described workflow and specifics of working with physical engines. Demo application can be downloaded here.

The second session was extremely excited because Vladimir Veevnik demonstrated Kinect™ and its features. He described hardware and software parts of Kinect, showed some source code etc. Also, he presented a real time demo where USB-rabbit was repeating the same motion, as Vladimir did. All Vladimir’s motions were controlled by Kinect and then a special application sent commands to USB-rabbit. Source code of demo application can be downloaded here.

For the desert I, Ivan Suhinin and all attendees discussed the future of Windows 8 and it’s impact on development. We discussed what Jupiter, DirectUI are etc

The video of the event is below. The first session starts at 05:00, the second starts at 53:03, Kinect Demo starts at 120:05 and the last session starts at 142:10. Also, I will provide more photos later.

The event was really great! We had chance to talk to each other, discuss actual question and meet new friends and followers.

Also, I would like to say thank you to all presenters for their hard work and spent time.

If you have any questions – let me know!

Tagged: User Group, Windows Phone, Silverlight, XNA, Windows 8

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

Software Development Life Cycle for Windows Phone and Blackberry/Pyxis Platforms Session

Max Paulousky — Mon, 04 Jul 2011 19:23:35 GMT

About a month ago I presented a session on development specifics for

Windows Phone and Blackberry/Pyxis platforms.

This presentation was a generalization of my mobile experience with Windows Phone and Blackberry/Pyxis platforms.

You can download presentation with detailed notes here. Also, you can watch video, that was prepared for the session, and where I compare implementation of a screen for displaying list of news for both platforms.

I hope, it will help you!

Tagged: Windows Phone 7, Blackberry, Pyxis, SDLC, Conference, Video

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

The First Public Meet-up Of Minsk Silverlight/Windows Phone Group Has Happened!

Max Paulousky — Sun, 29 May 2011 21:46:11 GMT

I am proud to inform my readers that the first public meet-up of Minsk Silverlight/Windows Phone group has happened. I and my friends made great efforts to prepare that meet-up, prepare sessions, answered big number of questions.

We planed five sessions but were able to perform just four. One session (3D in Silverlight5) was postponed till the next meet-up because of problems with beta version of Silverlight 5 runtime. To be honest, we were lucky that that session was canceled. Five sessions would take a lot of time and attendees would be exhausted. All other sessions were hosted on time and were really great!

Ivan Suhinin, presented XNA Content Pipeline session:

Ivan Kirkorau presented his Testing for Windows phone Marketplace session.

For dessert Alex Sorokoletov presented his session MIX 11 - an eyewitness account.

Also, we had a lot of behind-the-scenes talks with some tea, coffee, cookies and fun.

Hope, soon I will be able to publish photos of my session.

I would like to say thanks to Ciklum Minsk for a hall and catering, to it-job.by as a video partner and dev.by as an information partner.

I got some feedback on twitter about the event and it was positive – people like it. That means, we will have more meet-ups and will present more sessions about Silverlight, Windows Phone and XNA

Thank you everyone and see you next time!

Tagged: User Group, Windows Phone, Silverlight, XNA, MIX

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

Microsoft has updated Windows Phone articles on MSDN with 7.1 mango run-time update

Max Paulousky — Tue, 24 May 2011 08:12:23 GMT

Today I mentioned on Twitter that MSDN has published updates for Windows Phone documentation.

It seems, the official name of a new Windows Phone version is Windows Phone 7.1

There are some useful links to new articles

Also, today should happen a Mango event where Microsoft will talk about the future of Windows Phone. Probably they will officially announce Mango update and some new Windows Phone devices.

Stay tuned!

Tagged: Silverlight, Windows Phone

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