MySQL saving to and extracting mass amount of data

General
1

Hi. I’m looking for someone to look over the code below and maybe add some suggestions to improve it. With the following, this is taking data from 3 different tables on a MySQL db and saving the data locally to 3 tables in one SQLite db. I have another similar function in another program that is doing the opposite. This works, but when there is a large amount of data, the program lags a bit, only for 5-20 seconds, depending on the amount of data, and I am afraid this could bother users or the connection could get lost (not sure if that could happen in this short amount of time).

To briefly summarize, this is part of a monitoring program on an instructor’s machine. Her machine has one SQLite db with one table called teachRegCodes, that is nothing more than an ID field and a RegCode field. I am making a recordset of these RegCodes, looping through each one, then 3 tables on MySQL (emailresults, StudyModTime, and StudyModInfo) have separate recordsets filled one by one, and finally, using BEGIN TRANSACTION > Commit, saving this data to 3 tables on her machine. This is all within one connection to MySQL.

Few questions:

  • Am I using the BEGIN TRANSACTION > Commit pieces correctly? There are 3, one for each
  • Is using recordsets the best way to do this?
  • Can/does a MySQL connection time out after a couple of seconds? Is this a potential problem?
  • Should there be more than one MySQL connection? One for each table?
  • Do you see anything wrong that can be improved?

[code]'connect to MySQL
If mDb.Connect Then

dim sql2, sqlTime, sqlData as String
dim d as new date
dim strDate as String = d.SQLDateTime

dim sql as String = “SELECT * FROM teachRegCodes”
dim rs as RecordSet = dbUser.SQLSelect(sql)

while not rs.eof
‘tests
sql2 = “SELECT * FROM emailresults WHERE RegCode = '” + rs.Field(“RegCode”).StringValue + "’"
dim rs2 as RecordSet = mdb.SQLSelect(sql2)

dbUser.SQLExecute("BEGIN TRANSACTION")

while not rs2.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') VALUES ('" + rs2.Field("RegCode").StringValue + "', '" + rs2.Field("Email").StringValue + "'," +_
  " '" + rs2.Field("FirstName").StringValue + "', '" + rs2.Field("LastName").StringValue + "', '" + rs2.Field("RemoveStudent").StringValue + "'," +_
  " '" + rs2.Field("WhichTest").StringValue + "', '" + rs2.Field("OutOf").StringValue + "', '" + rs2.Field("TotalCorrect").StringValue + "'," +_
  " '" + rs2.Field("D1Corr").StringValue + "', '" + rs2.Field("D2Corr").StringValue + "', '" + rs2.Field("D3Corr").StringValue + "'," +_
  " '" + rs2.Field("D4Corr").StringValue + "', '" + rs2.Field("D5Corr").StringValue + "', '" + rs2.Field("D1Taken").StringValue + "'," +_
  " '" + rs2.Field("D2Taken").StringValue + "', '" + rs2.Field("D3Taken").StringValue + "', '" + rs2.Field("D4Taken").StringValue + "'," +_
  " '" + rs2.Field("D5Taken").StringValue + "', '" + strDate + "', '" + str(d.Year) + "'," +_
  " '" + rs2.Field("Score").StringValue + "', '" + rs2.Field("Domain").StringValue + "', '" + rs2.Field("TestDate").StringValue + "'," +_
  " '" + rs2.Field("TimeRemain").StringValue + "', '" + rs2.Field("Product").StringValue + "', '" + rs2.Field("id").StringValue + "');"
  rs2.MoveNext
wend
dbUser.Commit

'time
sqlTime = "SELECT * FROM StudyModTime WHERE RegCode = '" + rs.Field("RegCode").StringValue + "'"
dim rsTime as RecordSet = mdb.SQLSelect(sqlTime)

dbUser.SQLExecute("BEGIN TRANSACTION")

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.Commit

'study data
sqlData = "SELECT * FROM StudyModInfo WHERE RegCode = '" + rs.Field("RegCode").StringValue + "'"
dim rsData as RecordSet = mdb.SQLSelect(sqlData)

dbUser.SQLExecute("BEGIN TRANSACTION")

while not rsData.EOF
  dbUser.SQLExecute "INSERT INTO StudyModInfoMySQL ('studyMod', 'Topic', 'Correct', 'Email', 'RegCode') VALUES ('" + rsData.Field("StudyMod").StringValue + "', '" + rsData.Field("Topic").StringValue + "', '" + rsData.Field("Correct").StringValue + "', '" + rsData.Field("Email").StringValue + "', '" + rsData.Field("RegCode").StringValue + "');"
  rsData.MoveNext
wend
dbUser.Commit

rs.MoveNext

wend
End If[/code]

2

Have you benchmarked your code to see if it is the retrieval of the data or the insertion that is causing the problem?

If the problem is with the insertion of data into SQLite there are a few pragmas that might help:
PRAGMA schema.synchronous
PRAGMA schema.journal_mode
PRAGMA schema.locking_mode

https://www.sqlite.org/pragma.html

3

Thanks Kevin. All of the data is writing and extracting without any problems thus far from what I’ve seen in my testing. The only “issue” is that the process is taking a few seconds to complete. Guess I’m kind of checking to see if this timing is normal with this type of use of MySQL when processing a larger amount of data at one time

4

Yeh, but is it the read from MySQL that is slow or the insert into SQLite? If it is SQLite then you might be able to get some additional performance via the pragmas.

I know MBS has SQL plugins so they might give you better performance than the Xojo native functions.

You could also look at the Xojo pragmas as part of the problem could be the loop itself.