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

SQL query times out when run from C#, fast in SQL Server Management Studio(从 C# 运行时 SQL 查询超时，在 SQL Server Management Studio 中快速运行)

问题描述

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

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.

我正在向 SQL 传递一个查询字符串，其中包含一个字符串列表，这些字符串是股票标识符.几天前我运行它并且查询超时，如果我让它运行它将运行一个多小时.过去几天我一直在尝试调试它.在我最初的查询中，大约有 900 个标识符.

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.

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

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

例如:

1. 该查询适用于一个股票列表，但不适用于另一个在字符串数和总长度方面具有相同长度的列表

1. 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

使用一个列表，如果正好有 900 个标识符，它就可以工作，但如果有 899 或 901 个，它就不行，我可以包含或排除不同的标识符并获得相同的结果，所以它不是一个时髦的东西的标识符.

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.

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

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 中工作但从程序运行时超时的所有内容，但这似乎有所不同，因为我可以找到失败的情况和类似的情况不工作.

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.

我会很感激有关我可能会在哪里查看可能发生的事情的建议.

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

这是我的查询.在这个版本中，我包含了 65 只股票，但它不起作用(<=64 确实有效).

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

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')

推荐答案

看三件事，按优先顺序排列:

Three things to look at, in order of preference:

1. 避免使用AddWithValue() 函数，因为当 ADO.Net 猜测列类型错误时，这可能会产生灾难性的性能影响.做你必须能够为每个参数设置一个明确的数据库类型
2. 查看选项重新编译.
3. 查看 优化未知.只有在其他人失败后才这样做.

1. 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
2. Look into OPTION RECOMPILE.
3. Look into OPTIMIZE FOR UNKNOWN. Do this only after the others have failed.

这篇关于从 C# 运行时 SQL 查询超时，在 SQL Server Management Studio 中快速运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！