B4J Question Issues With Wait For with JSQL In Debugger

[keirS]

Running in Deug Mode with the Wait For commented out:

#Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: sqlite-jdbc-3.7.2

#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private TableView1 As TableView
    Dim ASUtils As ApacheSU
    Dim SQL1 As SQL
    Dim n As Long = DateTime.Now
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("Main")
    MainForm.SetFormStyle("UTILITY")
    MainForm.Title = "B4J HowTo Test"
    MainForm.Show
    'SQL1.InitializeSQLite(File.DirApp & "\SQL_Datenbank\","Schlaf_gut_Test.db", True)
    SQL1.InitializeSQLite("",":memory:", True)

    SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS BRP (Datum_Aufzeichnung Text, Datum TEXT, Uhrzeit TEXT, Plus_Zeit Text, Flow_Rate Text, Mask_Pressure Text)")
    AppInit
    '    Daten_To_DB
End Sub

'Init controls, settings etc.
Sub AppInit
    FillTableView
End Sub

Sub FillTableView
    Dim DB_Daten(3) As String
    Dim su As StringUtils
    Dim l As List
    Dim csvString As String
    Dim csVArray() As String
    csvString = File.ReadString(File.DirApp , "BRP.txt")
    csVArray = ASUtils.SplitWithSeparator(csvString,",")

   
    Log("Length of Array:" & csVArray.Length)
    For i = 0 To csVArray.Length- 1 Step 3
        SQL1.AddNonQueryToBatch("INSERT INTO BRP VALUES (?,?,?,?,?,?)", Array As Object("1", "2", "3",csVArray(i) ,csVArray(i+1) , csVArray(i+2)))
    Next
   
    Log("For Loop Complete: " & ((DateTime.Now - n)/1000) & " seconds")
    SQL1.ExecNonQueryBatch("Query")
   
     '!!!!! Uncomment For Wait For Times
    'Wait For  Query_NonQueryComplete (Success As Boolean)

    'Log("Wait For Complete: " & ((DateTime.Now - n)/1000)  & " seconds")

   
    'SQL1.Close

End Sub

'Close the app - add any specifics here
Sub AppClose
    MainForm.Close
End Sub

'Handle form closing via system close (top right X button)
Sub MainForm_CloseRequest (EventData As Event)
    AppClose
End Sub

Sub Query_NonQueryComplete(success As Boolean)
   
    Log("Sub NonQueryComplete: " & ((DateTime.Now - n)/1000)  & " seconds" )
End Sub

Waiting for debugger to connect...
Program started.
Length of Array:1188003
For Loop Complete: 1.031 seconds
Sub NonQueryComplete: 4.35 seconds

With wait for uncommented:

Waiting for debugger to connect...
Program started.
Length of Array:1188003
For Loop Complete: 19.842 seconds
Wait For Complete: 24.233 seconds

The text file used can be found in this topic

 

[stevel05]

It tries to create an extremely large batch, which fails completely on my system.

Using a transaction directly (No Async) it completes in approx 9 seconds in both Debug and Release modes.

 

[Erel]

There is no bug here. Resumable subs in debug mode cannot be optimized in the same way that non-resumable subs are optimized.

The solution is to move the long loop to a different sub:

Sub AddStatements(csvArray() As String)
 For i = 0 To csVArray.Length- 1 Step 3
        SQL1.AddNonQueryToBatch("INSERT INTO BRP VALUES (?,?,?,?,?,?)", Array As Object("1", "2", "3",csVArray(i) ,csVArray(i+1) , csVArray(i+2)))
  Next
End Sub

Now call it from FillTableView and it will be fast.
A similar issue is explained in the tutorial (tip #2): https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content

 

[stevel05]

That works here, Approx 9 seconds in debug and release.

 

[keirS]

That will teach me not to just download library updates without reading the documentation that goes with it...