Best Placement of Transaction Keywords

[Mahares]

I have 6 tables, some have thousands or records and some with a hundred records. I delete their records via code, then immediately import new data to them from a text file as shown in the below excerpt for one of the 6. The transactions take place one table after the other. I am trying to maximize the speed of the SQL transactions. I would like to verify the placement of the keywords: begin, successful and end transaction in the code while dealing with a do loop. It works as I ouline, but sometimes it is fast, and other times it is slow. Erel believes using these keywords in large tables is critical.

   Dim line As String      'in globals
   Dim reader As TextReader
   Dim i, j As Int
   Dim DBTableName As String
   
   SQL1.BeginTransaction
   txt="DELETE FROM " & DBTableName
   SQL1.ExecNonQuery(txt)
   j=0  'must reset
   i=0  'must reset
   Do While line <> Null 
     Try
        line = reader.ReadLine
        Dim CC() As String  'CC is column content
        CC = Regex.Split(",", line)  'field separator is comma. Commas separate the data
   
            SQL1.ExecNonQuery2("INSERT INTO " & DBTableName & " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," _
            & "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" _
            , Array As Object (CC(0), CC(1), CC(2), CC(3), CC(4), CC(5), CC(6), CC(7), CC(8), CC(9), _
             CC(10), CC(11), CC(12), CC(13),CC(14),CC(15),CC(16),CC(17), CC(18), CC(19), CC(20), _ 
             CC(21), CC(22), CC(23), CC(24),CC(25),CC(26),CC(27),CC(28), CC(29), CC(30), CC(31), _ 
             CC(32), CC(33), CC(34), CC(35),CC(36),CC(37),CC(38),CC(39), CC(40), CC(41), CC(42))) 
             SQL1.TransactionSuccessful  
             Catch
             i = i+1   
             End Try
            DoEvents      'command needed when you have many records to import
             j = j + 1
       Loop
       reader.Close      
          ProgressDialogHide
          SQL1.EndTransaction

 

[Erel]

Your placement is incorrect. You should start the transaction before the loop and mark / end the transaction after the loop ends. I also recommend you to remove the DoEvents call as it can slow down the operation.
You can issue a DoEvents call every x iterations:

If j Mod 50 = 0 Then DoEvents

 

[Mahares]

Please check my code again. I do start the BEGINTRANSACTION before the loop and ENDTRANSACTION after the loop. That is the way I have it.
Mahares

 

[Erel]

The correct structure is:

SQL1.BeginTransaction
Try
    'do all the SQL statements here.
    'loop should start and end here.

    SQL1.TransactionSuccessful
Catch
    Log(LastException.Message) 'no changes will be made
End Try
SQL1.EndTransaction

 

[Mahares]

Thanks Erel. Since I have 6 tables to insert to in consecutive fashion, I assume I will have to use the same type of SQL transactions layout you show for each table insert. In addtion, for the bigger tables insert I need to include the Doevents after so many records inserts and ignore it for tables with fewer records. Please confirm.
Thanks

 

[Erel]

You should use this block for each set of inserts.

You can call DoEvents. However you do not need to call it each iteration.

 

[JohnK]

Just a thought, for speed, you may find simply dropping the table may be faster than a delete (and re-create it before inserting new data of course)

 

[Mahares]

John, I actually drop the small tables and recreate them. However, for the large tables I have been deleting records and re-inserting. Have you tested very large tables with a few thousands records and several fields and found substantial improvement in speed when you actually drop the table , recreate and re-insert. If you have, any idea of percent increase?
Thanks
Mahares

 

[Mahares]

Erel, I am sorry to report to you that you below recommendation of the placement of the SQL transaction statement in the code is not working for me. I had 6 tables, none of them imported any data, I was getting the message on some of them that X number of records were imported. But when I checked, none of the tables had any records. I reverted back to the way I had my code before I contacted you and all tables get filled properly from the exact same 6 text files I used while testing with your procedure. I do not know what to make of it. With me placing the Catch End Try inside the loop and eliminating the following command: Log(LastException.Message) worked better. If anybody else has a comment or suggestion, I would love to hear it and implement it.

SQL1.BeginTransaction
Try
    'do all the SQL statements here.
    'loop should start and end here.

    SQL1.TransactionSuccessful
Catch
    Log(LastException.Message) 'no changes will be made
End Try
SQL1.EndTransaction

 

[JohnK]

John, I actually drop the small tables and recreate them. However, for the large tables I have been deleting records and re-inserting. Have you tested very large tables with a few thousands records and several fields and found substantial improvement in speed when you actually drop the table , recreate and re-insert. If you have, any idea of percent increase?

Not on Android, only on full scale dB Servers.

 

[Mahares]

Erel, Klaus or NJDude: Could you please address my issue where the SQL transaction placement recommended by Erel has not helped me.
Thanks

 

[Erel]

Please post the code that you tried to run (with my suggestion).

 

[Mahares]

Erel, here is the section of the code I used to import the text file in one of the 6 tables following your advice. No records were imported, possibly because the last column of some records is blank. Maybe Regex does not know what to do. Array out bounds exception error.

'HERE STARTS CONSUMABLES IMPORT
reader.Close   
      Dim reader As TextReader      'read text File
      DBTableName= "tblChemicals"  
      Dim MyConsumablesTextFile As String      :MyConsumablesTextFile=  "Base_Consumables.tab"  'base text file to import in base table
      SQL1.BeginTransaction   
             ConsumablesPrep               'sub to prepare table fields
             ConsumablesCreate   'sub to create table
      reader.Initialize(File.OpenInput(DBFilePath,MyConsumablesTextFile))   
      Dim line As String   
      i=0  'must reset   
      Try
    Do While line <> Null    
      line = reader.ReadLine
      Dim CC() As String  'CC is column content
         CC = Regex.Split(Chr(9), line)      'field separator is Tab
         SQL1.ExecNonQuery2("INSERT INTO " & DBTableName & " VALUES (?,?,?,?)", Array As Object (CC(0), CC(1), CC(2), CC(3)))
'         DoEvents      'command needed when you have many records to import
         i = i + 1
    Loop
      SQL1.TransactionSuccessful
      Catch
         Log(LastException.Message)
         'Msgbox(LastException.Message,"")
     End Try
      SQL1.EndTransaction 
      i=i-1       
     Msgbox(i & " Records in CONSUMABLES text file were imported to ".ToUpperCase & DBTableName, "C O N S U M A B L E S  T E X T  F I L E")
      'HERE ENDS CONSUMABLES IMPORT

 

[JohnK]

In between your declaration, and the start of your loop, "line" does not get a value to enable the loop to start? ie what lets it enter the loop the very first time?

Dim line As String    
i=0  'must reset    
Try
        Do While line <> Null

 

[Erel]

Also check the logs. Are there any error messages?

 

[Mahares]

JohnK: I do not understand the answer to your question. What are you supposed to add to enter the loop. The text file has no heading. Please elaborate.
Thanks

 

[JohnK]

I am not familiar with B4A and its handling of initial values for strings, but assuming they are nulls and assuming that "reader.ReadLine" returns a null at EOF. Try.

Dim line As String    
i=0  'must reset    
Try
    line = reader.ReadLine
    Do While line <> Null     
      Dim CC() As String  'CC is column content
      CC = Regex.Split(Chr(9), line)        'field separator is Tab
      SQL1.ExecNonQuery2("INSERT INTO " & DBTableName & " VALUES (?,?,?,?)", Array As Object (CC(0), CC(1), CC(2), CC(3)))
      DoEvents        'command needed when you have many records to import
      i = i + 1
      line = reader.ReadLine
    Loop
...snip...