freckles freckles - 15 days ago 6
C# Question

SQL query times out when run from C#, fast in SQL Server Management Studio

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:


  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

  2. it works with one list but not with the same list in reverse order

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

Answer

Three things to look at, in order of preference:

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