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..
LIMIT
on your selects:SELECT * FROM SomeTable ORDER BY SomeColumn LIMIT 100, 0
(always use an order by). You will have to manage loading and unloading additional rows yourself. \$\endgroup\$