I'm writing a program for a company that will generate a daily report for them. All of the data that they use for this report is stored in a local SQLite database. For this report, the utilize pretty much every bit of the information in the database. So currently, when I query the datbase, I retrieve everything, and store the information in lists. Here's what I've got:
using (var dataReader = _connection.Select(query))
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
_date.Add(Convert.ToDateTime(dataReader["date"]));
_measured.Add(Convert.ToDouble(dataReader["measured_dist"]));
_bit.Add(Convert.ToDouble(dataReader["bit_loc"]));
_psi.Add(Convert.ToDouble(dataReader["pump_press"]));
_time.Add(Convert.ToDateTime(dataReader["timestamp"]));
_fob.Add(Convert.ToDouble(dataReader["force_on_bit"]));
_torque.Add(Convert.ToDouble(dataReader["torque"]));
_rpm.Add(Convert.ToDouble(dataReader["rpm"]));
_pumpOneSpm.Add(Convert.ToDouble(dataReader["pump_1_strokes_pm"]));
_pumpTwoSpm.Add(Convert.ToDouble(dataReader["pump_2_strokes_pm"]));
_pullForce.Add(Convert.ToDouble(dataReader["pull_force"]));
_gpm.Add(Convert.ToDouble(dataReader["flow"]));
}
}
}
I then utilize these lists for the calculations. Obviously, the more information that is in this database, the longer the initial query will take. I'm curious if there is a way to increase the performance of the query at all? Thanks for any and all help.
EDIT
One of the report rows is called Daily Drilling Hours. For this calculation, I use this method:
// Retrieves the timestamps where measured depth == bit depth and PSI >= 50
public double CalculateDailyProjectDrillingHours(DateTime date)
{
var dailyTimeStamps = _time.Where((t, i) => _date[i].Equals(date) &&
_measured[i].Equals(_bit[i]) &&
_psi[i] >= 50).ToList();
return
_dailyDrillingHours =
Convert.ToDouble(Math.Round(TimeCalculations(dailyTimeStamps).TotalHours, 2, MidpointRounding.AwayFromZero));
}
// Checks that the interval is less than 10, then adds the interval to the total time
private static TimeSpan TimeCalculations(IList<DateTime> timeStamps)
{
var interval = new TimeSpan(0, 0, 10);
var totalTime = new TimeSpan();
TimeSpan timeDifference;
for (var j = 0; j < timeStamps.Count - 1; j++)
{
if (timeStamps[j + 1].Subtract(timeStamps[j]) <= interval)
{
timeDifference = timeStamps[j + 1].Subtract(timeStamps[j]);
totalTime = totalTime.Add(timeDifference);
}
}
return totalTime;
}
