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, Jun 21 '12 at 10:40
You should [obtain an execution plan](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) for both cases (running through SQL Server Management Studio & via .Net code) and compare the two. — Justin, Jun 21 '12 at 10:45
I added the code inside reading. It is nothing special just some object initialization based on DB data — Tom, Jun 21 '12 at 10:47
You are doing a SELECT * on tabletwo - how many columns are being returned, and what datatypes? If you watch the query run in Activity Monitor, are you getting any waits, specifically ASYNC_NETWORK_IO? — Dave Simione, Jun 21 '12 at 11:35
TableTow has two tinyint columns and a float one. And one int as primary key- — Tom, Jun 21 '12 at 11:48

score 1 · Accepted Answer · answered Jun 21 '12 at 12:49

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.

score 0 · Answer 2 · answered Jun 21 '12 at 11:54

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 '12 at 11:54

Stuart Ainsworth

12,792
41
46

I need them, only 2 tinyint + 1 float column – Tom Jun 21 '12 at 11:55

DB query performance: slow reader iteration in .net

2 Answers2