I’m reading a single integer value every 150 ms or so from a device and logging it to a SQLite db. If I attempt to do the db insert after each value is returned from the device it slows down everything to a crawl. If I do the logging at the end of the test instead it works fine but I want to see the results in real time. Is there something I can do to speed up the inserts so that my test runs faster? I’ve tried MultiUser mode which didn’t faze it as well as doing the insert in a thread already. Thanks for any advice.
what the schema for the tables involved ?
what indexes are on this table ?
whats you code look like for inserting ?
It’s nothing complicated, just one table with a few columns.
[code]Dim dbFile as FolderItem
Dim db as SQLiteDatabase
db=New SQLiteDatabase
dbFile = getDB
db.DatabaseFile=dbFile
db.encryptionKey = KEncryptionKey
If db.Connect() then
dim rec as DatabaseRecord
rec = New DatabaseRecord
rec.Column(“LogMsg”) = strLog
rec.Column(“LogDate”) = strLogDate
rec.Column(“LogType”) = strLogType
db.InsertRecord(“tLog”,rec)
db.Commit()
else
MsgBox “The database couldn’t be opened.”
QUIT
end if[/code]
create table tLog (LogMsg varchar, LogDate varchar, LogType varchar,"+_
"ID integer NOT NULL PRIMARY KEY)
try using SQL statements directly for starters…
Create a TRANSACTION, collect some number of transmission (10, 100, 1000? up to you)
then issue a commit, and start a new transaction
if you use prepared statements, you can probably bind some of the data (the date perhaps), once instead of every transaction
I’d actually try a prepared statement instead of a database record
http://documentation.xojo.com/index.php/SQLitePreparedStatement
Never thought to try a prepared statement. I’ll definitely give those ideas a try. Thanks.
you may NOT want transactions as they include a certain amount of overhead to manage the transaction state etc
raw inserts are basically automatically committed if you have not started a transaction
Const UseTransaction = false
Dim db As New SQLiteDatabase
Call db.connect
db.sqlexecute " create table tLog (LogMsg varchar, LogDate varchar, LogType varchar, ID integer NOT NULL PRIMARY KEY)"
Dim starttime As Double
Dim endtime As Double
Dim logmsg As String = "logmsg"
Dim logdate As String = "logdate"
Dim logtype As String = "logtype"
Dim i As Integer
Dim tCtr As Integer
Dim ps As SQLitePreparedStatement = db.prepare("insert into tLog (LogMsg, LogDate, LogType, ID) values( ?,?,?,?)")
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_INTEGER)
starttime = Microseconds
For i = 1 To 100000
ps.SQLExecute(logmsg, LogDate, LogType, i)
If db.Error Then
Break
Else
#If UseTransaction
tCtr = tCtr + 1
If tCtr > 600 Then
db.Commit
tCtr = 0
End If
#endif
End If
Next
#If UseTransaction
db.Commit
#EndIf
endtime = Microseconds
Dim elapsed As Double = endtime - starttime
breakI think you are connecting to the database every insert. Why don’t you connect once at application start up and do the insert using straight SQL. There has got to be overhead connecting to the database every insert every 150 ms. You also should check for errors at time of insert. Losing the transactions would help too (commit).
There should be NO problem inserting records this fast. I do something similar and typical write time is in the 10-20 ms range.
[quote=342736:@Norman Palardy][code]
Const UseTransaction = false
Dim db As New SQLiteDatabase
Call db.connect
db.sqlexecute " create table tLog (LogMsg varchar, LogDate varchar, LogType varchar, ID integer NOT NULL PRIMARY KEY)"
Dim starttime As Double
Dim endtime As Double
Dim logmsg As String = “logmsg”
Dim logdate As String = “logdate”
Dim logtype As String = “logtype”
Dim i As Integer
Dim tCtr As Integer
Dim ps As SQLitePreparedStatement = db.prepare(“insert into tLog (LogMsg, LogDate, LogType, ID) values( ?,?,?,?)”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_INTEGER)
starttime = Microseconds
For i = 1 To 100000
ps.SQLExecute(logmsg, LogDate, LogType, i)
If db.Error Then
Break
Else
#If UseTransaction
tCtr = tCtr + 1
If tCtr > 600 Then
db.Commit
tCtr = 0
End If
#endif
End If
Next
#If UseTransaction
db.Commit
#EndIf
endtime = Microseconds
Dim elapsed As Double = endtime - starttime
break
[/code][/quote]
Hey thanks for that. I was working on a prepared statement myself. I’ll have a close look at this example. 
[quote=342737:@Joseph Evert]I think you are connecting to the database every insert. Why don’t you connect once at application start up and do the insert using straight SQL. There has got to be overhead connecting to the database every insert every 150 ms. You also should check for errors at time of insert. Losing the transactions would help too (commit).
There should be NO problem inserting records this fast. I do something similar and typical write time is in the 10-20 ms range.[/quote]
I had a similar thought that I may be creating too many connections. I created one connection at the start of the test and did one commit and close at the end. Didn’t change anything. 
@Joseph Evert I went back and instead of trying to create the connection at the start of each test which I had previously tried I added it as you suggested at app open and it’s working great now! 
@Norman Palardy @Dave S
Edit* I also used the prepared statement NOT (Stored Procedure lol) instead which I am sure has helped a great deal.
Thanks so much guys. Problem solved.
prepared statement
stored procedures are something entirely different and something SQLite does not support
[quote=342741:@Norman Palardy]prepared statement
stored procedures are something entirely different and something SQLite does not support[/quote]
LOL my bad @Norman Palardy that was a total typo. Long day…
MSSQL on the brain!
@Norman Palardy @Dave S
Just out of curiosity if I have a global app property called App.db for SQLiteDatabase and I call this only once on open:
App.db=New SQLiteDatabase
App.db.DatabaseFile=getDB
How will this impact the performance of my app if multiple methods are trying to use App.db at the same time? For example, if there is a timer running that is inserting at 150ms and another timer running running every x ms executing select statements. I’m trying to determine if it would be best to create new connections for single database calls and then close them or just use one global connection. Thanks for any advice.
If you’re using timers, they will share the connection well. They really aren’t doing their work "at the same time ".
Thanks Tim. I know there’s some threading going on, that was probably bad wording there. I was just curious if SQLite would have issues trying to use a connection globally if it’s being used by multiple methods as opposed to creating a new connection and then closing it.
NEVER share one connection across threads
If you want to do this reread what I just said
[quote=342935:@Norman Palardy]NEVER share one connection across threads
If you want to do this reread what I just said[/quote]
Thanks @Norman Palardy I’ll keep that in mind.
So just to clarify, if I’m not creating a new thread but just using the same shared connection in multiple timers and methods without closing it that shouldn’t cause performance issues?