SQLitening Support Forum

I need to move records from old Cheetah database to the new SQL db.    I read the Cheetah database and put the records in an array. One array for each table in the SQL-db.    I then write the records to the various Tables.  But the process seems much slower than I am expecting.   The routine is adding  record at the rate of 12 records per second.     I need to add 200,000 to a million records.   At this rate this would be a many hour process.     I must be doing something wrong.   Can someone suggest how I might change this code to make it faster.

Note:  I have notice that SQLite is creating a "journal" file during the add process.  I am thinking that maybe this is why it is so slow.    I have tried adding records one at a time and as a group.  Adding them as a group is about 20% faster.     Here's  the code:


Sub WriteRecsToSQLdb ()

      Local sRec As String
      Local a  As Long
      Local b  As Long
      Local c  As Long       
      Local rc As Long 
      Local slBuildRecs As String
     
      b = UBound(gDrRecs()) : c = 0
      For a = 1 To b
         sRec = gDrRecs(a)
         If Len(sRec) > 10 Then
            'rc = slExe (slBuildInsertOrUpdate("Doctors", sRec))
            If c = 0 Then
               slBuildRecs = slBuildInsertOrUpdate("Doctors", sRec)
            Else   
               slBuildRecs & = " ; " & slBuildInsertOrUpdate("Doctors", sRec)
            End If   
            c += 1
         End If
      Next
      rc = slExe (slBuildRecs, "E0")
      FF_ProgressBar_SetPosition( HWND_FORM1_PROGRESSBAR1, 1 )
      FF_DoEvents()

      b = UBound(gRxRecs()) : c = 0
      For a = b To 1 Step -1
         sRec = gRxRecs(a)
         If Len(sRec) > 10 Then
            'rc = slExe ( slBuildInsertOrUpdate("RxRecs", sRec))
            If c = 0 Then
               slBuildRecs = slBuildInsertOrUpdate("RxRecs", sRec)
            Else   
               slBuildRecs & = " ; " & slBuildInsertOrUpdate("RxRecs", sRec)
            End If   
            c += 1
         End If   
      Next
      rc = slExe (slBuildRecs, "E0")     
      FF_ProgressBar_SetPosition( HWND_FORM1_PROGRESSBAR1, 2 )
      FF_DoEvents()
     
      b = UBound(gDgRecs()) : c = 0
      For a = 1 To b
         sRec = gDgRecs(a)
         If Len(sRec) > 10 Then
            'rc = slExe (slBuildInsertOrUpdate("Drugs", sRec))
            If c = 0 Then
               slBuildRecs = slBuildInsertOrUpdate("Drugs", sRec)
            Else   
               slBuildRecs & = " ; " & slBuildInsertOrUpdate("Drugs", sRec)
            End If   
            c += 1
         End If
      Next   
      rc = slExe (slBuildRecs, "E0")
      FF_ProgressBar_SetPosition( HWND_FORM1_PROGRESSBAR1, 3 )
      FF_DoEvents()
     
      b = UBound(gRfRecs()) : c = 0
      For a = b To 1 Step -1
         sRec = gRfRecs(a) 
         If Len(sRec) > 10 Then
           'rc = slExe (slBuildInsertOrUpdate("RfRecs", sRec))
            If c = 0 Then
               slBuildRecs = slBuildInsertOrUpdate("RfRecs", sRec)
            Else   
               slBuildRecs & = " ; " & slBuildInsertOrUpdate("RfRecs", sRec)
            End If   
            c += 1
         End If
      Next
      rc = slExe (slBuildRecs, "E0")
      FF_ProgressBar_SetPosition( HWND_FORM1_PROGRESSBAR1, 4 )
      FF_DoEvents()
     
      b = UBound(gPtRecs()) : c = 0
      For a = 1 To b
         sRec = gPtRecs(a)
         If Len(sRec) > 10 Then
           ' rc = slExe (slBuildInsertOrUpdate("Patients", sRec))
            If c = 0 Then
               slBuildRecs = slBuildInsertOrUpdate("Patients", sRec)
            Else   
               slBuildRecs & = " ; " & slBuildInsertOrUpdate("Patients", sRec)
            End If   
            c += 1
         End If
      Next   
      rc = slExe (slBuildRecs, "E0")
      FF_ProgressBar_SetPosition( HWND_FORM1_PROGRESSBAR1, 5 )
      FF_DoEvents()
     
      b = UBound(gInsRecs()) : c = 0
      For a = 1 To b
         sRec = gInsRecs(a)
         If Len(sRec) > 10 Then
           ' rc = slExe (slBuildInsertOrUpdate("Insurances", sRec))
            If c = 0 Then
               slBuildRecs = slBuildInsertOrUpdate("Insurances", sRec)
            Else   
               slBuildRecs & = " ; " & slBuildInsertOrUpdate("Insurances", sRec)
            End If   
            c += 1
         End If
      Next
      rc = slExe (slBuildRecs, "E0")   
      FF_ProgressBar_SetPosition( HWND_FORM1_PROGRESSBAR1, 6 )
      FF_DoEvents()
     
      b = UBound(gPrcRecs()) : c = 0
      For a = 1 To b
         sRec = gPrcRecs(a)
         If Len(sRec) > 10 Then
           ' rc = slExe (slBuildInsertOrUpdate("PriceTbls", sRec))
            If c = 0 Then
               slBuildRecs = slBuildInsertOrUpdate("PriceTbls", sRec)
            Else   
               slBuildRecs & = " ; " & slBuildInsertOrUpdate("PriceTbls", sRec)
            End If   
            c += 1
         End If
      Next
      rc = slExe (slBuildRecs, "E0")   
      FF_ProgressBar_SetPosition( HWND_FORM1_PROGRESSBAR1, 7 )
      FF_DoEvents()
     
      b = UBound(gSgRecs()) : c = 0
      For a = 1 To b
         sRec = gSgRecs(a)
         If Len(sRec) > 10 Then
           ' rc = slExe (slBuildInsertOrUpdate("SigRecs", sRec),"E")
            If c = 0 Then
               slBuildRecs = slBuildInsertOrUpdate("SigRecs", sRec)
            Else   
               slBuildRecs & = " ; " & slBuildInsertOrUpdate("SigRecs", sRec)
            End If   
            c += 1
         End If
      Next
      rc = slExe (slBuildRecs, "E0")
     
       
      FF_ProgressBar_SetPosition( HWND_FORM1_PROGRESSBAR1, 8 )
      FF_DoEvents()
     
End Sub

[/code

I would build a large string of UPDATE sql commands (each separated by a semicolon). Probably consisting of say 5000 or 10000 records depending on the size of each record. I would then send the string to SQLite via an slExe call but ensure that I wrap it in a transaction:

slExe "BEGIN IMMEDIATE;" & sTheLargeString & ";END;"

To build the large string, I would use super fast code like FF_FastConcat found in the FireFly Functions Library.

Paul,
   That's what the code above does. Except I didn't have the "BEGIN IMMEDIATE;"  and the  ";END;"

   I will give the  "BEGIN IMMEDIATE;"  and the  ";END;" a try and see if that makes a difference.