jeudi 5 novembre 2015

How to improve performance of bulk SELECT query using SQLite in .NET?

I have a large (long) table of data stored in SQLite - potentially 5m+ entries. I am using the System.Data.SQLite package to execute my query and read the data into a bespoke in-memory collection structure in the regular ADO.net way.

CODE (F#)

use cnxn = new SQLiteConnection(@"Data Source=C:\Temp\test.db;Version=3;Read Only=True;")
cnxn.Open()

let data = ResizeArray<Data>()

let cmd = new SQLiteCommand(@"SELECT X, Y, Z, AAA FROM Data", cnxn)
let reader = cmd.ExecuteReader()

while reader.Read() do
    let d = {X = reader.GetInt32(0); Y = reader.GetInt32(1); 
                    Z = reader.GetInt32(2); AAA = reader.GetDouble(3)}
    data.Add(d)
cnxn.Close()

Questions

  1. Is System.Data.SQLite the most performant library to be using for the job here? I am only using it because it appears to be the standard

  2. Is their a better way to code this up?

  3. Are there any settings/configurations on the database itself that would help this scenario?

Aucun commentaire:

Enregistrer un commentaire