从C＃在SQL Server Management Studio中运行，当SQL查询超时快 [英] SQL query times out when run from C#, fast in SQL Server Management Studio
问题描述
我有一个执行一个SQL查询，使用下面列出的code A C＃程序。我一直在使用这种$ C $下，没有任何问题，直到有一天一段时间。
我传递查询字符串到SQL包括字符串，这是股票的标识符的列表。前几天我跑它和查询超时，将运行一个多小时，如果我写的话。我花了几天试图调试这一点。在我原来的查询中，有大约900标识符。
我试着改变一切我能想到的，而我得到的结果我无法解释。
例如：
-
查询可与股之一列表，但是不具有相同长度的另一列表中串和总长度的数目方面
-
它与一个列表，但不与同一个列表以相反的顺序
-
有一个列表，它的工作原理，如果恰好有900标识符但如果有899或901，我可以包含或排除不同的标识符，并得到相同的结果，所以它不是什么时髦的一个的标识符。
在每一种情况下，我捕捉到了正在经过我的程序并拷贝到SQL Server Management Studio中的查询字符串，并在任何情况下，查询运行在1秒内。
我看过的一切，我可以在此与其他论坛有关工作在SQL Server Management Studio中的查询，但时间从一个程序运行的时候，但这似乎不同之处在于我能找到它失败案例和类似的情况下，它不工作。
我会在哪里我可以看看，看看什么可能会在AP preciate建议。
使用（SqlConnection的康恩=新的SqlConnection（_connectString））
{
conn.Open（）;
使用（CMD的SqlCommand =新的SqlCommand（查询字符串，康涅狄格州））
{
cmd.Parameters.Clear（）;
cmd.CommandTimeout = _TIMEOUT;
SqlParameter的参数;
如果（PARMS！= NULL）
{
的foreach（在parms.Keys串PARM）
{
参数= cmd.Parameters.AddWithValue（PARM，PARMS [PARM]）;
}
}
SqlDataReader的读者= cmd.ExecuteReader（）;
而（reader.Read（））
{
QueryResult中的记录=新QueryResult中（）;
record.Fields =新的名单，其中，对象＆gt;（）;
的for（int i = 0; I＆LT; returnColumns ++ I）
{
对象值= reader.GetValue（ⅰ）;
如果（价值==的DBNull.Value）
record.Fields.Add（空）;
其他
record.Fields.Add（值）;
}
result.Add（记录）;
}
reader.Close（）;
}
conn.Close（）;
}
下面是我的查询。在这个版本中，我有65股，它不工作（小于= 64不工作）
。 选择
不同的a.Cusip
，d.Value_ / f.CumAdjFactor为split_adj_val
从qai.prc.PrcScChg一个
加入qai.dbo.SecMapX b
上，code = b.ven code
和b.VenType = 1
和b.exchange = 1
和b.Rank =（选择最小值（等级）的qai.dbo.SecMapX那里法师code = A，code和VenType = 1和Exchange = 1）
加入qai.dbo.SecMapX B2
在b2.sec code = b.sec code
和b2.ventype = 40
和b2.exchange = 1
和b2.Rank =（选择最小值（等级）的qai.dbo.SecMapX，其中二段code = b.Sec code和VenType = 40交易= 1）
加入qai.dbo.SecMapX B3
在b3.sec code = b.sec code
和b3.ventype = 33
和b3.exchange = 1
和b3.Rank =（选择最小值（等级）的qai.dbo.SecMapX，其中二段code = b.Sec code和VenType = 33交易= 1）
加入qai.dbo.DXLSecInfoÇ
在b2.Ven code = C。code
加入qai.dbo.DXLAmDatað
基于C，code = D。code
和d.Date_ = @date
和d.Item = 6
左连接qai.dbo.DS2Adj˚F
ON f.Info code = b3.Ven code
与f.AdjType = 2
和f.AdjDate＆LT; = @date
和（f.EndAdjDate＆GT; = @date或f.EndAdjDate为null）
哪里
a.cusip在（'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'）
三样东西来看待，才能对preference：
- 避免使用
AddWithValue（）功能，因为这可以带来灾难性影响性能的时候ADO.Net猜测列类型错误。难道是你必须能够设置明确的DB型为每个参数 - 。看看<一href="http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx"相对=nofollow> OPTIMIZE FOR UNKNOWN 。
- 。看看选项重新编译。做到这一点的人都失败后，才，因为使用它放弃了一些从参数化查询的性能优势。
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.
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.
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.
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.
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();
}
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:
- 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 - Look into OPTIMIZE FOR UNKNOWN.
- Look into OPTION RECOMPILE. Do this only after the others have failed, as using it gives up some of the performance benefits from parameterized queries.
这篇关于从C＃在SQL Server Management Studio中运行，当SQL查询超时快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
