CODEWITHSUNDEEP
c#.netparallel-processingquery-performance
gene
gene

Reputation: 2108

How to increase data retrieval?

I have a query that takes long to execute and eventually times out.

My task is just to get all the data from the table for particular dates.

However, the database table does not have indexes on the date column and query takes long time to execute and times out.

This is piece of code I have:

DateTime dateTo = Convert.ToDateTime(data.DateTo);
DateTime dateFrom = Convert.ToDateTime(data.DateFrom);
command.CommandText = "select * from errorlog where errortime between @dateFrom and @dateTo";
command.Parameters.AddWithValue("@dateTo", dateTo);
command.Parameters.AddWithValue("@dateFrom", dateFrom);
da.SelectCommand = command;
da.Fill(ds);

Is there any way to rewrite the logic to improve the performance?

Upvotes: 1

Views: 81

Answers (2)

Patrick Hofman
Patrick Hofman

Reputation: 156918

Talk to your DBA. Let him create the index. All other things you are going to 'fix' because of this is just a waste of your time and the time of your users.

Upvotes: 3

displayName
displayName

Reputation: 14359

Since the query timeout happens because of slow database, few solutions are:

  1. Increase the timeout value;
  2. Execute the query in the database first as usually they cache the results. This will warm up the data for you, however, I totally understand that, it may not be always possible;
  3. Run query in loop going over the entire date range in chunks and then consolidate your result at your application's end;
  4. Get only the required columns instead of everything.

Upvotes: 0

Related Questions