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

Performance benchmarks for LINQ vs. SqlDataReader, DataSet - LINQ Compiled Queries: Part 2

In my previous post (Performance benchmarks for LINQ vs. SqlDataReader, DataSet - Selects: Part 1 ), I ran queries using LINQ to SQL, SqlDataReader, and Strongly Typed DataSets to see how they compare in the real world. I found that LINQ is much slower than either the SqlDataReader (no surprise there) or the Strongly Typed DataSets (I actually expected the DataSet to run slower).

Links to other posts in this series:
 



There were a couple of comments on the previous post I wanted to address. One in particular from Matt Warren http://blogs.msdn.com/mattwar/ suggested that I use a "complied LINQ to SQL" query. I did some research on this and found a nice little example of how to do this:

http://linqinaction.net/blogs/jwooley/archive/2007/09/04/linq-to-sql-compiled-queries.aspx

NOTE: To run this project, you need to have the database set up from the previous post so see Part 1 for details on getting the benchmarking database setup. You can download the code for this benchmark and run it yourself here:
Benchmarker2.zip

LINQ Compiled Query code isn't pretty. It's a little hard to understand at first because the syntax is a bit ugly/clunky but basically, you use generics to define a function pointer to another function. So I added this to the benchmarking tool and re-ran the tests. Here are the results:

 

 
******************************
Run : 1
******************************
SQL Data Reader: 1,582 ms
LINQ to SQL: 6,045 ms
Strongly Typed DataSet: 2,785 ms
LINQ to SQL Compiled: 2,472 ms
 
******************************
Run : 2
******************************
SQL Data Reader: 1,556 ms
LINQ to SQL: 5,855 ms
Strongly Typed DataSet: 2,696 ms
LINQ to SQL Compiled: 2,432 ms
 
******************************
Run : 3
******************************
SQL Data Reader: 1,553 ms
LINQ to SQL: 5,859 ms
Strongly Typed DataSet: 2,730 ms
LINQ to SQL Compiled: 2,418 ms
 
-------------------------------------------------------------------
Data Access Type    Run 1    Run 2    Run 3    Average
-------------------------------------------------------------------
SQL Data Reader        1,582    1,556    1,553    1,563
LINQ to SQL        6,045    5,855    5,859    5,919
Strongly Typed DataSet    2,785    2,696    2,730    2,737
LINQ to SQL Compiled    2,472    2,432    2,418    2,440
 
-------------------------------------------------------------------
SQL Data Reader Breakdown
-------------------------------------------------------------------
378.69% faster than LINQ to SQL
175.11% faster than Strongly Typed DataSet
156.11% faster than LINQ to SQL Compiled
 
-------------------------------------------------------------------
LINQ to SQL Breakdown
-------------------------------------------------------------------
378.69% slower than SQL Data Reader
216.26% slower than Strongly Typed DataSet
242.58% slower than LINQ to SQL Compiled
 
-------------------------------------------------------------------
Strongly Typed DataSet Breakdown
-------------------------------------------------------------------
175.11% slower than SQL Data Reader
216.26% faster than LINQ to SQL
112.17% slower than LINQ to SQL Compiled
 
-------------------------------------------------------------------
LINQ to SQL Compiled Breakdown
-------------------------------------------------------------------
156.11% slower than SQL Data Reader
242.58% faster than LINQ to SQL
112.17% faster than Strongly Typed DataSet

Compiled queries with LINQ to SQL make a HUGE DIFFERENCE! LINQ is still not as fast as the reader but it is beating the DataSet consistently (which is what I thought it would to anyway). So problem solved, right?

Not exactly. Yes, this test did yield a tremendous boost in performance for LINQ but it is still roughly 1.5 times slower than a SqlDataReader. The other issue which is actually a bigger deal to me is the code maintenance/readibility/ease of use of LINQ. If I have to compile all my queries in LINQ to get solid performance metrics and cloud them with this difficult to read CompiledQuery syntax, then that really diminishes the value of LINQ as an Agile development tool. SqlDataReader queries are almost easier to read and manage than the LINQ compliled syntax.

Here's the code for the Compiled Query & LINQ to SQL compiled bench:
 

// This is a generic function type that returns a function (which we have defined as the pre-compiled query).
private static Func<BenchmarkDataClassesDataContext, IQueryable<Employee>>
    SelectAllCompiledQuery = System.Data.Linq.CompiledQuery.Compile(
                (BenchmarkDataClassesDataContext contextLocal) =>
                from q in contextLocal.Employees
                select q);
 
