I have written a routine to import some data files into an SQLite db. It is super slow. Takes 4.5 minutes to import 20k records. I am using a transaction, and have also tried using pragmas to turn off synchronous mode and enable in memory journaling. I tried changing the thread yield interval as well. Still slow.
If I comment out the SQLExecute statement the routine returns instantly so I know it’s the DB insert using all the time.
Also, running this in the terminal and importing from the same file takes about 1 second.
I’ve looked online as well and it seems people are getting 10s of thousands of inserts per second on speedy hardware. I am sorely disappointed atm with the performance. I’ve seen some examples online of people getting 4000 inserts per second on smartphones. It seems I’m doing about 90 per second.
I had a bad trigger in the db file I was running the app with. My test file was a clean version without the trigger. The trigger was updating the whole table every time it ran because it was missing the where clause.
It is nice and speedy now that I’ve fixed the trigger.
Depending on the DB this may / may not be practical
If your trigger is VITAL to the import then it might not be.
But it’s not uncommon to drop all triggers & indexes, import the data, then turn the triggers & what not back on
I think the trigger is minimal impact now that I’ve put in the missing where clause. I could do what I need in code, but I wanted to account for the potentiality of other tools dumping data into this DB.
In case anyone is interested, turning off synchronous and putting the journal in memory only reduced the total import time by 10%. Even without those I am getting over 10k inserts per second now which is totally acceptable. I’m importing slightly more than 1M records from 50+ import files in about 95 seconds.
The usual case for this app will not process this many files at once. Typically there will be one or two files and probably no more than 10k records.
Here’s the code… (f is passed in as a parameter to the method along with myDB which is the already connect SQLite db)
dim t as TextInputStream
dim data as string
t = TextInputStream.Open(f)
t.Encoding = encodings.UTF8
data = t.ReadAll
Dim ps As SQLitePreparedStatement = _
myDB.Prepare("INSERT INTO usps_log (facilityzip, operationcode, timestamp, routingcode, imbtrackingcode) "+_
“VALUES (?, ?, ?, ?, ?)”)
ps.BindType(0,ps.SQLITE_TEXT)
ps.BindType(1,ps.SQLITE_TEXT)
ps.BindType(2,ps.SQLITE_TEXT)
ps.BindType(3,ps.SQLITE_TEXT)
ps.BindType(4,ps.SQLITE_TEXT)
dim lines(),fields() as string
lines = split(data,Chr(10))
myDB.SQLExecute(“BEGIN TRANSACTION”)
for each line as string in lines
fields = split(line,",")
if ubound(fields) = 4 then
ps.Bind(0,fields(0))
ps.Bind(1,fields(1))
ps.Bind(2,fields(2))
ps.Bind(3,fields(3))
ps.Bind(4,fields(4))
ps.SQLExecute()
end if
next
myDB.Commit
You might try committing every 5 thousand or so.
Otherwise the transaction size itself gets to be enormous and that in itself can be an issue.
Another option is dont use a transaction at all.
Since this is an “all or nothing” you can know for sure if it worked / didn’t based on a single db insert failure - roughly what the transaction would get you
That is UNLESS you are importing several files and need each FILE to be entirely imported without disturbing prior imports.
Then a temporary table might be useful since you could still import into that and skip the transaction, any failure means drop the temporary table and report the failure.
On successfully importing to the temporary you can insert into the real table in a single “insert into query” which could be in a transaction.
In my testing, upping the cache size nor committing more often made any noticeable difference. After several rounds of tests I never saw more than a few seconds difference and that could have been timing inconsistency on my part since I didn’t implement any code to time the imports.
In any case, it’s plenty fast enough for my needs. Thanks for all the suggestions.
