Sergio Navarro

Blog

The WP7 Databases Cup: Siaqodb vs SQLCE. Part 1: Inserts (English)

Posted on February 23, 2012 at 1:10 PM

Hello another time!

The day of writing this post has arrived at last! ... Today the post is about a little benchmark which compares two of the most popular WP7 databases.

On the one hand, at the end of last year, Josue Yeray started an interesting series of posts attempting to find the most efficient way of doing a massive insert operation on SQLCE for WP7. In fact those series of post were a continuation of a funny challenge between two friends that competed using SQL Server and MongoDB for finding which of these two databases were faster inserting data. The challenge was quite simple they simply tried to insert a number of records in a table in less time than the other. Josue found out as he expected that desktop databases vs mobile databases comparison was not a fair competition :D. Eventhough it was a funny pretext to make a first public benchmark test to SQLCE for WP7.

http://www.microsofttranslator.com/bv.aspx?from=&to=en&a=http%3A%2F%2Fgeeks.ms%2Fblogs%2Fjyeray%2Farchive%2F2011%2F12%2F21%2Fwindows-phone-7-5-inserciones-masivas-en-sql-server-ce-vs-sql-server-vs-mongodb.aspx

http://www.microsofttranslator.com/bv.aspx?from=&to=en&a=http://geeks.ms/blogs/jyeray/archive/2011/12/21/windows-phone-7-5-inserciones-masivas-en-sql-server-ce-vs-sql-server-vs-mongodb-y-ii.aspx

On the other hand, months before Josue Yeray posted his series of posts I had to take a decision regarding which database to use in a project: Siaqodb or SQLCE. I selected Siaqodb for the project. So after reading his post I felt it would be worth for the community that I answer his "challenge" in order to have a comparison with other WP7 database.

For me the main reasons to select this object oriented database were the following ones:

  1. The time needed for the unit tests of a little application using Siaqodb was a half of the time needed by this application when it used SQLCE.
  2. Siaqodb is a true cross-platform database (Silverlight, WPF, WindowsForms, WP7, WindowsMobile, Android and iOS).
  3. Siaqodb has an Include method like the one available on Entity Framework. It allows you to set which of the nested objects of a class you want to load for a LINQ query executed by the database.
    SQLCE doesn't have this method available. You only can predefine which nested objects will be loaded each time you load its parent object. The problem is it cannot be changed. So no matter in which circumstances you load an object, you always will have to load the predefined nested objects you set on dbcontext configuration.



After theses words showing my clear preference for Siaqodb, I also reckon that between my reasons there is a lack of an objective benchmark test that shows which of the two databases has better performance for the different typical operations performed by a database. At least one benchmark more reliable than the data layer unit tests I performed with my little application.

The article has been divided in a series of four posts: Inserts, Inserts with Nested Objects, Queries and Updates. So this is the first post of the series.

The tests are based on the Missile 1 example used by Josue Yeray on its tests. In order to make a fair comparison I have added to this example some of the improvements he uses for his Missile 2 example. Concretely I removed the IsVersion annotation and I used the InserAllOnSubmit method in order to obtain the best performance when insert operations are performed using SQLCE.

However I have not added other improvements proposed on Missile 2 like the use of threads. The reason is that I am looking for meaningful differences between the two databases. So despite I consider that this other improvements would improve performance for both databases I hope you will agree it should not return meaningful result for comparing the two databases.

The source code I have used can be downloaded from the link at the bottom of this post. Results has been obtained using the WP7 emulator. Values showed on graphs and tables are measured in minutes.

The tests carried out can be divided in two axes. For the first axe I focus on how many flushes (or commits) are needed for inserting data on the database :

  • 1 Flush: The flush is performed only one time for inserting all the objects in the database. It is the most efficient strategy and it can be used on scenarios where you have available all the data you are going to insert into a table of the database.
  • N Flushes: It uses one flush operation per object to add. This strategy is less efficient than previous one. However it is the one we will use when our scenario forces us to gradually add data to our database along all the life cycle of the application.



The second axe for my tests is related to the constraints which have to fulfil the class to be saved on the database. For example while SQLCE forces us to set a primary key for our object between its properties. Siaqodb does not forces us to set a primary key but forces us to declare an OID property that will be used by Siaqodb as a kind of autonumeric primary key. So, with this constraints in mind and using as a base the test class used in the "challenge" by Josue Yeray I defined the next two test groups for this axe:

  • SQLCE Conditions: The test class used for inserting data in the database has to contain a primary key using a Guid property. It doesn't matter if besides it has an autonumeric property OID.
  • Siaqodb Conditions: The test class used has to have an autonumeric primary key. The Guid property is a simple field which has neither an index nor an unique constraint.

Regarding the way to decide the winner, I think it could be funny to present it as a soccer knockout of
a championship. On each knockout the two teams in the competition play two matches: One as local and the other as visitor. :P

First match: SQLCE (local) vs Siaqodb (visitor)

First Half: 1 Flush

The knockout starts on the Microsoft field. Both teams start the match ready to win and supporters of both teams sign their chants as aloud that we cannot hear anything else. :)

The SQLCE runs are the following:

  • SQLCE (1 Flush) Index + Unique + Guid : It uses the autonumeric property OID as primary key. The Guid property uses an index an has an unique constrain set.
  • SQLCE (1 Flush) Index + Unique + Guid + Not Autonumeric : It uses the Guid property as primary key. Moreover, it does not use any OID property just because it is not a must for SQLCE.

