Best Placement of Transaction Keywords

M

Mahares

Expert
Licensed User
Longtime User
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.

B4X: 
   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
 
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
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
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
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
 
Upvote 0
J

JohnK

Active Member
Licensed User
Longtime User
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)
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
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
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
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.


B4X: 
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
 
Upvote 0
J

JohnK

Active Member
Licensed User
Longtime User
Mahares said:
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?
Click to expand...
Not on Android, only on full scale dB Servers.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Erel, Klaus or NJDude: Could you please address my issue where the SQL transaction placement recommended by Erel has not helped me.
Thanks
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
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.

B4X: 
'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
 
Upvote 0
J

JohnK

Active Member
Licensed User
Longtime User
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?

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

Mahares

Expert
Licensed User
Longtime User
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
 
Upvote 0
J

JohnK

Active Member
Licensed User
Longtime User
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.
B4X: 
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...
 
Upvote 0
You must log in or register to reply here.
Top