|"Rapid C# for Windows Development" by Joseph Chancellor is available (in .pdf format) for free download from LLBLGen Pro site.|
Monday, July 30, 2007
Monday, July 16, 2007
|Abstract: This article presents benchmarking results of performance of SP calls using LINQ, Data-access application block and ADO.NET.|
Some time ago I was doing LINQ performance benchmarking, looking for an option of replacing our existing data layer with it. One thing that was especially important for me to check - was performance of executing SP's. The reason is that most of our DAL logic resides in SP's at one hand and that performance-critical parts are implemented as SP's as well (for all the good reasons, like possibility to benefit from queries compilation etc).
Below are performance benchmarking results of execution uspGetManagerEmployees SP from AdventureWorks DB. (Basically it does some sort of JOIN to retrieve records of employees data for the specified Manager ID).
I tested three data-access models - raw ADO.NET vs. DAAB (our current DAL workhorse) vs. LINQ (May 2006 preview version).
As a prerequisite for running this test you'll need AdventureWorks DB and installation of LINQ.
So here goes the test code:
1. Plain ADO.NET:
2. DAAB (Data Access Application Block):
3. LINQ (using ORM layer produced by SQLMetal.exe - "c:\Program Files\LINQ Preview\Bin\SqlMetal.exe" /server:. /database:AdventureWorks /pluralize /sprocs /code:AdventureWorks.cs):
Here are timing results of this benchmarking test:
So, comparing to the raw ADO.NET - DAAB is 8% slower and LINQ is 28% slower.
Comparing to DAAB - LINQ is 18% slower.
CPU usage intensity is about 2% average for raw ADO.NET vs. about 8% for DAAB vs. about 20% for LINQ - order of magnitude worse than raw ADO.NET (CPU graphs captured with perfmon below).
Fig. 1 ADO.NET - CPU usage
Fig. 2 DAAB - CPU usage
Fig. 3 LINQ - CPU usage
In overall - this performance overhead is quite disappointing. I still consider to use LINQ, as we have DAL/BLL servers in our architecture, which we may merely upgrade to faster hardware, while isolating thin-client computers from this performance overhead. I think it's a big challenge for the LINQ team to address its performance.