使用带日期参数的SqlCommand时，为什么会出现超时？

I'm having code similar to this:

我有类似的代码:

DateTime startDate = new DateTime(2010, 12, 1);
DateTime endDate = new DateTime(2011, 12, 1);
string sql = "SELECT * FROM SomeTable WHERE SomeDateColumn BETWEEN @StartDate AND @EndDate";

using (var connection = new SqlConnection(_connString))
using (var command = new SqlCommand(connection)) {
    command.Parameters.AddWithValue("@StartDate", startDate.Date);
    command.Parameters.AddWithValue("@EndDate", endDate.Date);

    using (var reader = command.ExecuteReader()) {
        // filling up some objects...
    }
}

This times out.

这次超时了。

Just by changing the SQL query to include the dates in clear text, the query runs fine.

只需更改SQL查询以包含明文中的日期,查询就可以正常运行。

string sql = "SELECT * FROM SomeTable WHERE SomeDateColumn BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "'";

Using the Query Connection in VS2010, both queries runs fast, but as soon as i try it out in my program, the first one fails because it times out.

使用VS2010中的查询连接,两个查询都运行得很快,但是一旦我在我的程序中尝试它,第一个就会因为超时而失败。

Is there an explanation to this behaviour, or have I just missed something?

这种行为有解释,还是我错过了什么?

3 个解决方案

#1

The reason is that the query optimizer of SQL Server is not considering the parameter values when it picks indexes.

原因是SQL Server的查询优化器在选择索引时没有考虑参数值。

As such, it does not know whether your BETWEEN clause would pick 1 row, 10 rows, or a bazillion rows, and thus picks a table-scan.

因此,它不知道您的BETWEEN子句是选择1行,10行还是数十亿行,因此选择表扫描。

When you embed the dates as literals inside the SQL statement, the optimizer has all the information it needs in order to pick the right indexes.

在SQL语句中嵌入日期作为文字时,优化程序具有获取正确索引所需的所有信息。

What you can do is give query hints. If you know that the typical use-case for this query would involve a few rows, like for instance a span of a week, you can embed that knowledge into the SQL:

您可以做的是提供查询提示。如果您知道此查询的典型用例涉及几行,例如一周的跨度,您可以将该知识嵌入到SQL中:

SELECT * FROM SomeTable WHERE SomeDateColumn BETWEEN @StartDate AND @EndDate
OPTION (
    OPTIMIZE FOR @StartDate = '<some date here>`,
                 @EndDate = '<some other date here>`
)

You should also ensure that statistics has been recalculated recently on the indexes involved on that table, to help the query optimizer pick useful indexes.

您还应该确保最近在该表上涉及的索引上重新计算了统计信息,以帮助查询优化器选择有用的索引。

#2

Where is the @enddate parameter. Shouldn't itr be like

@enddate参数在哪里?不应该是这样的

DateTime startDate = new DateTime(2010, 12, 1); 
DateTime endDate = new DateTime(2011, 12, 1);

#3

Depending on whether a query contains parameters or not will determine how the query optimiser generates an execution plan. With literals it will generate the best plan for the supplied values with params it will generate the best plan for the values that could be supplied based on what i knows about the data in the table from the statistics.

根据查询是否包含参数,将确定查询优化器如何生成执行计划。使用文字,它将为params生成所提供值的最佳计划,它将根据我对统计数据中表中数据的了解,为可能提供的值生成最佳计划。

Probably creating some statistics and indexes for those date columns will help improve the performance of the param query.

可能为这些日期列创建一些统计信息和索引将有助于提高param查询的性能。

#1