c# - 如何通过 LINQ-to-SQL 和普通 SQL-Server 比较查询的性能

Question

我想比较 3 种方法的查询执行性能:

• 查询类型化数据集
• 使用 LINQ-to-SQL 查询
• 直接在 SQL Server 2008 R2 上执行的 SQL 查询

因此我想运行一些测试场景如下:

查询类型数据集

在这里，我使用数据集设计器创建了数据集。然后我像这样查询相应的 TableAdapter

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();

这会导致超时，因为它将首先从数据库中的表中获取所有数据，然后对接收到的对象执行查询。这是预期的并且很好。

查询类型数据集

我在 Visual Studio 中使用 O/R-Designer 创建了一个 LINQ-to-SQL 类

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

这总共需要 2294 毫秒。到目前为止，这似乎是合理的。

SQL 查询

将 LINQ-to-SQL-Query 的执行时间与在 SQL Server Management Studio 中执行的实际 SQL 查询的执行时间进行比较时，事情变得很奇怪。

declare @min datetime2 = '20140501';
declare @max datetime2 = '20140502';

select * 
    from MyTable 
    where   Date >= @min
        and Date <= @max

这需要 6 秒多一点的时间，准确地说是 6211 毫秒(取自 SQL Server Profiler - Batch Completed)。

这怎么可能？

我想我可能没有正确理解 LINQ-to-SQL 的概念。我认为对查询调用 ToArray() 会导致将查询元组实际传输到数组中。但是，这比直接在服务器上执行查询要快多少(大约 3 倍)呢？

是否有更好的方法来比较这两个概念的查询性能？

PS: LINQ-to-SQL 生成的查询与在 SQL Server Mgmt Studio 中执行的查询相同

编辑:LINQ-to-SQL 生成的查询看起来……像下面这样

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'

我已经尝试在 SSMS 中执行它，但它仍然需要 6 秒左右....

Answer 1

这可能归因于非常不同的查询构造 - 特别是参数化和使用的日期时间类型。您可以在 SSMS 中比较:

或者您可以在 SSMS 中执行相同的操作，只需:

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;

要在 ADO.NET 中执行与原始查询相同的测试，您需要一个参数化 查询，并将值作为常规 DateTime 传递，因为那样会与其他人比较好:

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()) {...}
    }
}

或者更简单地说，使用像 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();

其中 SomeType 具有类似于表中列的属性。