DB query performance: slow reader iteration in .net

Question

I have an SQL query like this:

set @fromdate = '2012/01/01 00:00:00'
set @todate = '2013/01/01 00:00:00'

SELECT TableOne.date_time, TableTwo.* 
FROM TableOne 
INNER JOIN TableTwo ON TableOne.gprs_id = TableTwo.recordid 
WHERE date_time >= @fromdate AND date_time <= @todate

I use it in .net 4 (c#) like this:

string strSQL = "SELECT TableOne.date_time, TableTwo.* FROM TableOne INNER JOIN TableTwo ON " +
                "TableOne.gprs_id = TableTwo.recordid where date_time >= @fromdate AND date_time <= @todate";

var pCommand = pConnectionWrapper.DBConnection.CreateCommand();
pCommand.CommandText = strSQL;

var paramFromDate = pCommand.CreateParameter();
paramFromDate.ParameterName = "@fromdate";
paramFromDate.DbType = DbType.DateTime;
paramFromDate.SourceColumn = "date_time";
paramFromDate.Value = fromDate;
pCommand.Parameters.Add(paramFromDate);

var paramToDate = pCommand.CreateParameter();
paramToDate.ParameterName = "@todate";
paramToDate.DbType = DbType.DateTime;
paramToDate.SourceColumn = "date_time";
paramToDate.Value = toDate;
pCommand.Parameters.Add(paramToDate);

var pReader = pCommand.ExecuteReader();

while (pReader.Read())
{
   var someValue = double.Parse(pReader["somevalue"].ToString());
   var date = DateTime.Parse(pReader["date_time"].ToString());

   var someObject = new someObject(someValue, someDate);
   someObjects.Add(comeObject);
}

Running this query is much faster in SQL Server Management Studio than from .net. Iterating through rows is extremely slow from .net. As I think this inner join causes this (?), because my other queries that does not contain joins are really fast under .net.

Can I improve the query performance from .net? Using datasets instead of reader or sg like this?

Thank you very much!

That's clearly not your real code, given the while loop at the end... could you give us your genuine code? What are you doing in the loop? There should be no reason for it to be slow due to a join - it's not like the join executes locally. — Jon Skeet
– Jon Skeet, Commented Jun 21, 2012 at 10:40
You should obtain an execution plan for both cases (running through SQL Server Management Studio & via .Net code) and compare the two. — Justin
– Justin, Commented Jun 21, 2012 at 10:45
I added the code inside reading. It is nothing special just some object initialization based on DB data — Tom
– Tom, Commented Jun 21, 2012 at 10:47

paparazzo · Accepted Answer · 2012-06-21 12:49:52Z

1

This will not really fix the query but will get the data from the query more efficiently. Return only the columns you need by name. Reader should be faster than a dataset.

Double someValue;
DateTime date;
while (pReader.Read())
{
   someValue = pReader.GetDouble(0);
   date = pReader.GetDate(1);

   var someObject = new someObject(someValue, someDate);
   someObjects.Add(comeObject);
}

//or
while (pReader.Read())
{
   someObjects.Add(new someObject(pReader.GetDouble(0), pReader.GetDate(1)));
}

As for the query it seems pretty basic.

Comment out the 2 object lines to see if that is possibly the bottleneck.

answered Jun 21, 2012 at 12:49

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Over a year ago

Thanks, it made it 50% faster!

Stuart Ainsworth · Accepted Answer · 2012-06-21 11:54:42Z

0

Are you using all column names from Table2? If not, then specify ONLY the column names you need, and eliminate some of the overhead of the dataset being returned. Even if you are using all of them, usign named columns is a good habit to get into.

answered Jun 21, 2012 at 11:54

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

1 Comment

Tom Over a year ago

I need them, only 2 tinyint + 1 float column

Collectives™ on Stack Overflow

DB query performance: slow reader iteration in .net

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related