Time to create 19,972 business objects
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.
Josh Smith and I are very busy preparing for the Charlotte, NC May 17th Code Camp as we will be teaching the WPF Multi-Tier Business Application Track. This will be a very exciting day for WPF and LOB and hope to see you there!
I am also writing the 4th installment of the WPF Business Application Series and hope to deliver the entire application soon and then continue to write articles against it.
During this preparation the topic of loading Business Entity Objects from the database keesp coming up. Each time I reflect on that topic these questions immediately come to mind:
- Should I stick with the time tested and super fast SQLDataReader?
- Should I move to LINQ to SQL?
- Should I load objects using reflection?
- Should I write object loader code for each object?
- When I add Silverlight into the mix will that effect my above choice?
I did a good bit of research and will present the results.
When researching 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.
I hope this blog post encourages you to perform your own testing before choosing Door #1 or Door #2.
I wanted the test to simulate a real world database request that involved selecting almost 20,000 rows and building a generic collection of objects to process. Not that any of us would be selecting all our customers and stuffing them into a ComboBox, but that the test would simulate building 20,000 instances of a business object class that would be processed and the results written back to the database. For this bench mark, I’m not writing results back to the database. Business applications I write can easily generate this type and volume of traffic when performing business processing so I elected to use this as my test. This type of request is also commonly used to preprocess data for a report.
Users could care less about bench marks. They judge our applications based on how it appears to run on their desktop in their unique environment. How long does a form take to appear? When the customer form opens and the users clicks on the History tab, is there a delay before the 500 records appear in the grid? When the user runs a report that requires preprocessing, how long does it take? How long does batch processing take? Is the UI frozen while processing?
When I design applications my first priority is delivering documented, maintainable code, my second priority is performance.
As architects we are not only concerned with how responsive and quick our UI’s are, but must take into consideration processing time on our servers. If we have clients that are connected over the web, almost all processing will take place on the web or application server. It is imperative that server deployed code runs as fast and efficient as possible.
Bench Mark Testing
Let me introduce you to the players in this bench mark.
SQL Server Express Database
Included in the download is the BenchMarkData SQL Server Express 2005 Database. 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.
SQL Server Profiler
I used the SQL Server Profiler to record bench marks from the SQL Server during processing and have included the results below.
Bench Mark Loading Objects Sample Application
This is a very simple console application that demonstrates four different methods of loading business objects. I tried to make each test as far as possible. Each test will open and close the connection to the database. Each test has been optimized and correctly handles DBNull coming from the database and correctly set Nullable(Of Type) properties.
Below is the list of the four methods. Each method will build a generic List(Of Contact) or List(Of ConactEntity). LINQ builds the Contact class and the other methods build the ConactEntity class. I did this so that each technique would build a class like it would in a real world application.
- Manual – the LoadManual 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 – the LoadReflection 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.
- Dynamic – the LoadDynamic 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. This code reminds me of the old ADO GetRows method we used on early ASP web sites. This too was insanely fast compared to any other method of reading data from SQL Server.
- LINQ – the LoadLINQ method utilizes code generated by Visual Studio 2008, creates a DataContext and with a single line of code, transforms the query results into a List(Of Contact).
This test was run on my home system, an Intel Core2 Quad 2.4 GHz with 4 GB main memory, 4 MB L2 cache and a single hardware RAID 1 SATA disk array. This configuration would be common among server class machines except large servers would have more memory and dual SCSI RAID arrays.
From the above image, the stats that stick out are the low and high results (lower time is better). The LINQ loader took just over one second. The Dynamic loader to .063 seconds which is insanely fast. The other two were longer but still much faster than the LINQ solution. In a UI, time is perception. Add in any other latencies, and the user is now waiting.
In the below image, we can see how each of the above techniques interacted with the SQL Server while iterating the SQLDataReader. The duration is measured in microseconds.
All of the techniques are very efficient in terms of working with the SQL Server. Each time I ran this, I got different results with respect to CPU, but he Reads and Duration columns remained the same. To be honest, I do not know why the Reflection method Duration was so long compared to the others or why the LINQ to SQL method was 2.5 times slower than the Manual of Dynamic methods. The SQL queries were the same except for the way the LINQ query used fully qualified column names.
Real World Development
I strongly recommend that you read Herb’s article and the comments other developers posted. Herb’s article is about “how” something can be accomplished and he presents several solutions for the accomplishing the same programming task. I simply added a LINQ to SQL solution to the mix and presented these here for your inspection and review.
I don’t know about you, but I like to adopt new technologies slowly. Give myself and others around me time to prove them from a security, development, performance, reliability, scalability, toolset availability, toolset maturity and maintenance perspectives.
From a code generation standpoint, all four solutions can be code generated. I don’t recommend using the Visual Studio ORM Designer or SQLMetal tools at this time because they do not provide any ability for developers to place either XML comments or attributes on their class properties. You can use the XML Map Files to make this happen but have to pay additional performance hits for this ability. The lack of support for the developers meta data in the ORM tools has caused me to write all my own code generation tools. I hope this will be corrected in future releases of the ORM or other Visual Studio RAD tool offerings. While at the April 2008 MVP Summit I did speak with several teams at Microsoft about this current limitation of meta data awareness.
I have a blog post that raises some questions on ORM tools consuming meta data that you can read here.
The time tested Manual technique is super fast but requires the most code. If this code is generated then this becomes a non-issue. The Reflection and Dynamic methods offer very simple and small footprint for great functionality and performance. The Dynamic method has a draw back in that you can’t debug the generate IL code using the standard debugging techniques most developers are comfortable with.
I don’t think I’m ready to jump on the LINQ to SQL bandwagon for business object loading just yet. However, that does not mean that I can’t load my objects using the other techniques and then use LINQ to work with them.
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.
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.