Speeding up query of SQLite

Question

I have a Desktop Application using MSSQL CE. I am using the CE version because I will need the file transfer in a Mobile Device later.

Using MSSQL CE I could transfer data from CSV File to my SDF Databse in 10-15 seconds with 32000 records.

The problem is when I use the SDF database in my mobile device as it is taking about 24 seconds just for a simple SELECT sku,upc,description,price FROM items WHERE upc='111' statement.

I would like to try and test SQLite as it is lighter and maybe faster but during the first phase, transfer the data from CSV to the SQLite Databse, it took very long, in 30 mins the data transfer from the CSV is only 1000+ records.

How should I make my queries faster?

My MSSQL code: `FileReader = New StreamReader(StringFileName)

    Do While FileReader.Peek() >= 0
        TempReaderString = FileReader.ReadLine
        TempArraySplitString = TempReaderString.Split("`")
        UpdateAppSqlCommand.CommandText = "INSERT INTO Items(Sku, Upc, Description, Price) VALUES('" & TempArraySplitString(0) & "','" & TempArraySplitString(1) & "','" & TempArraySplitString(2) & "','" & TempArraySplitString(3) & "')"
        UpdateAppSqlCommand.ExecuteNonQuery()
    Loop
    FileReader.Close()`

My SQLite Code:

FileReader = New StreamReader(StringFileName)

    Do While FileReader.Peek() >= 0
        TempReaderString = FileReader.ReadLine
        TempArraySplitString = TempReaderString.Split("`")
        TempArraySplitString(2) = TempArraySplitString(2).Replace("'", " ")
        TempArraySplitString(2) = TempArraySplitString(2).Replace("""", " ")
        UpdateAppSqlCommand.CommandText = "INSERT INTO Items(Sku, Upc, Description, Price) VALUES('" & TempArraySplitString(0) & "','" & TempArraySplitString(1) & "','" & TempArraySplitString(2) & "','" & TempArraySplitString(3) & "')"
        UpdateAppSqlCommand.ExecuteNonQuery()
    Loop
    FileReader.Close()

The SQLite took maybe 10x slower moving the data from the CSV to the Database.

score 2 · Accepted Answer · edited May 23 '17 at 12:02

Basically copy paste my own answer from here: VB.Net write large amounts of data to SQLite-DB

Executing a lot of inserts one after another is very slow. It will help you tremendously to wrap all the inserts into a transaction.

Using t As SQLiteTransaction = sqlcon.BeginTransaction 'sqlcon being the SQLiteConnection
    Do While FileReader.Peek() >= 0
        'Your other code
        UpdateAppSqlCommand.CommandText = "INSERT INTO Items(Sku, Upc, Description, Price) VALUES('" & TempArraySplitString(0) & "','" & TempArraySplitString(1) & "','" & TempArraySplitString(2) & "','" & TempArraySplitString(3) & "')"
        UpdateAppSqlCommand.ExecuteNonQuery()
    Loop                      
    t.Commit()
End Using

You basically collect all the inserts you want to do and when you are done they are all executed in one large swoosh. This speeds things up a lot.

Here is a tutorial on transactions:

http://www.tutorialspoint.com/sqlite/sqlite_transactions.htm

Another improvement would be to use parameters in your SQLiteCommand and prepare the command. Then just change the parameters for each insert. Like this:

'Before loop
UpdateAppSqlCommand.CommandText = "INSERT INTO Items (Sku, Upc, Description, Price) VALUES(@Sku, @Upc, @Description, @Price)"
UpdateAppSqlCommand.Parameters.Add("@Sku", DbType.Int32)
'Repeat for other parameters with respective type...
UpdateAppSqlCommand.Prepare()


'In loop
UpdateAppSqlCommand.Parameters("@Sku").Value = 12341
'update other parameters...

UpdateAppSqlCommand.ExecuteNonQuery()

A more detailed comparison of insert speeds: Improve INSERT-per-second performance of SQLite?

The `Another Improvement` greatly improves the speed, without it, it was about 10 secs but with the `prepare` it takes about 3-4 secs to complete 32k of rows. — jaa2013, May 11 '15 at 00:15
@Jens why not just `InsertCommand.Parameters.add(New SQLite.SQLiteParameter("@Sku", 12341))` — pnizzle, Sep 23 '16 at 00:54
@Jens Obviously if I use the approach I specified above then I do not need to do a prepare() on the command. Therefore my question is, does specifying the parameter keys and data type and then doing prepare() have a performance difference to just adding the parameters as instances of `SQLiteParameter`? — pnizzle, Sep 23 '16 at 00:59
@pnizzle The main reason is here that in the example the value of the parameter changes very often. So you just add the parameter once to the command first, prepare, and are then free to change its value as often as you like, e.g. Once per insert. If you are just doing it once, performance differences are so tiny to be hard to judge. Why don't you just try it? — Jens, Sep 23 '16 at 07:17
I did, and my approach took 20s less. The total time being 55s. So I simply stuck to my approach — pnizzle, Sep 26 '16 at 00:14

Speeding up query of SQLite

1 Answers1