MySQL saving to and extracting mass amount of data

  1. 2 weeks ago

    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?
    '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
  2. Kevin G

    May 15 Pre-Release Testers, Xojo Pro Gatesheed, England

    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. Kevin G

    May 15 Pre-Release Testers, Xojo Pro Gatesheed, England

    @Ryan H 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

    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.

or Sign Up to reply!