Database load times

I am uploading data to a external database, several thousand rows at a time.

I understand XOJO supports a couple different dB schemas… are any of them considered faster than the rest when mass loading information?

Right now, I am using SQLite and it takes a noticeable amount of time to chunk the data into the DB. Would another DB format speed things up?

Thanks,
John

SQLite is exactly that a “Lite” database. while its not a slow-poke… there are other more complex database engines that may provide more “horse-power” based on your situation, however… “several thousand” is an insignificant amount to be honest…

The speed could be your method, your network, your machine or any combination, and I’m not saying mySQL, Posgres, Oracle or any of a number of others may or may not improve things… but start with the basics

Share your code for how you’re adding data to the SQLite database. There’s right and wrong ways to do things, and the right way is super fast. In one project we load several thousand records from a CSV file and insert the data to SQLite faster than you can blink.

John, are you running on mac, windows or linux? Are you using transactions?

Windows… Will post code shortly.

Appreciate the help!!

Input to the sql table is by pasting the data into a textarea box, then pushing an import button.
The script as I understand it (I had help writing it) – skips the first 31 rows, then writes in 20 columns of data until it sees the end of line indicator, then goes to the next row and starts over. It continues until it finds the end of file.

Here’s the code on the import button:

dim Rows() as string = split(ReplaceLineEndings(myTextArea.text, EndOfLine), endofline)
for i as integer = 31 to ubound(Rows)
dim Columns() as string = split(Rows(i), chr(9))
  
  If AddMatchRow(Columns(0), Columns(1), Columns(2), Columns(3), Columns(4), Columns(5), Columns(6), Columns(7), Columns(8), Columns(9), Columns(10), Columns(11), Columns(12), Columns(13), Columns(14), Columns(15), Columns(16), Columns(17), Columns(18), Columns(19)) Then
    
    AddDataStatusLabel.Text = "Congrats. Your data has been added."
  Else
    AddDataStatusLabel.Text = "Error."
  End If
next

Here is the method called AddMatchRow:

Dim row As New DatabaseRecord
row.Column("Host")= HostInput.Text
row.Column("Kit_Nbr") = kit_nbr
row.Column("Type") = type
row.Column("List") = list
row.Column("Select1") = select1
row.Column("Sex") = sex
row.Column("GED_Wikitree") = GED_Wikitree
row.Column("Halogroup_Mt ") = Halogroup_Mt
row.Column("Halogroup_Y") = Halogroup_Y
row.Column("A_Details") = A_Details
row.Column("B_Details") = B_Details
row.Column("A_TotalCM") = A_TotalCM
row.Column("A_LargestCM") = A_LargestCM
row.Column("A_Gen") = A_Gen
row.Column("Z_Details") = Z_Details
row.Column("X_Details") = X_Details
row.Column("X_TotalCM") = X_TotalCM
row.Column("X_LargestCM") = X_LargestCM
row.Column("ZZZ") = ZZZ
row.Column("Name1") = Name1
row.Column("Email") = Email

App.DB.InsertRecord("Matches", row)

If App.DB.Error Then
  AddDataStatusLabel.Text = "DB Error: " + App.DB.ErrorMessage
  Return False
End If
Return True

First off… please use the code tags to make your code posting easier to read

this is [ code ]  open and [ /code ] close ...remove the spaces

Second, I would (personally) convert that all to pure SQL, wrap it in a transaction, and I bet you see a huge increase in speed from that alone. I think you have a boatload of over-head here.

Fixed the code tags. Thanks for the pointer.
Do both parts go to sql, or just the first part?

Last year, when I found and started learning Xojo and SQLite, didn’t find the DatabaseRecord and just started using SQLite commands (following some examples from Xojo and Webinars).

I just read: DatabaseRecord and in the section ‘See Also’ there is no mention of SQLiteDatabase but REALSQLdatabase, there it say please use SQLiteDatabase as a replacement.

I recommend reading about SQLiteDatabase and SQLitePreparedStatement. Learn from the examples that come with Xojo.

SQLitePreparedStatement, with some practice, will make sense and will help you avoid sql injection and other things.

Early this year I was creating a program to download several thousand records and load them to a database. I had the for next loop writing each record (like you are doing) and on mac I didn’t see any mayor change from a few hundred to several thousand. This was not the case with windows, the speed difference was big. Wrapping the for next in a transaction (as Dave mention) solved the speed problem.

Basically, you want to start the transaction with:

DB.SQLExecute("BEGIN TRANSACTION") // maybe app.db.SQLExecute with your code

do the for next,
execute the SQL insert (check Adding Data to a Database here)
check for any error, then if no error:

DB.Commit

if you have an error, you can show the error and to a DB.Rollback.

Here is some information about the performance:

[quote]Performance
If you commit after every database change, you force your database to do a lot of work behind the scenes to make the data permanently available. This is not a big problem when dealing with small amounts of data, but it can really add up when dealing with lots of data.

For example, if you are importing thousands of rows of data into a specific table, committing after each row could cause your import to take several minutes because of all the overhead. Switching to a transaction that only commits at the end (or even every 1000 rows) could result in a tremendous improvement. I’ve seen times drop from several minutes down to several seconds when using a transaction in this manner.

Transactions give you better performance.[/quote]
Hope this is helpful.

Personally… I do all database data transactions with SQL… It provides the flexiblity, scalablity, is not database dependent (ie. the same statements can be used in languages other than Xojo if required)

db.begintransaction
then
around 1000 sql inserts
then
db.commit
and your data will be stored 10 or 100x faster.
this will work for a local database, and be even more fast with a remote database.

db.SQLExecute("BEGIN TRANSACTION")

Is possible to extend it to have db.begintransaction? I’m not sure how to do it/test it yet. Because db.begintransaction will always be db.SQLExecute(“BEGIN TRANSACTION”) it makes sense to just have a ‘begintransaction’ command.

Much like we have db.commit and db.rollback that is the same as:

db.SQLExecute("COMMIT") db.SQLExecute("ROLLBACK")

sub BeginTransaction( extends aDatabase as Database)
aDatabase.SQLExecute("BEGIN TRANSACTION")
// code to handle errors
End Sub

or also

function BeginTransaction( extends aDatabase as Database) as integer
aDatabase.SQLExecute("BEGIN TRANSACTION")
return aDatabase.errorCode
End Sub

I would recommend using the one that returns an error code, not all databases allow transactions within transactions :slight_smile:

There is a long standing Feature Request for this: <https://xojo.com/issue/25257> the issue seems to be that various databases allow to pass a parameter to control the transaction isolation level which may be difficult to map in a generic way.

A lot of times the transaction isolation level can be set using raw sql.