1
\$\begingroup\$

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.

\$\endgroup\$

migrated from stackoverflow.com Jan 11 '16 at 20:07

This question came from our site for professional and enthusiast programmers.

  • \$\begingroup\$ Not code, but still a potential improvement: Is the W: drive a local drive on your machine, or a mapped network drive? If it's a network drive, you'll likely get a lot of benefit from copying it locally, especially on an SSD. \$\endgroup\$ – Avner Shahar-Kashtan Jan 11 '16 at 21:11
  • \$\begingroup\$ Also, the best way to find places to improve performance is to measure. What takes the most time? Try executing the SELECT command directly, and passing it to the Fill method, see if there's a measurable difference. That way you can see if your problem is the DB access or the mapping to a DataTable. \$\endgroup\$ – Avner Shahar-Kashtan Jan 11 '16 at 21:14
  • \$\begingroup\$ Do all tables have a primary key? ...even then, I'd first-off question the requirement to load up everything in memory in the first place. Seems doing that is begging the rest of the code to do programmatically work that really should be done by the database server (e.g. compute aggregates, filter data, etc.). \$\endgroup\$ – Mathieu Guindon Nov 7 '16 at 22:14
2
\$\begingroup\$

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.
\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.