Need a simple CRM and Project Management system?
Check out JobNimbus - CRM for Contractors and Service Professionals.

Performance benchmarks for LINQ vs. SqlDataReader, DataSet - Selects: Part 1

I've been wondering about LINQ to SQL performance ever since I started using it a couple of months ago. All I've been hearing from MS is how well tuned the LINQ queries are and how performance is "comparable" to current data access methods. Of course, LINQ has never been billed as the utlra-best performance way to access a database available. What's really powerful about LINQ is the strongly typed objects, language query abilities, and the ease of use.

In looking at performance, I started where we are all start... google. I found a couple of places to get me going. An article by Alex Pinsker where he set up a simple benchmarking framework for LINQ and SqlDataReader. You can find it here:



http://alexpinsker.blogspot.com/2007/07/benchmarking-linq-vs.html

The basic findings were:

LINQ to SQL is 28.80% slower than SqlDataReader

LINQ to SQL is 8.49% slower than the Database Applicaton Block (DAAB)

I used his application framework to write my own benchmarking tool. The problem with this tool was that it didn't address all the possibilities of why LINQ was performing worse than the other methods (it only used stored procedures, etc.). AND the last run was with Beta 2 sometime in January 2008 so it was a bit dated since the actual full release of LINQ was later in 2008 (and performance gains may have been made).

I did my own test runs with a slightly modified version of Alex's tool using SQL queries instead of stored procedures, and the results were shocking. Here's the execution output of my Benchmarker tool:

******************************
Run : 1
******************************
SQL Data Reader: 2,205 ms
LINQ to SQL: 7,334 ms
Strongly Typed DataSet: 3,354 ms
 
******************************
Run : 2
******************************
SQL Data Reader: 2,081 ms
LINQ to SQL: 6,878 ms
Strongly Typed DataSet: 3,370 ms
 
******************************
Run : 3
******************************
SQL Data Reader: 2,132 ms
LINQ to SQL: 6,963 ms
Strongly Typed DataSet: 3,402 ms
 
-------------------------------------------------------------------
Data Access Type    Run 1    Run 2    Run 3    Average
-------------------------------------------------------------------
SQL Data Reader        2,205    2,081    2,132    2,139
LINQ to SQL        7,334    6,878    6,963    7,058
Strongly Typed DataSet    3,354    3,370    3,402    3,375
 
-------------------------------------------------------------------
SQL Data Reader Breakdown
-------------------------------------------------------------------
329.97% faster than LINQ to SQL
157.78% faster than Strongly Typed DataSet
 
-------------------------------------------------------------------
LINQ to SQL Breakdown
-------------------------------------------------------------------
329.97% slower than SQL Data Reader
209.13% slower than Strongly Typed DataSet
 
-------------------------------------------------------------------
Strongly Typed DataSet Breakdown
-------------------------------------------------------------------
157.78% slower than SQL Data Reader
209.13% faster than LINQ to SQL

Basically, I executed these tests with 10,000 iterations for each database interaction type and then averaged this across 3 runs of the tool. You can download the code and run it yourself here:

 
Benchmarker.zip

NOTE: To create the test database I used, create a new database in SQL Server 2005 Express called "benchmark" and run the script included in the zipped project called "create_employee_table_script.sql". (NOTE: This will create the Employee table we query against as well as fill it with 25 rows of data).

Here's the method for LINQ to SQL:

public static long RunLinqToSqlBench()
{
    Stopwatch watch = new Stopwatch();
    watch.Start();
 
    using (BenchmarkDataClassesDataContext context = new BenchmarkDataClassesDataContext(CONNECTION_STRING))
    {
        for (int i = 0; i < ITERATIONS_TO_EXECUTE; i++)
        {
            var query = from q in context.Employees
                        select q;
 
            // since linq is deferred execution, force the query to execute by doing a ToList()
            List<Employee> employeeList = query.ToList();
        }
    }
 
    watch.Stop();
    return watch.ElapsedMilliseconds;
}

Here's the method for the SQL Data Reader:

public static long RunSqlDataReaderBench()
{
    Stopwatch watch = new Stopwatch();
    watch.Start();
 
    using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
    {
        con.Open();
 
        for (int i = 0; i < ITERATIONS_TO_EXECUTE; i++)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = SQL;
                using (SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default))
                {
                    List<Employee> employeeList = new List<Employee>();
                    // read rows returned
                    while (reader.Read())
                    {
                        // to make it fair, we will add this to a list just like the LINQ query.
                        // create new employee object and add fields queried
                        Employee employee = new Employee();
                        employee.Id = (int)reader[0];
                        employee.FirstName = (string)reader[1];
                        employee.LastName = (string)reader[2];
                        employee.Email = (string)reader[3];
                        employee.DateCreated = (DateTime)reader[4];
                        employee.DateUpdated = (DateTime)reader[5];
                        // add to list
                        employeeList.Add(employee);
                    }
                }
            }
        }
    }
 
    watch.Stop();
    return watch.ElapsedMilliseconds;
}

