Increase application performance

Posted by Prayos on Programmers See other posts from Programmers or by Prayos
Published on 2012-10-23T19:38:41Z Indexed on 2012/10/23 23:18 UTC
Read the original article Hit count: 323

Filed under:
|
|

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;
}

© Programmers or respective owner

Related posts about c#

Related posts about database