SQLite INSERT commands with multiple While/Wend blocks

Databases
1

Hi all. I wanted to see if someone can take a look at this and advise if I am on the right path or if this can be improved. This is importing the data just fine, so that’s not what I’m looking for help on. It’s the speed of the whole process. This is a desktop project and testing on Mac 10.14 but will be run on Windows too and Xojo 2019 r3.2

The code is down below, but here are some of my comments/questions to see if this is optimized the best:

  1. This is pulling data from a MySQL table and saving into a SQLite table
  2. ActiveStudents is a local SQLite table and contains 45 records
  3. teachMonitor is another local SQLite table that is populated from the data in the MySQL table. There are only 150 records based on the criteria from the SELECT statement for ActiveStudents
  4. StudyModTimeMySQL is the 3rd local SQLite table also populated with data from the MySQL table. There aren’t any records that match the original criteria, so no data to insert
  5. Am I using BeginTransaction and CommitTransaction correctly? Since there are two sets of inserts within one main While/Wend block, do I put just one BeginTransaction before the first While and the CommitTransaction after the last Wend? (no BT/CT inside the main While/Wend block) Or does this not even matter?
  6. This code takes about 11 seconds to complete. That is after going through 45 loops and pulling in a total of 150 records
  7. I understand that having to loop 45 times initially and then again for each of the W/W loops inside is quite excessive, but based on everything I laid out and the code below, is this the best practice? Optimal? Expected? Completely wrong?

Again, the INSERT commands are just fine. I am getting the data as expected. I just copied over everything from this part of the project so I didn’t forget anything, so this looks a bit muddy. It’s the time I want to improve upon. Thanks!

dim sql as String = "SELECT * FROM ActiveStudents"
dim rs as RecordSet = dbUser.SQLSelect(sql)

while not rs.eof
  'tests
  dim sqlTests as string = "SELECT * FROM emailresults WHERE RegCode = '" + rs.Field("RegCode").StringValue + "' AND Email = '" + rs.Field("Email").StringValue + "'"
  dim rsTests as RecordSet = mdb.SQLSelect(sqlTests)
  
  dbUser.BeginTransaction
  
  if rsTests <> Nil then
    while not rsTests.EOF
      dbUser.SQLExecute "INSERT INTO teachMonitor ('RegCode', 'Email', 'FirstName', 'LastName', 'RemoveStudent', 'WhichTest', 'OutOf', 'TotalCorrect'," +_
      " 'D1Corr', 'D2Corr', 'D3Corr', 'D4Corr', 'D5Corr', 'D1Taken', 'D2Taken', 'D3Taken', 'D4Taken', 'D5Taken', 'LastImport', 'YearMonitor', 'Score'," +_
      " 'Domain', 'TestDate', 'TimeRemain', 'Product', 'TestID', 'MonTopicCorr', 'MonTopicIncorr') VALUES ('" + rsTests.Field("RegCode").StringValue + "', '" + rsTests.Field("Email").StringValue + "'," +_
      " '" + rsTests.Field("FirstName").StringValue + "', '" + rsTests.Field("LastName").StringValue + "', ''," +_
      " '" + rsTests.Field("WhichTest").StringValue + "', '" + rsTests.Field("OutOf").StringValue + "', '" + rsTests.Field("TotalCorrect").StringValue + "'," +_
      " '" + rsTests.Field("D1Corr").StringValue + "', '" + rsTests.Field("D2Corr").StringValue + "', '" + rsTests.Field("D3Corr").StringValue + "'," +_
      " '" + rsTests.Field("D4Corr").StringValue + "', '" + rsTests.Field("D5Corr").StringValue + "', '" + rsTests.Field("D1Taken").StringValue + "'," +_
      " '" + rsTests.Field("D2Taken").StringValue + "', '" + rsTests.Field("D3Taken").StringValue + "', '" + rsTests.Field("D4Taken").StringValue + "'," +_
      " '" + rsTests.Field("D5Taken").StringValue + "', '" + strDate + "', '" + rs.Field("YearMonitor").StringValue + "'," +_
      " '" + rsTests.Field("Score").StringValue + "', '" + rsTests.Field("Domain").StringValue + "', '" + rsTests.Field("TestDate").StringValue + "'," +_
      " '" + rsTests.Field("TimeRemain").StringValue + "', '" + rsTests.Field("Product").StringValue + "', '" + rsTests.Field("id").StringValue + "', '" + rsTests.Field("MonTopicCorr").StringValue + "', '" + rsTests.Field("MonTopicIncorr").StringValue + "');"
      
      rsTests.MoveNext
    wend
    dbUser.CommitTransaction
  end if
  
  'time
  sqlTime = "SELECT * FROM StudyModTime WHERE RegCode = '" + rs.Field("RegCode").StringValue + "' AND Email = '" + rs.Field("Email").StringValue + "'"
  dim rsTime as RecordSet = mdb.SQLSelect(sqlTime)
  
  dbUser.BeginTransaction
  
  if rsTime <> Nil then
    while not rsTime.EOF
      dbUser.SQLExecute "INSERT INTO StudyModTimeMySQL ('totalTime', 'Email', 'studyMod', 'RegCode') VALUES ('" + rsTime.Field("TimeSpent").StringValue + "', '" + rsTime.Field("Email").StringValue + "', '" + rsTime.Field("StudyMod").StringValue + "', '" + rsTime.Field("RegCode").StringValue + "');"
      rsTime.MoveNext
    wend
    dbUser.CommitTransaction
  end if
  
  rs.MoveNext
