I have a C # program that executes an SQL query using the code below. I used this code for a while, without any problems until the day.
I am passing an SQL query string that contains a list of strings that are stock identifiers. A few days ago I launched it, and the request was timed and will last more than an hour if I let it go. I spent the last few days trying to debug this. In my initial request there were about 900 identifiers.
I tried to change everything that I can think of, and I get results that I can not explain.
For example:
the request works with one list of stocks, but not with another list of the same length in terms of the number of rows and the 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 this is not something scared with one identifier.
In each of these cases, I grabbed the query string, which is transmitted by my program and copied to SQL Server Management Studio, and in each case, the query is executed after 1 second.
I read everything I can on this and other forums about queries that work in SQL Server Management Studio, but the time is when starting from the program, but it looks like I can find cases where this fails, and similar cases when it does not work.
I would appreciate suggestions on where I can see what might happen.
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 request. In this version, I include 65 stocks and does not work (<= 64 works).
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')