lundi 4 janvier 2016

SQLite Select Performance - is this any good?

I need to read Databases which contain 44-50 Tables, with around 5 Million entries in Total (~ 100k entries per Table).

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;

}

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.

Aucun commentaire:

Enregistrer un commentaire