Optimizing SQLite SELECT Performance

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

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

If I open my Database with a Program like SQlite Browser, the tables are being read within a second..

Answer 1

Improving the performance depends on where exactly the bottleneck is - with a database it's typically either IO or CPU. A simple way to see if IO is the main problem is to load the entire database into memory and then selecting from it. However once you have loaded the database into memory you might just want to consider loading your data on demand when you need to display it rather than pulling it into a DataTable.

Once you have the database in-memory you could also try a Parallel.ForEach to load the data from all tables in parallel. Of course you can try this without loading the data into in-memory. I'm not sure how well this works with a physical file but it's worth a shot.

A few additional remarks:

1. Nitpick: It's a database and not a data base hence your variable names should read Database and not DataBase.

2. The general C# naming convention for method parameters and local variables is camelCase - Percentage and ProgCtl both violate this.

3. Don't abbreviate variable and parameter names needlessly, e.g. ProgCtl could be easily progressController - much more readable.

4. For more complex string formatting I'd prefer string.Format over concatenating manually:

   ProgCtrl.SetMessage(string.Format("Loading Data\nCurrent Table ({0} of {1}): {2} ...", 
                                      currentTable, tables.Rows.Count, tablename));
   

   Makes the structure of the resulting string much clearer.

5. SQLiteConnection, SQLiteDataAdapter and SQLiteCommand are all IDisposable and should be wrapped into using statements to make sure they get cleaned up properly.

6. DatabasePath and DatabaseName should be const.

7. Use Path.Combine to combine paths:

   string dbpath = Path.Combine(DataBasePath, DataBaseName + ".sqlite");