问题描述:

I want to compare query execution performance of 3 methods:

  • Query on Typed DataSet
  • Query usingLINQ-to-SQL
  • SQL-queries executed on SQL Server 2008 R2 directly

Therefore i'd like to run a few test szenarios which would be the following:

Query on typed Dataset

Here i've created the Dataset using the Dataset Desinger. I am then querying the corresponding TableAdapter like this

var minDat = new DateTime(2014, 5, 1);

var maxDat = new DateTime(2014, 5, 2);

var ta = new MyTableAdapter();

vat res = (from row in ta.GetData()

where row.Date >= minDat && row.Date <= maxDat

select row).ToArray();

This results in a Timeout, as it would first get all the Data from the table in the database and execute the query on the received object. This is expected and fine.

Query on typed Dataset

I've created a LINQ-to-SQL Classes using O/R-Designer in Visual Studio

var minDat = new DateTime(2014, 5, 1);

var maxDat = new DateTime(2014, 5, 2);

var context = new LtSqlDataContext();

var query = (from row in context.MyTable

where row.Date >= startTime && row.Date <= endTime

select row).ToArray; // I do the .ToArray() to actually receive the whole set of results, i guess here i am doing sth. wrong

This takes 2294 ms total. That seems reasonable so far.

SQL Query

Things are getting strange when comparing the execution time of the LINQ-to-SQL-Query with an actual SQL Query executed in SQL Server Management Studio.

declare @min datetime2 = '20140501';

declare @max datetime2 = '20140502';

select *

from MyTable

where Date >= @min

and Date <= @max

This takes a little more than 6 seconds, 6211 ms to be exact (taken from SQL Server Profiler - Batch Completed).

How it this possible?

I think maybe I didn't get the Concept of LINQ-to-SQL right. I thought calling ToArray() on the query would lead to actually transferring the queries tuples into an array. But how would that be faster (by approx. 3 times) than the query when executed on the server directly?

It there a better way to compare the Query performance of the 2 concepts?

PS: The query generated by LINQ-to-SQL is the same like the one executed in SQL Server Mgmt Studio

EDIT:

The query generated by LINQ-to-SQL looks sth. like the following

set quoted_identifier on

set arithabort off

set numeric_roundabort off

set ansi_warnings on

set ansi_padding on

set ansi_nulls on

set concat_null_yields_null on

set cursor_close_on_commit off

set implicit_transactions off

set language Deutsch

set dateformat dmy

set datefirst 1

set transaction isolation level read committed

exec sp_executesql N'SELECT [t0].[Column1], [t0].[Column2], [t0].[Column3], [t0].[Column4], [t0].[Column5], [t0].[Date], [t0].[Column7]

FROM [Alle].[MyTable] AS [t0]

WHERE ([t0].[Date] >= @p0) AND ([t0].[Date] <= @p1)',N'@p0 datetime2(7),@p1 datetime2(7)',@p0='2014-05-01 00:00:00',@p1='2014-05-02 00:00:00'

I have tried executing it in SSMS but it still takes 6 or so seconds....

网友答案:

This could be down to the very different query construction - in particular, parameterization, and the type of datetime used. You could compare in SSMS to:

Or you could do the same in SSMS with simply:

declare @min datetime = '20140501', @max datetime = '20140502';

exec sp_executesql N'select * 
from MyTable 
where   Date >= @min
and Date <= @max', N'@min datetime, @max datetime', @min, @max;

To perform the same test in ADO.NET as a raw query, you would have a parameterized query, and pass in the values as regular DateTime, since that will compare well to the others:

var minDat = new DateTime(2014, 5, 1);
var maxDat = new DateTime(2014, 5, 2);
using(var cmd = conn.CreateCommand()) {
    cmd.CommandText = @"select * 
from MyTable 
where   Date >= @min
    and Date <= @max";
    cmd.Parameters.AddWithValue("min", minDat);
    cmd.Parameters.AddWithValue("max", maxDat);
    using(var reader = cmd.ExecuteReader()) {
        while(reader.Read()) {...}
    }
}

or perhaps more simply, with something like dapper:

var minDat = new DateTime(2014, 5, 1);
var maxDat = new DateTime(2014, 5, 2);
var rows = conn.Query<SomeType>(@"select * 
from MyTable 
where   Date >= @min
    and Date <= @max", new { minDat, maxDat }).ToList();

where SomeType has properties that resemble the columns in the table.

网友答案:

How many rows are returned?

.

565173 rows are returned

There you go. SSMS is slow processing many rows. Your benchmark is invalid. Execute the LINQ query and the SQL query using ADO.NET from your app. Iterate through all rows. In the ADO.NET version, retrieve all column values to force deserialization.

Probably, the duration will now match rather closely, with ADO.NET being faster.

If LINQ to SQL is still faster there is something else going on such as a bad cached plan or SET options. For such a simple query I'd be surprised though.

相关阅读:
Top