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

5 Mar 2007 12:13 PM

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

5 Mar 2007 12:38 PM

Miha Markic [MVP C#]

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

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"Jonas Knaus" <j**@hotmail.com> wrote in message
news:%23fVsX$xXHHA.2316@TK2MSFTNGP04.phx.gbl...
> 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
>