title: SqlCommand slow when executing a query with parameters
body: <p>DbDataAdapter.Fill() is extremly slow when performing parameters!</p>
  
  <p>I have a query with 2 parameters inside, and when I put those parameters hardcoded in the query it takes 1 second to execute (in a 470k table rows, returning only 20 rows).</p>
  
  <p>I found many posts similars here and I tried all those solutions (set arithabort, option recompile, option optimize for, ...) with no luck.</p>
  
  <p>I just perform a query (sql server 2008) and not a stored procedure, so the query with arithabort is like this:</p>
  
  <blockquote>
  <pre><code>    string strSql = @"set ARITHABORT ON;
                               select  TOP 20 ....
  </code></pre>
  </blockquote>
  
  <p>Also I tried to call set arithabort in the same transaction but performing that query first..</p>
  
  <p>I don't know if I'm doing something wrong, but the sensation is the ado.net is performing a very bad execution plan in ado.net when I have defined parameters on it.</p>
  
  <p>As a result of this bad choice, the execution time in SSMS is 1 second (after being cached) but in asp is like 9 seconds!</p>
  
  <p>The query is something like this:</p>
  
  <blockquote>
    <p>strSQL @="
    select *<br>
    from Table1 
    where Name like @name";</p>
  </blockquote>
  
  <p>And then: </p>
  
  <blockquote>
  <pre><code>        DbProviderFactory factory = DbProviderFactories.GetFactory(mProvider);
          DbCommand dbcmd = factory.CreateCommand();
          if (CommandTimeout != null)
              dbcmd.CommandTimeout = CommandTimeout.Value;
          if(this.transaccion != null)
              dbcmd.Transaction = this.transaccion;
          dbcmd.Connection = dbc;
          dbcmd.CommandText = strSQL;
          if (parametros != null)
              dbcmd.Parameters.AddRange(parametros);
          DbDataAdapter dbda = factory.CreateDataAdapter();
          dbda.SelectCommand = dbcmd;
          DataTable dt = new DataTable();
          dbda.Fill(dt);
          return dt;
  </code></pre>
  </blockquote>
  
  <p><strong>EDIT 14/01/2013 (18:44)</strong></p>
  
  <p>I'm not longer retrieve the connection from DbProviderFactory, insted I'm using directly SqlConnection and SqlCommand. I know DbCommand and DbProvider are a base clase... but I think there is something more in there.. because the performance drasticaly increase like 300%! </p>
  
  <p>It's not the fill method, because I already tried in the code shown before..</p>
  
  <p>Anyway, I don't know the reason why but using a SqlConnection is much faster! Any idea? Maybe isn't making that bad execution plan made before?</p>
  
  <blockquote>
  <pre><code>       SqlCommand objCmd = new SqlCommand(strSQL, sqlConn);
  
          if (CommandTimeout != null)
              objCmd.CommandTimeout = CommandTimeout.Value;
          if (this.transaccion != null)
              objCmd.Transaction = SQLtransaccion;
  
          if (parametros != null)
              objCmd.Parameters.AddRange(parametros);
  
          DbDataReader dbReader = objCmd.ExecuteReader();
          DataTable dt = new DataTable();
          dt.Load(dbReader);
          dbReader.Close();
          return dt;
  </code></pre>
  </blockquote>
  
  <p>Any help will be greatly  appreciated,</p>
  
  <p>Thanks,</p>