Sample Series – Bench Marking Object Loading Application II

This is the next sample in the Sample Applications Series. The purpose of the Sample Series is to provide concise code solutions for specific programming tasks. This sample provides a brief description of the problem, the solution and full source code.

After last nights blog post Sample Series – Bench Marking Object Loading I received a good number of questions on a WPF Disciples Google Group Discussion Thread.  If you didn’t get a chance to read that blog post, please look at it now as I won’t be repeating the background information.

I thought about all the comments, suggestions and possible “one time” object initiation that various software technologies pay the first time they are used.

I really want to get to the bottom for the initial delay that me and others got when using LINQ to build object collections.  Additionally I wanted to see what the bench mark would look like in a typical application.

When I was researching the loading of business entity objects from a database I ran across an awesome article on Code Project entitled, Dynamic Method IL Generator written by Code Project author Herb Randson.  I used the code from his article and translated into VB.NET and made just a few very minor adjustments and added a LINQ test to the mix for comparison.

New Bench Mark Test

Let me introduce you to the players in this bench mark.

NEW SQL Server Express Database

Included in the download is a NEW BenchMarkData SQL Server Express 2005 Database.  If you downloaded and set up the database from the previous sample, please remove that database and attached the new database supplied with the source download below. 

To run the tests in the sample download, you will need to have either SQL Server 2005 or SQL Server 2005 Express installed on your computer.  Attach the included database to either server and edit the app.config file and changed the connection string to match your system.  This database is ultra simple with one table at has 19,972 rows that I imported from the AdventureWorks database Person.Contacts table. 

This new database has an additional field and stored procedures for all CRUD operations.

NEW Bench Mark Loading Objects Sample Application

This new application is a Windows forms application that runs all the tests and displays the results in a DataGridView.  There are four sets of tests.  Each set of tests has 5 tests, except for the LINQ set which has 6 tests.  All CRUD operations are performed using stored procedures and concurrency checks are made using the SQL Server timestamp column in each row.  Each test has been optimized and correctly handles DBNull coming from the database and correctly set Nullable(Of Type) properties.

The below list describes the four sets of tests. 

• Manual – method loads the business objects by iterating a SQLDataReader and manually constructing the classes with optimized code.  In a real world application, this would require that a method be written for each business entity to load it.
• Reflection – method loads the business objects by iterating a SQLDataReader and using a cached listing of the target objects properties.  In a real world application, this requires only this code be called and a List of business objects will be returned.  What is more realistic is that a method will be created that wraps this code as I have done in this application.
• Dynamic – method loads the business objects by iterating a SQLDataReader and using some of the most wicked code I’ve seen a long time.  In his Code Project article Dynamic Method IL Generator the author lays out how to generate IL code at runtime and then call that code.  This method results in insanely fast loading of business objects.  In a real world application, this requires only this code be called and a List of business objects will be returned.  What is more realistic is that a method will be created that wraps this code as I have done in this application.
• LINQ – method utilizes code generated by Visual Studio 2008 and takes advantage of the stored procedures for CRUD operations.

The below list describes each of the five tests performed by the four above methods.

• Read – Update One Record Single Connection – this test simulates the retrieval of a single record, the changing of the record by a process and the updating of the record over a single connection to the database.  NOTE:  This is also the very first test in the set and you will notice that it records a longer execution duration than the next test.
• Read – Update One Record Two Connections – this test simulates a user loading a form with a single record, editing of the record and then updating the record over two separate connections to the database.
• Read 911 records – this test reads all records in the database where the last name starts with “a.”  911 is the total number of records returned.
• Read Insert and Delete – this test inserters a record, rereads that record and deletes that same record.
• Read and Update 19,972 records using same connection – this test simulates business processing with complex business layer calculations being applied to a set of data.  All the records are loaded into business objects, that collection of objects is iterated and each object has a field updated using a calculation and each individual record is updated back to the database.  The same database connection is used to retrieve the records and to write them back to the database.
• Read and Update 19,972 records using SUBMIT CHANGES same connection – this test is only performed by LINQ and is TestNumber 6.  I did this to show the difference between using a stored procedure and LINQ to update a large number of records in a batch.

Test Results

All of these tests were run against a production SQL Server in the middle of the day.  The client was a 2 Core Dual 2.4GHz with 2GB memory running Vista x32.

This test clearly shows that LINQ to SQL is just as fast or faster than other techniques for building business entity objects from a database query.

It is only in the Update department that LINQ to SQL turns in slower times.  The average time to retrieve 19,972 records, iterate through them and write individual updates back to the database was 16 seconds.  Pretty impressive.  LINQ using stored procedures took 24 seconds.  Not back either.  However, when I used the LINQ SubmitChanges command it took 1:28 seconds.

Comments

After showing this to others at my work, they looked at me and said, what is the conclusion?  Which technique should we go with?  We can generate all of the code for any of the techniques.  The leanest method is the ManualBuilder method.  It consistently turns in the second fastest time and does not require any caching of precompiled queries, IL execution code or reflection property lists.  So for my next few projects, I’m sticking with “old school” DataReaders and optimized .NET object loading.

Choosing the ManualBuilder does not prevent me from using LINQ within the application once the object are loaded.  For object loading, I prefer simpler business objects with less overhead and less dependencies.

Close

Your application requirements and development resources will dictate your choices.  Making an informed decision that meets your needs is what this post is about.

I hope this sample gets you to investigate the various options you have for loading business entity objects from a database.

Source Code: After downloading the source code you MUST change the file extension from .zip.DOC to .zip. This is a requirement of WordPress.com.

The source includes a Visual Studio 2008 solution and SQL Server 2005 database for testing.  At your option, you could very easily download the code and point it to any of your current databases and run tests against them.

Download Source and SQL Database 779KB

Hope you can learn just a little bit more about .NET from this article and the Sample Series.

Just a grain of sand on the worlds beaches.