// This is the static function that runs the query in a loop.
public static long RunLinqToSqlCompiledBench()
{
    Stopwatch watch = new Stopwatch();
    watch.Start();
 
    using (BenchmarkDataClassesDataContext context = new BenchmarkDataClassesDataContext(CONNECTION_STRING))
    {
        for (int i = 0; i < ITERATIONS_TO_EXECUTE; i++)
        {
            // call the LINQ query using a compiled query instead.
            var query = SelectAllCompiledQuery(context);
 
            // since linq is deferred execution, force the query to execute by doing a ToList()
            List<Employee> employeeList = query.ToList();
        }
    }
 
    watch.Stop();
    return watch.ElapsedMilliseconds;
}

As you can see, the Compiled query syntax is kind of clunky. But it does yield much better performance for LINQ to SQL (mostly because it caches all the work of building the query dynamically from the C# LINQ syntax and reuses that query plan).

There was another comment in regards to the fact that for some unknown reason, when LINQ finishes a query, it calls the built in stored procedure "sp_reset_connection". I fired up SQL Server Profiler and ran a trace while I re-ran the benchmarker2 results. Sure enough, every single query for LINQ immediately calls "sp_reset_connection". The SqlDataReader and Strongly Typed DataSet do not call this procedure. They just run the query. You can see the SQL Profiler screenshot for the reader here:

Here is the screenshot from SQL Profiler for the LINQ queries. You can see the stored procedure being called each time. WHY????

I googled this a bunch but found no real help. If I could find a way to tell LINQ to not make that stored procedure call, I bet I could get performance up even more.

As a side-note, one other thing I stumbled across as I was running this has to do with accessing values by ordinal position in the SqlDataReader. I had been casting the reader[i] to it's data type and assigning it the value of the Employee object's property like this:
 

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];

I changed them around to use the built-in methods of the reader object and found that I got about a roughly 2% - 5% performance gain in the speed of the SqlDataReader. So the code for the reader has been changed to this:
 

employee.Id = reader.GetInt32(0);
employee.FirstName = reader.GetString(1);
employee.LastName = reader.GetString(2);
employee.Email = reader.GetString(3);
employee.DateCreated = reader.GetDateTime(4);
employee.DateUpdated = reader.GetDateTime(5);

Other than that change, the rest of the code is the same as Part 1.

Very sad that linq underperforms DataReader

I was hoping that Linq static functions would be highly optimized to improve vectorization; it's not unreasonable to assume that Linq is checking processor and memory capabilities to optimize performance for the current architecture. Your benchmark implies that it does not.

Also: please include a date in your posts. I was able to infer how recent your post was by the dates of the comments, but I'd rather know when you posted this.

Finally: is .NET 4.0 Any better? If Linq has been fully optimized for 4.0, it's a huge, huge win for developers.

-

i download your sample code and i found probleam .
please try it on sqlserver enterprice

possible re-test

Would it be possible to recreate your test without the reset since that issue was resolved? Very helpful post!

Very Nice

Thanks!

But you can see in the code I posted

But you can see in the code I posted that I am explicitly closing the data reader connection. This doesn't seem to be related to the LINQ. Remember, I tried this with DataReader, DataSet, and LINQ but this issue only occurs when using LINQ.

maybe because in datareader code, it

maybe because in datareader code, it does not close current connection.

It may be "normal" but the problem is

It may be "normal" but the problem is that it is only called when making a LINQ query call. It is not called from a DataReader or DataSet so it seems to be something with the way LINQ sets up its query as opposed to a standard ADO.NET connection poll call.

Hi, sp_reset_connection is called from

Hi, sp_reset_connection is called from ado.net connection pool, that behavior is normal. greetings.

Open your connection

I find it funny when I hear people saying "that's the way it works so it's normal".

For your connection reset, open your DataContext connection before working with it and close it at the end. That gives a good extra performance boost.

db.Connection.Open()
db.Connection.Close()

I ran the profiler and -- no more connection reset!!

Except that it IS a normal

Except that it IS a normal thing. It indicates that you are using connection pooling (which is a good thing) and that it is resetting in preparation for the next command. Explicitly opening and closing a connection every time means you are not taking advantage of a connection pool.