Monday, July 16, 2007

Benchmarking LINQ vs. DAAB vs. ADO.NET



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.
Few side-notes:
  • I've left opening of DB connection out of the context of benchmarked section. Only SP execution is measured.

  • I use ExecuteReader rather than ExecuteDataSet, as it is much more lightweight. For LINQ benchmark I using GetEnumerator().

So here goes the test code:

1. Plain ADO.NET:


public static void SqlSPBench()
{
string connectionString =
"Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True";

using( SqlConnection conn = new SqlConnection( connectionString ) )
{
conn.Open();

Stopwatch watch = new Stopwatch();
watch.Start();

int count = 10000;
for( int i = 0; i < count; i++ )
{
using( SqlCommand cmd = new SqlCommand() )
{
cmd.Connection = conn;
cmd.CommandText = "uspGetManagerEmployees";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new SqlParameter( "ManagerID", 3 ) );

using( SqlDataReader reader = cmd.ExecuteReader( CommandBehavior.Default ) )
{
//while (reader.Read())
//{
// Console.WriteLine(reader[3]);
//}
}
}
}
watch.Stop();
Console.WriteLine(
"Running " + count +
" SP's using SQL ExecuteReader took " + watch.ElapsedMilliseconds + " ms");
Console.ReadLine();
}
}


2. DAAB (Data Access Application Block):


public static void SqlSpDAAB()
{
Database adventureWorksDataBase = DatabaseFactory.CreateDatabase( "AdventureWorks" );

Stopwatch watch = new Stopwatch();
watch.Start();

int count = 10000;
for( int i =0; i<count; i++ )
{
using( DBCommandWrapper sp_uspGetManagerEmployees =
adventureWorksDataBase.GetStoredProcCommandWrapper(
"uspGetManagerEmployees" ) )
{
sp_uspGetManagerEmployees.AddInParameter( "@ManagerID", DbType.Int32, 3 );
using( IDataReader dataReader =
adventureWorksDataBase.ExecuteReader( sp_uspGetManagerEmployees ) )
{
//DataSet results =
// adventureWorksDataBase.ExecuteDataSet( sp_uspGetManagerEmployees );
}
}
}
watch.Stop();
Console.WriteLine(
"Running " + count + " SP's using DAAB took " +
watch.ElapsedMilliseconds + " ms");
Console.ReadLine();
}


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):



public static void LinqSPBench()
{
string connectionString =
"Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True";
AdventureWorks adventureWorks = new AdventureWorks( connectionString );

Stopwatch watch = new Stopwatch();
watch.Start();

int count = 10000;
for( int i =0; i<count; i++ )
{
StoredProcedureResult<UspGetManagerEmployeesResult> result =
adventureWorks.UspGetManagerEmployees( 3 );
IEnumerator<UspGetManagerEmployeesResult> enumerator = result.GetEnumerator();
//while( enumerator.MoveNext() )
//{
// Console.WriteLine( enumerator.Current.LastName );
//}
}
watch.Stop();
Console.WriteLine(
"Running " + count +
" SP's using LINQ took " + watch.ElapsedMilliseconds + " ms");
Console.ReadLine();
}


Here are timing results of this benchmarking test:
  • Running 10000 SP's using SQL ExecuteReader took 42806 ms

  • Running 10000 SP's using DAAB took 46443 ms

  • Running 10000 SP's using LINQ took 55136 ms




Results
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.



14 comments:

Anonymous said...

Hi Alex,

I'm also looking at both DAAB and LINQ. Which version of DAAB are you using in this benchmark? Have you tried the LINQ included in VS2008 Beta2? If the final release of VS2008 has the same performance like your benchmark, I need to rethink whether to use LINQ.

Alex Pinsker said...

Hey! Thanks for your comment.

I've used the old good DAAB v 1.1 in my benchmarking. I think there are performance improvements in the latest EL DAAB v 3.1, which only increases the performance gap. I didn't used LINQ included in VS 2008 beta, but I think it's exactly the same version.

fabrice said...

You should really redo your tests with .NET 3.5 Beta 2 because a lot of improvements have been made since the May 2006 prototype.
See http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx and the other parts of the series.

Alex Pinsker said...

Hi Fabrice,
Thanks for your comment!
I've repeated my test with .NET 3.5 (beta 2) - unfortunately it seems that there are no performance improvements at all... Check results here.

Shawnolius said...

Hi Alex,
Please could you include your "using" statements...I'm struggling to get the code to compile...

Alex Pinsker said...

Hi Shawnolius,
Here you go:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Query;
using System.Xml.XLinq;
using System.Data.DLinq;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Expressions;
using System.Collections;

Anonymous said...

Was a later version of LINQ tested again to see it's performance?

Alex Pinsker said...

Yes, and there is no performance improvement.

Ben Hodson said...

I've posted a follow-up to this performance test with some new results. Generally speaking, LINQ is much slower than the SQL Data Reader and my results correlate with Alex. You can see my post and methodology here:

http://devtoolshed.blogspot.com/2008/04/performance-benchmarks-for-linq-vs.html

Anonymous said...

Interesting. I had worse results than that (althought cpu usage could explain them).

Simple query to get 1 row by primary key.

LINQ (compiled query)

5-6 times slower than

DataAdapter (stored procedure, strongly typed dataset)

Anonymous said...

According to one of the speaker from Microsoft seminar that I attend to, LINQ is still not suitable for in querying very large records, still the best option is Stored Procedure.

Anonymous said...

According to one of the speaker of Microsoft seminar that I attend to. LINQ is still not suitable and comparable in terms of performance with Stored Procedure, Stored Procedure is still the no. 1 when you're querying very large records in a database.

Skowronek said...

If you take the time to review the actual SQL generated and results with LINQ to SQL, you'll be amazed at the bloatness of LINQ. Add joins/unions, you might as well go out and grab a coffee.

Run query profiler on all of these and you'll see what i mean.

Anonymous said...

you mean LinQ's performance is awful