Looking for quicker method of adding hundreds of records to table

General
1

Hello. I’m looking for a quicker/better method of adding many records to a new table, or a preferred approach to the end result - see below for scenario.

With the code below, the app is suspended until the load is complete, and this test process took just over one minute when loading a sample data set of just over 200 records.

[code] dim sql as String = “SELECT * FROM RDexam WHERE DomainNumber = ‘Domain 1’” 'this is the original table with test data
dim rs as RecordSet = dbExam.SQLSelect(sql)

while not rs.EOF

'saves data into table
Dim dr as new DatabaseRecord

dr.Column("RefID") = rs.Field("ID").StringValue

dbDom.InsertRecord "DomOne", dr  'this is the table where will be adding the reference ID numbers
dbDom.Commit
rs.MoveNext

'showing only Domain 1 and DomOne for simplicity sake, but the actual exam table has 4 domains and almost 1,000 questions in the original test table
wend[/code]

Is there a quicker/preferred/more direct method of accomplishing this without the app being suspended until the recordset loops all the way through?

I’ll try to sum up what I am trying to accomplish, and if anyone has a better approach to the end result, I am all ears.

I have a practice exam application that includes a random test feature.

  • The user chooses the random test
  • The current method chooses the ID numbers in a random order by selecting all IDs in the exam table, adding to an array, and shuffling the set so there are no repeating IDs
  • The table contains over 800 questions and asks the student up to 145 of these questions in a random test

With this current process, I’ve had some customers write to me saying when they take the random test multiple times, they are seeing the same questions more than once. Well of course we would expect this since each time they enter the random test, the method shuffles the db, so essentially, the user can see a question on multiple occasions.

During my brainstorming session last night while trying to fall asleep, I thought maybe what I can do is:

  • When the user clicks the random test button, the app would first add all the question ID numbers to a separate table (if this was the first time they clicked it), hence the code above
  • These question IDs would be added into an array, shuffled, and the questions would appear in a random order
  • When the user answers a question in the random test, the question ID would be removed from the DomOne table, so the list would get shorter
  • Next time the user enters the random test, they would be asked only from the remaining questions IDs in the DomOne table
  • This would continue until all the questions have been viewed, at which point, the app would recognize this and then add the IDs to the DomOne table again, and the process would repeat

So I think this could work, although may not be a best practice. Plus, the initial load into the DomOne table take a long time, and I don’t want the app suspended for the user if this is at all possible.

If anyone could help with a quicker method of adding a large number of records to a table or if you can think of a better way of accomplishing the end result, please advise :slight_smile:

Ryan

2

Dont commit on EVERY record
Commit maybe every 100 or 1000 or so
Should make a huge difference

3

use a db.sqlexecute(“insert into …”)
and put 500 lines of insert in the string you pass to sqlexecute
that’s the fastest way I’ve found to do this, on a local or even on a remote database.

4

[quote=282245:@Jean-Yves Pochez]use a db.sqlexecute(“insert into …”)
and put 500 lines of insert in the string you pass to sqlexecute
that’s the fastest way I’ve found to do this, on a local or even on a remote database.[/quote]
… and an extra speed kick may be attained by writing your strings into a string array and joining its contents before you do the execute. If you have a lot of records to insert, it can be an enormous speed gain compared to joining the string in a loop for each segment.

5

Maybe you can do that entirely in SQL. Something like this as an example:

INSERT INTO existingTargetTable SELECT *, random() AS SortOrder FROM sourceTable ORDER BY SortOrder or CREATE TABLE newTargetTable AS SELECT *, random() AS SortOrder FROM sourceTable ORDER BY SortOrder
This would be for SQLite. Note I haven’t tested it.

6

Thanks for the replies all. I tried with the above using databaserecord as well as using INSERT INTO (below) and doing the commit at the end. All is being saved to the new table but still taking over one minute to execute. Was this correct by just doing commit at the end after the while not… wend?

[code] while not rs.EOF

'saves data into table
dbDom.SQLExecute "INSERT INTO DomOne ('RefID') VALUES ('" + rs.Field("ID").StringValue + "');"
rs.MoveNext

wend
dbDom.Commit[/code]

7

Did you start a transaction before you begin adding and data? With SQLite:

dbDom.SQLExecute("BEGIN TRANSACTION")
8

Holy moly! Ok Paul, that made the world of difference!

I’ve never used BEGIN TRANSACTION. Is this necessary in any and all db executions?

9

Theres only 800 questions.
Why not just keep them in an array in memory?

As each question is asked, mark it or move it to a different array.
If you need to save between sessions, you can save as a text file, xml file, or into a database if you like.

10

Thanks for the suggestion Jeff! I may consider this if I find my current plan doesn’t pan out too well

11

Not necessarily. There’s more information in the User Guide:

12

No, but it really can speed up working with your database if you are doing a lot of inserts and updates. Begin Transaction will delay the writing of the data which is the slow part.

You really should check for an error after every insert, update, or SQLExecute statement. And if there is an error then do a Rollback. Failing to do a commit/rollback after a Begin Transaction will cause an error the next time you try to begin another transaction.

13

Got it. Thank you Paul and Bob!