wend
2

You might try to put BeginTransaction at the top of the method, before the first While and then Commit it after the last Wend.

3

Thanks Tim. I’ll give that a try.

If it doesn’t seem to be any better, is it safe to assume that the delay could be because of the many loops and also the loops within the main loop? My test data was pretty extreme, but I also need to be prepared for ActiveStudents that can be 100+, which would increase the number of times the loop is run

4

Unfortunately, this seems to take longer. With my original method, it took about 11 seconds. Putting BeginTransaction before the first While and CommitTransaction after the last Wend took close to 28 seconds :neutral_face:

5

Some suggestion:

  1. You are performing SELECT *. Could this be retrieving columns you don’t need?

  2. Are the columns in your query indexed?

  3. Have you tried preparing statements outside of the loops and binding the parameters?

6

If the OP is going to prepare statements, which could speed things up a bit due to not doing all that string work each time inside each loop, better off using API 2 methods where all the preparing is done for you. Also why not put all the declarations at the top, then those aren’t repeated either. So you might have:

Var  sql as String, rsTime, sqlTime as RowSet

sqlTime = "SELECT * FROM StudyModTime WHERE RegCode = ? and Email = ?"  // Done top of method
sql = "INSERT INTO StudyModTimeMySQL ('totalTime', 'Email', 'studyMod', 'RegCode') VALUES (?, ?, ?, ?)'"

...

rsTime = mdb.SelectSQL (sqlTime, rs.Column("RegCode").StringValue, rs.Column("Email").StringValue)

if  (rsTime<>Nil)  Then
  dbUser.ExecuteSQL ("begin transaction")
  while not rsTime.AfterLastRow
    dbUser.ExecuteSQL (sql, rsTime.Column("TimeSpent").StringValue, rsTime.Column("Email").StringValue, rsTime.Column("StudyMod").StringValue, rsTime.Column("RegCode").StringValue)
    rsTime.MoveToNextRow ()
  wend
  dbUser.ExecuteSQL ("Commit")
End if

Note: If you’re putting the Commit inside the If, you’d better put the Begin there too.

7

