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

Question

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 1

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

You could:

1. refactor your query
2. add WITH RECOMPILE to your stored proc / option (recompile) to your query
3. optimize for unknown/option (optimize for... to your proc/query
4. others?

Answer 2

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.