SqlParameter slower in combination with DataAdabter.Fill method???

[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

 

[Miha Markic [MVP C#]]

That doesn't make sense. How did you measure the times?