DataAdapter.Fill very slow with SqlParameters?

[Jonas Knaus]

Hi there

I found a starnage behavior. I have a method 'GetDataFromDB', which i use
internaly in my class to get data from the db. Now if i build the query
using SqlParameters for the date my query takes up to 60 sec vs. 2 sec if I
do not work with the SqlParameters!

What can be the problem? Did anyone of you had the same problems?

SQL-Statement with DateTime-params:
SELECT @TitleID AS TitleID, StationID, COUNT(*) AS BroadcastCount
FROM tPrg WHERE TitleID = @TitleID
AND StartTime >= @DateFrom
AND StartTime < @DateUntil
GROUP BY StationID ORDER BY StationID


SQL-Statement without DateTime-params:
SELECT @TitleID AS TitleID, StationID, COUNT(*) AS BroadcastCount
FROM tPrg WHERE TitleID = @TitleID
AND StartTime >= CONVERT(DATETIME, '2007-01-1 03:00:00', 102)
AND StartTime < CONVERT(DATETIME, '2007-02-28 03:00:00', 102)
GROUP BY StationID ORDER BY StationID

The implementation of the mentioned method:

public DataTable GetDataFromDB(string sqlStatement, string tableName)
{
DataSet ds = new DataSet();
ValidateConnection();
_dbCommand.CommandType = CommandType.Text;
_dbCommand.CommandText = sqlStatement;
DbDataAdapter adap = _provider.CreateDataAdapter();
adap.SelectCommand = _dbCommand;
adap.Fill(ds);
ds.Tables[0].TableName = tableName;
return ds.Tables[0];
}

Any ideas? Thank you very much for your help!
Jonas

 

[David Browne]

Hi there

I found a starnage behavior. I have a method 'GetDataFromDB', which i use
internaly in my class to get data from the db. Now if i build the query
using SqlParameters for the date my query takes up to 60 sec vs. 2 sec if
I
do not work with the SqlParameters!

What can be the problem? Did anyone of you had the same problems?

This is relatively common for date range queries on large tables. When you
use parameters, SQL Server will create a query plan that can be reused for
whatever values of @DateFrom and @DateUntil you pass, and it has no idea how
many days will be contained in that range. Hard-Coding the parameters will
prevent plan caching and provide SQL Server with more statistical
information on how to execute the query.

You can either use the hard-coded version, or (for SQL 2005) use a query
hint to provide representitive parameter values to "OPTIMIZE FOR". See

Forcing Query Plans
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

Query Hint (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms181714.aspx

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

David

 

[Jonas Knaus]

Hi David!

Thank you very much for the fast and good answer!

Jonas