在SQL Server Management Studio中快速从C＃运行时，SQL查询超时

I have a C# program that executes a SQL query, using the code listed below. I've been using this code for a while with no problems until the other day.

我有一个C＃程序，使用下面列出的代码执行SQL查询。我已经使用这段代码一段时间没有问题，直到前几天。

I'm passing a query string to SQL that includes a list of strings, which are stock identifiers. A few days ago I ran it and the query timed out, and will run more than an hour if I let it. I've spent the past few days trying to debug this. In my original query, there were about 900 identifiers.

我将查询字符串传递给SQL，其中包含一个字符串列表，这些字符串是库存标识符。几天前我运行它，查询超时，如果我让它运行一个多小时。我花了这么多天试图调试这个。在我的原始查询中，大约有900个标识符。

I've tried changing everything I can think of, and I get results I can't explain.

我已经尝试过改变我能想到的一切，但我得到了无法解释的结果。

For example:

例如：

the query works with one list of stocks, but not with another list of the same length in terms of number of string and total length

查询使用一个股票列表，但不能使用另一个字符串和总长度相同的长度列表
it works with one list but not with the same list in reverse order

它适用于一个列表，但不能以相反的顺序使用相同的列表
with one list, it works if there are exactly 900 identifiers but not if there are 899 or 901, and I can include or exclude different identifiers and get the same results, so it isn't something funky with one of the identifiers.

有一个列表，如果有正好900个标识符，它可以工作，但如果有899或901则不行，我可以包含或排除不同的标识符并获得相同的结果，因此它不是一个标识符之一的时髦。

In each of these cases, I captured the query string that is being passed by my program and copied into SQL Server Management Studio, and in every case, the query runs in 1 second.

在每种情况下，我都捕获了我的程序传递的查询字符串并将其复制到SQL Server Management Studio中，并且在每种情况下，查询都会在1秒内运行。

I have read everything I can on this and other forums about queries that work in SQL Server Management Studio but time out when run from a program, but this seems different in that I can find cases where it fails and similar cases where it doesn't work.

我已经在这个和其他论坛上阅读了关于在SQL Server Management Studio中工作但在从程序运行时超时的所有内容，但这似乎有所不同，因为我可以找到失败的情况和类似的情况工作。

I would appreciate suggestions about where I might look to see what might be going on.

我希望看到我可能会看到可能发生的事情的建议。

using (SqlConnection conn = new SqlConnection(_connectString))
{
    conn.Open();

    using (SqlCommand cmd = new SqlCommand(queryString, conn))
    {
        cmd.Parameters.Clear();
        cmd.CommandTimeout = _timeout;

        SqlParameter param;

        if (parms != null)
        {
            foreach (string parm in parms.Keys)
            {
                param = cmd.Parameters.AddWithValue(parm, parms[parm]);
            }
        }

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            QueryResult record = new QueryResult();
            record.Fields = new List<object>();

            for (int i = 0; i < returnColumns; ++i)
            {
                object value = reader.GetValue(i);

                if (value == DBNull.Value)
                    record.Fields.Add(null);
                else
                    record.Fields.Add(value);
            }

            result.Add(record);
        }

        reader.Close();
    }

    conn.Close();
}

Here is my query. In this version, I include 65 stocks and it doesn't work (<=64 does work).

这是我的查询。在这个版本中，我包括65股，它不起作用（<= 64确实有效）。

select
    distinct a.Cusip
,   d.Value_ / f.CumAdjFactor as split_adj_val

from qai.prc.PrcScChg a

join qai.dbo.SecMapX b
    on a.Code = b.venCode
    and b.VenType = 1
    and b.exchange = 1
    and b.Rank = (select Min(Rank) from qai.dbo.SecMapX where VenCode = a.Code and VenType = 1 and Exchange = 1)

join qai.dbo.SecMapX b2
    on b2.seccode = b.seccode
    and b2.ventype = 40
    and b2.exchange = 1
    and b2.Rank = (select Min(Rank) from qai.dbo.SecMapX where SecCode = b.SecCode and VenType = 40 and Exchange = 1)

join qai.dbo.SecMapX b3
    on b3.seccode = b.seccode
    and b3.ventype = 33
    and b3.exchange = 1
    and b3.Rank = (select Min(Rank) from qai.dbo.SecMapX where SecCode = b.SecCode and VenType = 33 and Exchange = 1)

join qai.dbo.DXLSecInfo c
    on b2.VenCode = c.Code

join qai.dbo.DXLAmData d
    on c.Code = d.Code
    and d.Date_ = @Date
    and d.Item = 6

left JOIN qai.dbo.DS2Adj f 
    ON f.InfoCode = b3.VenCode
    AND f.AdjType = 2
    and f.AdjDate <= @Date
    and ( f.EndAdjDate >= @Date or f.EndAdjDate is null )

where 
    a.cusip in ('00101J10', '00105510', '00120410', '00130H10', '00206R10',
    '00282410', '00287Y10', '00289620', '00724F10', '00817Y10', '00846U10',
    '00915810', '00936310', '00971T10', '01381710', '01535110', '01741R10',
    '01849010', '02000210', '02144110', '02209S10', '02313510', '02360810',
    '02553710', '02581610', '02687478', '03027X10', '03073E10', '03076C10',
    '03110010', '03116210', '03209510', '03251110', '03265410', '03741110',
    '03748R10', '03783310', '03822210', '03948310', '04621X10', '05276910',
    '05301510', '05329W10', '05333210', '05348410', '05361110', '05430310',
    '05493710', '05722410', '05849810', '06050510', '06405810', '06738310',
    '07181310', '07373010', '07588710', '07589610', '08143710', '08467070',
    '08651610', '09062X10', '09247X10', '09367110', '09702310', '09972410')

2 个解决方案

#1

Three things to look at, in order of preference:

根据偏好顺序要看三件事：

Avoid using the AddWithValue() function, as that can have catastrophic performance implications when ADO.Net guesses a column type wrong. Do what you must to be able to set an explicit DB type for each parameter
避免使用AddWithValue（）函数，因为当ADO.Net猜错列类型时，可能会产生灾难性的性能影响。执行必须为每个参数设置显式DB类型的操作
Look into OPTION RECOMPILE.
查看OPTION RECOMPILE。
Look into OPTIMIZE FOR UNKNOWN. Do this only after the others have failed.
查看OPTIMIZE FOR UNKNOWN。只有在其他人失败后才这样做。

#2

You haven't posted your query, but just based on how it's being built with the dynamic list of parameters and the sheer number of parameters, I'm going to make a guess and say it has something to do with parameter sniffing - see:

您还没有发布您的查询，但仅仅基于如何使用动态参数列表和参数数量来构建它，我将猜测并说它与参数嗅探有关 - 请参阅：

http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

The basic idea of the issue is an optimal query execution plan is created for a specific set of parameters, which is very sub-optimal for another set.

该问题的基本思想是为一组特定参数创建最佳查询执行计划，这对于另一组非常不理想。

There are several ways to get around parameter sniffing issues (thankfully, many of which opened up in sql server 2008).

有几种方法可以解决参数嗅探问题（幸好，其中很多都是在sql server 2008中打开的）。

You could:

你可以：

refactor your query
重构你的查询
add WITH RECOMPILE to your stored proc / option (recompile) to your query
将WITH RECOMPILE添加到您的查询的存储过程/选项（重新编译）
optimize for unknown/option (optimize for... to your proc/query
优化未知/选项（优化...到你的proc /查询
others?
其他？

#1