Thank you for the replies. I attempted the prepared statement per @TimStreater but unfortunately did not see any major differences in the speed. I have to think this is just the way it will be for a larger list of ActiveStudents and will just have to incorporate a progress bar letting the instructor know it is still working and not stuck in a loop

Unless I misunderstood your code sample Tim, I still do need to have much of your code wrapped into an initial While/Wend loop where I first gather the list of ActiveStudents. This is what my latest code looks like. For practice, I am only working on the Tests. There will be two other tables to include (Time and Data), but the tests is the one in my sample data that has data and is the main one.

Feel free to comment if you see something I am missing/doing wrong

dim sqlTests, sqlTestsInsert, sqlTime, sqlData as String
dim rsTime, rsData as RowSet
dim sql as String = "SELECT * FROM ActiveStudents"
dim rs as RowSet = dbUser.SelectSQL(sql)

'tests
sqlTests = "SELECT * FROM emailresults WHERE RegCode = ? and Email = ?" 
sqlTestsInsert = "INSERT INTO teachMonitor ('RegCode', 'Email', 'FirstName', 'LastName', 'WhichTest', 'OutOf', 'TotalCorrect'," +_
" 'D1Corr', 'D2Corr', 'D3Corr', 'D4Corr', 'D5Corr', 'D1Taken', 'D2Taken', 'D3Taken', 'D4Taken', 'D5Taken', 'LastImport', 'YearMonitor', 'Score'," +_
" 'Domain', 'TestDate', 'TimeRemain', 'Product', 'TestID', 'MonTopicCorr', 'MonTopicIncorr') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

while not rs.AfterLastRow
  
  dim rsTests as RowSet = mdb.SelectSQL(sqlTests, rs.Column("RegCode").StringValue, rs.Column("Email").StringValue)
  
  if (rsTests <> Nil) then
    dbUser.ExecuteSQL("BEGIN TRANSACTION")
    while not rsTests.AfterLastRow
      dbUser.ExecuteSQL(sqlTestsInsert, rsTests.Column("RegCode").StringValue, rsTests.Column("Email").StringValue, rsTests.Column("FirstName").StringValue, _
      rsTests.Column("LastName").StringValue, rsTests.Column("WhichTest").StringValue, rsTests.Column("OutOf").StringValue, rsTests.Column("TotalCorrect").StringValue, _
      rsTests.Column("D1Corr").StringValue, rsTests.Column("D2Corr").StringValue, rsTests.Column("D3Corr").StringValue, rsTests.Column("D4Corr").StringValue, _
      rsTests.Column("D5Corr").StringValue, rsTests.Column("D1Taken").StringValue, rsTests.Column("D2Taken").StringValue, rsTests.Column("D3Taken").StringValue, _
      rsTests.Column("D4Taken").StringValue, rsTests.Column("D5Taken").StringValue, strDate, rs.Column("YearMonitor").StringValue, rsTests.Column("Score").StringValue, _
      rsTests.Column("Domain").StringValue, rsTests.Column("TestDate").StringValue, rsTests.Column("TimeRemain").StringValue, rsTests.Column("Product").StringValue, _
      rsTests.Column("id").StringValue, rsTests.Column("MonTopicCorr").StringValue, rsTests.Column("MonTopicIncorr").StringValue)
      
      rsTests.MoveToNextRow
    wend
    
    dbUser.ExecuteSQL("COMMIT")
  end if
  rs.MoveToNextRow
wend
8

Do you have any indexes on your databases?

You might want to enquire in the SQLite Users’ Forum, too, at:

9

Just tossing in my 2¢.

One option would be to feed the database multiple SQL statements in one execute call.

Another option would be to let the database do all the work and let the INSERT work off a SELECT statement’s returned values (if all the data is already in the database).

10

I do not at the moment but will look into it. Have never worked with indexes, but I’m sure I can lots of info

Your 2 cents are very welcome. I have some other ideas to try (in addition to the indexes) and will consider these

Thank you both for the suggestions