We are working with MSSQL 2005 and ado.net framework 1.1
During our performance tests over large DB (~1M records) I
found out that when I run one of our queries, with embeded values in the query-string, instead of using the CreateParametr method, the query ran 40 times faster!!!The parameters are 2 strings
(~30 characters), the type in database is varchar(255).Wh
at can cause such a huge difference in the execution timeThis is our AddParameter source:
public
void AddParameter(object parameterValue) { string paramName;IDataParameter IParam;
//For string that is empty do nothing means null is entered to field. if(((parameterValue is string) && (parameterValue.ToString()==String.Empty))|| (parameterValue==
null) || (parameterValue is DBNull)) return;IParam = m_command.CreateParameter();
IParam.Value = parameterValue;
if(m_paramMark.Length > 1) {paramName = m_paramMark + m_paramCount.ToString();
IParam.ParameterName= paramName;
m_paramCount++;
}m_command.Parameters.Add(IParam);
}We are using the Interface IDataParameter and not SqlParameter because our code is generic.
Thanks,
Dalila
CreateParameter give bad performance
MisterZimbu
Of course it will look different.
One way will look:
select a,b
from ps_table1
where c = @p1
and d = @p2
and e= @p3
and the other:
select a,b from ps_table1
where c = 'fjsojojflsjflsd'
and d = 'fjlshflks lksj'
and e = 0
The second one is allways faster.
emasstech
JIM.H.
In your code you create parameter that has no type and size defined. It means provider has to resolve each time when it executes the query. It des not know that your parameters are string even though you assign string values to them. You should create some sort of factory that will create provider specific objects, not generic one, but will return them to the application using IDB... interface.
Ajamilan
If your application runs a series of queries that are only different in some constants, you can improve performance by using a parameterized query. For example, to return orders by different customers, you can run the following query:
SELECT "Customer ID" FROM Orders WHERE "Order ID" =
Parameterized queries yield better performance by compiling the query only once and executing the compiled plan multiple times. Programmatically, you must hold on to the command object that contains the cached query plan. Destroying the previous command object and creating a new one destroys the cached plan. This requires the query to be re-compiled. If you must run several parameterized queries in interleaved manner, you can create several command objects, each caching the execution plan for a parameterized query. This way, you effectively avoid re-compilations for all of them.