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;
}
8
If I understand what you’re doing correctly, then you’re actually copying the whole database into memory and querying it using Linq. This is extremely wasteful since you now have the data both in memory and in the database itself. What you need to do it to query the database itself for the data you need, which is what a database was designed for. For that you’ll need to learn SQL. For instance, look at this Linq query:
var dailyTimeStamps = _time.Where((t, i) => _date[i].Equals(date) &&
_measured[i].Equals(_bit[i]) &&
_psi[i] >= 50).ToList();
You’re pulling a list of all timestamps just to filter it to a few. However, using a SQL query like this:
SELECT timestamp
FROM table
WHERE date = @date
AND measured_dist = bit_loc
AND pump_press >= 50
This will immidiately give you a list of the timestamps you need, without having to load the whole data in memory.
4