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

i have c# program executes sql query, using code listed below. i've been using code while no problems until other day.

i'm passing query string sql includes list of strings, stock identifiers. few days ago ran , query timed out, , run more hour if let it. i've spent past few days trying debug this. in original query, there 900 identifiers.

i've tried changing can think of, , results can't explain.

for example:

1. the query works 1 list of stocks, not list of same length in terms of number of string , total length

2. it works 1 list not same list in reverse order

3. with 1 list, works if there 900 identifiers not if there 899 or 901, , can include or exclude different identifiers , same results, isn't funky 1 of identifiers.

in each of these cases, captured query string being passed program , copied sql server management studio, , in every case, query runs in 1 second.

i have read can on , other forums queries work in sql server management studio time out when run program, seems different in can find cases fails , similar cases doesn't work.

i appreciate suggestions might see might 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>();              (int = 0; < 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 query. in version, include 65 stocks , doesn't work (<=64 work).

select     distinct a.cusip ,   d.value_ / f.cumadjfactor split_adj_val  qai.prc.prcscchg  join qai.dbo.secmapx b     on a.code = b.vencode     , b.ventype = 1     , b.exchange = 1     , b.rank = (select min(rank) qai.dbo.secmapx vencode = a.code , ventype = 1 , exchange = 1)  join qai.dbo.secmapx b2     on b2.seccode = b.seccode     , b2.ventype = 40     , b2.exchange = 1     , b2.rank = (select min(rank) qai.dbo.secmapx seccode = b.seccode , ventype = 40 , exchange = 1)  join qai.dbo.secmapx b3     on b3.seccode = b.seccode     , b3.ventype = 33     , b3.exchange = 1     , b3.rank = (select min(rank) qai.dbo.secmapx seccode = b.seccode , ventype = 33 , exchange = 1)  join qai.dbo.dxlsecinfo c     on b2.vencode = c.code  join qai.dbo.dxlamdata d     on c.code = d.code     , d.date_ = @date     , d.item = 6  left join qai.dbo.ds2adj f      on f.infocode = b3.vencode     , f.adjtype = 2     , f.adjdate <= @date     , ( f.endadjdate >= @date or f.endadjdate null )       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 at, in order of preference:

1. avoid using addwithvalue() function, can have catastrophic performance implications when ado.net guesses column type wrong. must able set explicit db type each parameter
2. look option recompile.
3. look optimize unknown. after others have failed.