Reading databases of sports data

Question

I need to read databases which contain 44-50 tables, with around 5 million entries in total (~ 100k entries per table).

The data consists of positional tracking data in sports (players, refs and the ball) and match-events (shots, plays, tackles, ...):

Match-events are negligible regarding performance.

Table: PlayerXYZ or Ball
-------------------------------------------
|id (int Primary Key)| x | y | z | timekey | 
--------------------------------------------

Right now, it takes 86 seconds to read the database and assign the content to a DataTable dictionary. That's a "speed" of 57000 entries per second.

private async void ProcessLoadMatch()
{
    var window = Application.Current.MainWindow as MetroWindow;
    var controller = await window.ShowProgressAsync("Please wait...", "Process message", false, new MetroDialogSettings());

    controller.SetTitle("Loading Match-Data...");
    await Task.Run(() => HandleLoadMatch(controller));
    await controller.CloseAsync();


}

static bool HandleLoadMatch(ProgressDialogController ProgCtrl)
{
    string DataBasePath = @"W:\data\sqlite";
    string DataBaseName = "db";
    string dbpath = @DataBasePath + @"\" + @DataBaseName + ".sqlite";

    SQLiteConnection con = new SQLiteConnection("Data Source=" + dbpath + ";Version=3;");
    con.Open();

    DataTable tables = con.GetSchema("Tables");
    double currentTable = 0;
    double Percentage = 0;
    foreach (DataRow row in tables.Rows)
    {
        currentTable++;
        Percentage = (100 / tables.Rows.Count) * currentTable; 
        string tablename = (string)row[2];
        ProgCtrl.SetMessage("Loading Data\nCurrent Table ("+currentTable+" of "+tables.Rows.Count+"): " + tablename + " ...");
        ProgCtrl.SetProgress(Percentage / 100);

        string CmdString = "SELECT * FROM " + tablename;
        SQLiteCommand cmd = new SQLiteCommand(CmdString, con);
        SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
        DataTable MatchDt = new DataTable();
        sda.Fill(MatchDt);

        CurrentDataSet.CurrentMatch.Data.Add(tablename, MatchDt);
    }
    con.Close();
    return true;

}

CurrentDataSet.CurrentMatch.Data:

class CurrentMatch
{
    public static Dictionary<string, DataTable> Data = new Dictionary<string, DataTable>();
}

My system:

• Mac Mini (late 2012)
• i5-3210m clone
• 16GB RAM
• 256GB SSD

Is there any performance potential left in my code? I load different databases on a regular basis, so any significant performance gains would be appreciated.

Answer 1

Just a few thoughts...

• Instead of using data tables and data sets I would try to go with POCO and a data reader. This is definitely more linq friendly. Perhaps even faster to load.
• You could try to load the tables in parallel - I doubt a single table brings the SSD to its limits.
• All SQLite objects need to be disposed.
• Your naming conventions is in great need of improvement - lots of abbreviations and mixed case.
• The return true isn't necessary. The method never returns a false anyway. It might as well be just void.

Answer 2

Your approach is pretty straightforward. Load all records from all tables. It basically means load the whole database. To achieve better performance ask yourselves:

• Does my app really need to load all db tables at the start?
• Does my app really need the whole table records to be loaded at once?

If the answer is No, consider another approach:

• Load data when required. Eliminate some tables at start.
• Restrict records by SQL or better implement some ORM (e.g. PetaPoco) and take only required records.
• Consider caching database records locally (e.g. cache older records which are immutable).

You can also experiment with in-memory database approach.