Hi all,
This is my first post on this forum. I’m wondering what is the best way to do a bulk insert using prepared statements. If I understand correctly, with API 2.0 it is no longer necessary to set the type of each parameter etc, as this is handled by the db.ExecuteSQL method.
When reading a text file and inserting 104,000 records using the ‘old’ prepared statement method (as in API 1.0), it takes around 3,000 ms to complete. Using db.ExecuteSQL takes 5,000 ms. I assume the latter is slower because the sql statement needs to be recreated each time?
Am I doing something wrong, or should I just stick with prepared statements?
Many thanks for your help!
//Using prepared statement:
Dim ps As SQLitePreparedStatement = db.Prepare("INSERT INTO Countryspecies (CspID, SpID, CountryID) VALUES (?, ?, ?)")
db.ExecuteSQL("BEGIN TRANSACTION")
Dim tis As TextInputStream = TextInputStream.Open(f) // Open as a text stream
While Not tis.EOF
Dim line As String = tis.ReadLine
Dim tab As String = ChrB(9)
Dim CspID, SpID, CountryID as Integer
CspID = val(NthField(line, tab, 1))
SpID = val(NthField(line, tab, 2))
CountryID = val(NthField(line, tab, 3))
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind (0, CspID)
ps.Bind (1, SpID)
ps.Bind (2, CountryID)
ps.SQLExecute()
Wend
Try
db.CommitTransaction
Catch error As DatabaseException
MsgBox("Error: " + error.Message)
db.RollbackTransaction
End Try
tis.Close
//The new method:
sqlstr = "INSERT INTO Countryspecies (CspID, SpID, CountryID) VALUES (?, ?, ?)"
db.ExecuteSQL("BEGIN TRANSACTION")
Dim tis As TextInputStream = TextInputStream.Open(f) // Open as a text stream
While Not tis.EOF
Dim line As String = tis.ReadLine
Dim tab As String = ChrB(9)
Dim CspID, SpID, CountryID as Integer
CspID = val(NthField(line, tab, 1))
SpID = val(NthField(line, tab, 2))
CountryID = val(NthField(line, tab, 3))
Dim values(2) As Variant
values(0) = CspID
values(1) = SpID
values(2) = CountryID
db.ExecuteSQL(sqlstr, values)
Wend
Try
db.CommitTransaction
Catch error As DatabaseException
MsgBox("Error: " + error.Message)
db.RollbackTransaction
End Try
tis.Close