Here's the Strongly Typed DataSet method:

public static long RunStronglyTypedDataSetBench()
{
    Stopwatch watch = new Stopwatch();
    watch.Start();
 
    using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
    {
        con.Open();
 
        for (int i = 0; i < ITERATIONS_TO_EXECUTE; i++)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = SQL;
 
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    EmployeeDataSet ds = new EmployeeDataSet();
                    da.Fill(ds);
                }
            }
        }
    }
 
    watch.Stop();
    return watch.ElapsedMilliseconds;
}

I did a lot of research to see if I could poke holes in the tests and see why I was getting such shocking results with LINQ to SQL vs. other data access methods. Here's a couple of notes that I tried:

  • I tried switching the order of the tests to see if that would do anything. I got the exact same results.
  • Since the DataSet and LINQ to SQL methods have to do the extra step of building the strongly typed object for each loop, I did the same in the Reader to slow it down and make everything fair. This actually didn't make much of a difference in performance.
  • I thought this might have something to do with the fact that LINQ to SQL is generating the SQL code to execute against the database so I checked the query and used the EXACT same SQL syntax in the other 2 queries (no difference to performance). NOTE: Also that this does show that, at least in simple cases like this, LINQ generates very good SQL query syntax.
  • There is a possibility that the timer is not high resolution that I used but the fact of the matter is that this is a real world test of what I do every day when accessing data so even with other factors not accounted for, this is my real world results.

So what's the take away? LINQ is built to speed development time for agile projects. It actually has "descent" performance in comparison to other O/R mapper tools (our own internal tool runs almost 5 times slower than LINQ). So for most of my projects, when a database call does not require tremendous performance, I'm using LINQ. When I need speed, I drop out to a Reader instead. Then I can get the best of both worlds.

Large data

Hi there,
Thank you for this article and this benchmarker, it was very useful, but I noticed one thing. I have tried to use large data (about 10000 rows) and ran the test again. The results were shocking because the LINQ to SQL was markably 3 or 4 times faster than the other two ways.
I took the same data you represented with 10000 rows of employess and an iteration of 1000, and I got these results:

******************************
Run : 1
******************************
SQL Data Reader: 34,767 ms
LINQ to SQL: 11,166 ms
Strongly Typed DataSet: 49,797 ms

******************************
Run : 2
******************************
SQL Data Reader: 32,809 ms
LINQ to SQL: 10,895 ms
Strongly Typed DataSet: 49,299 ms

******************************
Run : 3
******************************
SQL Data Reader: 32,562 ms
LINQ to SQL: 11,334 ms
Strongly Typed DataSet: 49,037 ms

-------------------------------------------------------------------
Data Access Type Run 1 Run 2 Run 3 Average
-------------------------------------------------------------------
SQL Data Reader 34,767 32,809 32,562 33,379
LINQ to SQL 11,166 10,895 11,334 11,131
Strongly Typed DataSet 49,797 49,299 49,037 49,377

-------------------------------------------------------------------
SQL Data Reader Breakdown
-------------------------------------------------------------------
299.87% slower than LINQ to SQL
147.93% faster than Strongly Typed DataSet

-------------------------------------------------------------------
LINQ to SQL Breakdown
-------------------------------------------------------------------
299.87% faster than SQL Data Reader
443.60% faster than Strongly Typed DataSet

-------------------------------------------------------------------
Strongly Typed DataSet Breakdown
-------------------------------------------------------------------
147.93% slower than SQL Data Reader
443.60% slower than LINQ to SQL

I haven't got any explanation for this.. Have you?

Full Marks for this Article

that is very useful to know the programmer/Devloper

Your performance

When you used the Datareader you didnt check for null values first. Your code would throw an exception when you tried to convert:
(DateTime)reader[4];
(Int)reader[2];

So, you didnt factor in how most everyone uses it in the real world.

Checking for null in datareader

Try it with and without. You will get the same benchmark result. The if check is negligible to the time metrics that we captured.

But you are correct, many would check for null so you can add it if you like. I stand by the results either way.

WhAeqMUSsEw

Could you write about Physics so I can pass Scinece class?

I've posted a follow-up to this post in

I've posted a follow-up to this post in a "part 2" that examines both of these issues regarding the use of Compiled Queries and the call to sp_reset_connection in LINQ. It's here:

http://devtoolshed.blogspot.com/2008/05/performance-benchmarks-for-linq-...

You can get speed equivalent to direct

You can get speed equivalent to direct DataReader code when you use a L2S compiled query. Otherwise you are always going to be much slower since you are doing a lot of work on the client to translate the query and generate the materializer. How much slower you are is going to be based on factors that vary depending on the query, the location of the database and the amount of data in the result.

One thing I noticed when I ran your

One thing I noticed when I ran your benchmark was the exec sp_reset_connection that Linq runs after each select. Anyway around that? Running that 1,000 times surely took a ms or two...