The Siaqodb runs are the following:

  • SIAQODB (1 Flush) Index + Unique + Guid : It uses an indexed Guid field which also has an unique constraint. Furthermore it contains the autonumeric OID field that is a must for Siaqodb.
  • SIAQODB (1 Flush Massive Insert) Index + Unique + Guid : It uses the same configuration than previous one but instead of using the standard method for store objects, it uses the Siaqodb specific one for massive inserts.







Looking at the results it is not easy to decide which database has scored the first goal. In absolute terms and by a slightly difference Siaqodb obtained the best time for 500.000 objects inserted in the database. However if we take a look to the result for 100.000 elements we see the results are just the opposite.

So in consequence I consider the result at the end of the half-time is 0-0 tie. Just because anyone of the two contenders has been able to impose to the other. Players walk back to the dressing room ...


Second Half: N Flushes

In the Microsoft field there is a tension feeling before the start of the second part. They did not expect that Siaqodb were a fearsome rival. It is a newcomer and no-one thought they will put so much pressure to one of the leaders of the competition.

It is important to stand out that the database which will rule this second half will be the one which will show better behaviour for the insertions performed along the life-cycle of an application. I am talking about applications that perform a big number of insertions with a low number of objects per insertion.

Let's see the changes on the line-up for each team ...

SQLCE runs:

  • SQLCE (N Flush) Index + Unique + Guid : It uses the autonumeric OID property as primary key. Furthermore it uses the Guid field with an index and with an unique constraint.
  • SQLCE (N Flush) Index + Unique + Guid + No Autonumeric : It uses the Guid property as primary key. However, it does not use the OID property because it is not a must for SQLCE.

Siaqodb run:

  • SIAQODB (N Flush) Index + Unique + Guid : It uses the Guid property with an index and with an unique constraint. Moreover, it uses an autonumeric property OID which is mandatory for Siaqodb.





Taking a look to the results we can see how both databases have difficulties for inserting 100,000 objects or more to the same table when the insertions are performed using one flush per insert. So it is important to take into account how expensive can be to add new objects to a table that contains a high number of elements before our adding operation.

Both databases has similar behaviour, eventhough Siaqodb is a clear winner because while it only needed 1 minute for inserting 10,000 objects, SQLCE needed half hour in order to make the same operation.

So despite anyone of the two teams has showed a great style at this second part. Siaqodb has made the goal that gives them the victory of this match as visitor on the Microsoft field.

The parity showed on this first match makes us think in a passionate second match of the knockout.



Second Match: Siaqodb (Local) vs SQLCE (Visitor)

First Half: 1 Flush

The second match starts on the Siaqodb field. This a more modest stadium that the Microsoft one. However Siaqodb team overcomes it with the enthusiasm of each one of its members. These are the lines-up.

Siaqodb runs:

  • SIAQODB (1 Flush) Index + Autonumeric Key : It uses the mandatory autonumeric OID. Moreover the Guid property is used without neither index nor unique constraint.
  • SIAQODB (1 Flush Massive Insert) Index + Autonumeric Key : It uses the same configuration than the previous one run. However instead of using the standard insert method it uses a specific method for massive inserts provided by Siaqodb.

SQLCE run:

  • SQLCE (1 Flush) Index + Autonumeric Key : It uses an autonumeric OID as primary key. Furthermore the Guid property is used without neither index nor unique constraint.





Looking at the results we can see that when Siaqodb plays on its field (with its conditions) it achieves better results than SQLCE. In fact, its advantage is bigger when the amount of data inserted increases.

So, at the end of half-time result is 1-0. The newcomer has taken a little advantage in the match and a big advantage in the global score of the knockout.

Second Half: N Flushes

Siaqodb stadium is a party at the begining of the second half. Will at least SQLCE able of coming from behind to tie the game?

Siaqodb run:

  • SIAQODB (N Flush) Autonumeric Key: It uses the mandatory autonumeric OID. Moreover the Guid property is used without neither index nor unique constraint.

SQLCE run:

  • SQLCE (N Flush) Autonumeric Key : It uses an autonumeric OID as primary key. Furthermore the Guid property is used without neither index nor unique constraint.





Another time as in second half of the first match Siaqodb shows better performance than SQLCE for inserting bigs amounts of data gradually on the database . Also for these runs both databases have serious problems for inserting data on tables with 100,000 objects.

The match finish with 2-0. Siaqodb is the winner of this match

Knockout Result: Conclusion



The knockout score is clear: 3-0. There is no doubt about the winner. Maybe we could think that this big difference on the score of the match shows an exaggerated difference between this two databases when in fact the differences in general did not were so big. However, if we take into account the fact that Siaqodb is a crossplatform database which can run on Android or IPhone then we can understand the final result is fair. So Siaqodb is a clear winner of this knockout.

On next posts we will see what happen when I evaluate other types of operations with these two databases ...

Source Code: http://jersiovic.webs.com/SiaqodbvsSQLCE/wp75_MissileOne_WithSergioNavarroChanges.zip

Nota: In order to run the project you will need to add a reference to the Siaqodb dll beta file for Mango. Moreover you will need to set a 30 days license code in the source code. Both can be obtained at the Downloads section of Siaqodb webpage http://siaqodb.com .

Categories: None

Post a Comment

Oops!

Oops, you forgot something.

Oops!

The words you entered did not match the given text. Please try again.

Already a member? Sign In

0 Comments

Twitter