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.

Answer 1

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?