|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.
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:
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 :
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:
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.
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:
The Siaqodb runs are the following:
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 ...
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.
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.
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?
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
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 .