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..

• Quill: Why not use SQL server, Microsoft's SQL instead?
• Johnbot: SQlite Browser probably only loads a small subset of the data at a time using what is known as pagination. You can do the same using 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.
• ManuKaracho: Quill: Would the performance be any better? @Johnbot: Pagination is indeed a nice idea. Would have to rearrange my data structure but it could be worth it. In General: Is the performance I am experiencing on a normal level?
• Johnbot: I'd say it's expected given that you're not really using the database at all. The point of a database is querying data. You're just pulling all of it once.
• Ben Aaronson: If you do use SQL Server, it's pretty smart about loading things into its cache. You could see differences on the scale of 100fold performance improvement between the first few times you run this, as long as you have enough RAM available. I can't speak for other database technologies but I'd imagine that's common.

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");