SqlCommand slow when executing a query with parameters

Question

DbDataAdapter.Fill() is extremly slow when performing parameters!

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

I found many posts similars here and I tried all those solutions (set arithabort, option recompile, option optimize for, ...) with no luck.

I just perform a query (sql server 2008) and not a stored procedure, so the query with arithabort is like this:

    string strSql = @"set ARITHABORT ON;
                             select  TOP 20 ....

Also I tried to call set arithabort in the same transaction but performing that query first..

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.

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!

The query is something like this:

strSQL @=" select *
from Table1 where Name like @name";

And then:

        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;

EDIT 14/01/2013 (18:44)

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%!

It's not the fill method, because I already tried in the code shown before..

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?

       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;

Any help will be greatly appreciated,

Thanks,

It's probably the difference between ODBC (which is notoriously slow) and the custom built SQL Server assembly (which has all kinds of optimizations in it). In any case, everything after the "EDIT" part is actually an answer. You should move it into an answer and then accept it for the benefit of future users (who may skip your question because it has no accepted answer). — JDB still remembers Monica

anguila anguila · Accepted Answer · 2013-01-15T01:10:06

I found the solution!

It was parameters!

I was using a wrong type in the the List!

Parametross.Add(bd.MakeParameter("@val", "%" + txtFind.Text + "%", DbType.String));

DbType.String vs. DbType.AnsiString

Although both DbType.String and DbType.AnsiString deal with character data, these datatypes are processed differently, and using the wrong data type can have a negative effect on the application’s performance. DbType.String identifies the parameter as a 2-byte Unicode value and is sent to the server as such.DbType.AnsiString causes the parameter to be sent as a multibyte character string. To avoid excessive string conversions, use:

DbType.AnsiString for char or varchar columns and parameters.
DbType.String for unichar and univarchar columns and parameters.

Source: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc20066.0115/html/adonet/adonet49.htm

In my query there is a:

.... where Table.Col1 like @val

But the column type was varchar and I should use DbType.AnsiString, instead of DbType.String

Parametross.Add(bd.MakeParameter("@val", "%" + txtFind.Text + "%", DbType.AnsiString));

In my huge table I was making a lot of unnecesary casts and this is the reason why the performance drastically fall down!

Hope this will help someone,

SqlCommand slow when executing a query with parameters

1 Answers