Bug? Slow to process strings of text.

[vecino]

Hello, this function reads multiple text files, each line divided into several parts and then import that data.
This was a relatively rapid process. However, compiling with version 3.82 is agonizingly slow.
It seems that the slow occurs in them functions of strings of text: substring2, trim, upper, etc.

With previous versions took 5 minutes, now takes more than 1 hour

Sub Tratar( cRuta, cFile, cPrefijo As String ) As Boolean
'    Dim lista As List  
    Dim Reader As TextReader
'    Dim iX As Int
    Dim cTabla, cLin, cSql As String
    Dim iReg As Int = 0
    '  
    Dim  c1, c2, c3, c4, c5, c6, c7, c8, c9,c10 As String
    Dim c11,c12,c13,c14,c15,c16,c17,c18,c19,c20 As String  
    Dim c21,c22,c23,c24,c25,c26,c27,c28,c29,c30 As String
    '  
'    lista.Initialize
    '
    Select cPrefijo
      Case cFAMim: cTabla = "tbFamilias"
        Case cCLIim: cTabla = "tbClientes"
        Case cARTim: cTabla = "tbArticulos"
        Case cCODim: cTabla = "tbCodigosAlternativos"
        Case cPREim: cTabla = "tbPreciosEspeciales"
        Case cPENim: cTabla = "tbPendientesCobro"
        Case cMENim: cTabla = "tbMensajesClientes"
        Case cCONim: cTabla = "tbArticulosConsumidos"
        Case cTEXim: cTabla = "tbTextosObservaciones"
        Case cOFEim: cTabla = "tbOfertas"
        Case cRUTim: cTabla = "tbRutas"
        Case "": cTabla = ""
    End Select
    '
'    Log("cRuta: "&cRuta&" ")
'    Log("cFile: "&cFile&" ")
'    Log("cPrefijo: "&cPrefijo&" ")
'    Log("cTabla: "&cTabla&" ")  
    '
    If cTabla="" Then
      Return
    End If
    ' borramos los datos de la tabla
    globales.DBconex.ExecNonQuery("delete from '" & cTabla & "' ")      
    ' ------------------------------
    lvLog.AddSingleLine(cTabla&"...")              
    lvLog.SetSelection( lvLog.Size-1 )  
    DoEvents  
  '  
    Reader.Initialize2(File.OpenInput(cRuta, cFile),"8859-1")  
'    lista = Reader.ReadList            : Log(lista.Size)
'    Reader.Close
    '
    '
  cLin=""  
  Do While cLin <> Null      
'    For iX=0 To lista.Size-1
        '
    cLin = Reader.ReadLine      
'        cLin = lista.Get(iX)
        If cLin=Null Then Exit
        '
        '
        iReg = iReg+1
        If (iReg Mod 16)=0 Then
            DoEvents
        End If
'        If (iX Mod 16)=0 Then
'            DoEvents
'        End If
        '      
        '
        cLin = cLin.Replace(",",".")
        '
        '
        Select cPrefijo
            '
          Case cFAMim: ' tbFamilias                                                                                                                                  
                c1="":c2=""
                If (cLin.Trim).Length>0 Then
                    c1 = cLin.SubString2(0,7)        :  c1 = c1.Trim.ToUpperCase    ' todos los campos claves los paso a mayúsculas (por las búsquedas)
                    c2 = cLin.SubString2(7,37)    :  c2 = c2.Trim
                    ' insertamos en la BD
                    Try
                        globales.DBconex.ExecNonQuery2("insert into '" & cTabla & "' values (?,?)",Array As String(c1,c2))                  
                    Catch
                        Log(cTabla&": "&LastException.Message)                      
                    End Try
                End If
                '
            Case cCLIim:    ' tbClientes
                c1="":  c2="": c3="": c4="": c5="": c6="": c7="": c8="": c9="":c10=""
                c11="":c12="":c13="":c14="":c16="":c16="":c17="":c18="":c19="":c20=""
                c21="":c22="":c23="":c24="":c25="":c26="":c27="":c28=""
                If (cLin.Trim).Length>0 Then              
                    c1  = cLin.SubString2(0,10)                :  c1  = c1.Trim.ToUpperCase    ' codigo
                    c2  = cLin.SubString2(10,40)            :  c2  = c2.Trim.ToUpperCase    ' nombrecomercial
                    c3  = cLin.SubString2(40,70)            :  c3  = c3.Trim.ToUpperCase    ' razonsocial
                    c4  = cLin.SubString2(70,100)            :  c4  = c4.Trim
                    c5  = cLin.SubString2(100,130)        :  c5  = c5.Trim
                    c6  = cLin.SubString2(130,144)        :  c6  = c6.Trim    :    c6 = funciones.Limpiar(c6)    ' NIF
                    c7  = cLin.SubString2(144,164)        :  c7  = c7.Trim
                    c8  = cLin.SubString2(164,184)        :  c8  = c8.Trim
                    c9  = cLin.SubString2(184,185)        :  c9  = c9.Trim
                    c10 = cLin.SubString2(185,186)        :  c10 = c10.Trim
                    c11 = cLin.SubString2(186,188)        :  c11 = c11.Trim
                    c12 = cLin.SubString2(188,189)        :  c12 = c12.Trim
                    c13 = cLin.SubString2(189,194)        :  c13 = c13.Trim
                    c14 = cLin.SubString2(194,199)        :  c14 = c14.Trim
                    c15 = cLin.SubString2(199,204)        :  c15 = c15.Trim
                    c16 = cLin.SubString2(204,214)        :  c16 = c16.Trim
                    c17 = cLin.SubString2(214,222)        :  c17 = c17.Trim
                    c18 = cLin.SubString2(222,223)        :  c18 = c18.Trim
                    c19 = cLin.SubString2(223,224)        :  c19 = c19.Trim
                    c20 = cLin.SubString2(224,225)        :  c20 = c20.Trim
                    c21 = cLin.SubString2(225,226)        :  c21 = c21.Trim
                    c22 = cLin.SubString2(226,227)        :  c22 = c22.Trim
                    c23 = cLin.SubString2(227,228)        :  c23 = c23.Trim
                    c24 = cLin.SubString2(228,229)        :  c24 = c24.Trim
                    c25 = cLin.SubString2(229,230)        :  c25 = c25.Trim
                    c26 = cLin.SubString2(230,231)        :  c26 = c26.Trim
                    c27 = cLin.SubString2(231,232)        :  c27 = c27.Trim
                    c28 = cLin.SubString2(232,242)        :  c28 = c28.Trim                  
                    ' insertamos en la BD
                    cSql = "insert into '" & cTabla & "' values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?) "    ' añadido para facturaplus
                    Try
                        globales.DBconex.ExecNonQuery2( cSql, Array As String(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28, "0","","","","0","0","0","","1","N","0","0","N","0" )) ' añadido para facturaplus
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If
                '
            Case cARTim:    ' tbArticulos
                c1="":  c2="": c3="": c4="": c5="": c6="": c7="": c8="": c9="":c10=""
                c11="":c12="":c13="":c14="":c16="":c16="":c17="":c18="":c19="":c20=""
                c21="":c22="":c23="":c24=""
                If (cLin.Trim).Length>0 Then              
                    c1  = cLin.SubString2(0,18)            :  c1  = c1.Trim.ToUpperCase    ' codigo  
                    c2  = cLin.SubString2(18,25)        :  c2  = c2.Trim.ToUpperCase    ' codigofamilia
                    c3  = cLin.SubString2(25,55)        :  c3  = c3.Trim.ToUpperCase    ' nombre
                    c4  = cLin.SubString2(55,62)        :  c4  = c4.Trim
                    c5  = cLin.SubString2(62,69)        :  c5  = c5.Trim
                    c6  = cLin.SubString2(69,76)        :  c6  = c6.Trim
                    c7  = cLin.SubString2(76,83)        :  c7  = c7.Trim
                    c8  = cLin.SubString2(83,90)        :  c8  = c8.Trim
                    c9  = cLin.SubString2(90,97)        :  c9  = c9.Trim
                    c10 = cLin.SubString2(97,104)        :  c10 = c10.Trim
                    c11 = cLin.SubString2(104,111)    :  c11 = c11.Trim
                    c12 = cLin.SubString2(111,118)    :  c12 = c12.Trim
                    c13 = cLin.SubString2(118,125)    :  c13 = c13.Trim
                    c14 = cLin.SubString2(125,132)    :  c14 = c14.Trim
                    c15 = cLin.SubString2(132,133)    :  c15 = c15.Trim
                    c16 = cLin.SubString2(133,139)    :  c16 = c16.Trim
                    c17 = cLin.SubString2(139,145)    :  c17 = c17.Trim
                    c18 = cLin.SubString2(145,146)    :  c18 = c18.Trim
                    c19 = cLin.SubString2(146,147)    :  c19 = c19.Trim
                    c20 = cLin.SubString2(147,148)    :  c20 = c20.Trim
                    c21 = cLin.SubString2(148,149)    :  c21 = c21.Trim
                    c22 = cLin.SubString2(149,150)    :  c22 = c22.Trim
                    c23 = cLin.SubString2(150,168)    :  c23 = c23.Trim
                    c24 = cLin.SubString2(168,169)    :  c24 = c24.Trim                  
                    ' insertamos en la BD
                    cSql = "insert into '" & cTabla & "' values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?) "    ' añadido para facturaplus
                    Try
                        globales.DBconex.ExecNonQuery2( cSql, Array As String(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24, "0","0","","" )) ' añadido para facturaplus
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If
                '
            Case cCODim: ' tbCodigosAlternativos
                c1="":c2=""
                If (cLin.Trim).Length>0 Then
                    c1 = cLin.SubString2(0,18)    :  c1 = c1.Trim.ToUpperCase    ' el código alternativo
                    c2 = cLin.SubString2(18,36)    :  c2 = c2.Trim.ToUpperCase ' código en tbArticulos
                    ' insertamos en la BD                  
                    Try
                        globales.DBconex.ExecNonQuery2("insert into '" & cTabla & "' values (?,?)",Array As String(c1,c2))                                      
                    Catch
                    End Try
                End If
                '
            Case cPREim: ' tbPreciosEspeciales
                c1="":  c2="": c3="": c4="": c5="": c6="": c7=""
                If (cLin.Trim).Length>0 Then
                    c1  = cLin.SubString2(0,1)            :  c1  = c1.Trim  
                    c2  = cLin.SubString2(1,11)          :  c2  = c2.Trim.ToUpperCase    ' el codigo del cliente
                    c3  = cLin.SubString2(11,29)        :  c3  = c3.Trim.ToUpperCase  ' el codigo del artículo o de la familia
                    c4  = cLin.SubString2(29,36)        :  c4  = c4.Trim
                    c5  = cLin.SubString2(36,43)        :  c5  = c5.Trim
                    c6  = cLin.SubString2(43,48)        :  c6  = c6.Trim
                    c7  = cLin.SubString2(48,53)        :  c7  = c7.Trim
                    ' insertamos en la BD
                    Try
                        globales.DBconex.ExecNonQuery2("insert into '" & cTabla & "' values (?,?,?,?,?,?,?)",Array As String(c1,c2,c3,c4,c5,c6,c7))                  
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If
                '
            Case cPENim: ' tbPendientesCobro
                c1="":  c2="": c3="": c4="": c5="": c6="": c7=""
                If (cLin.Trim).Length>0 Then              
                    c1  = cLin.SubString2(0,10)            :  c1  = c1.Trim.ToUpperCase    ' el código del cliente  
                    c2  = cLin.SubString2(10,20)      :  c2  = c2.Trim  
                    c3  = cLin.SubString2(20,30)        :  c3  = c3.Trim  
                    c4  = cLin.SubString2(30,31)        :  c4  = c4.Trim
                    c5  = cLin.SubString2(31,41)        :  c5  = c5.Trim
                    c6  = cLin.SubString2(41,49)        :  c6  = c6.Trim
                    c7  = cLin.SubString2(49,57)        :  c7  = c7.Trim
                    ' insertamos en la BD
                    Try
                        globales.DBconex.ExecNonQuery2("insert into '" & cTabla & "' values (?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?)",Array As Object(c1,c2,c3,c4,c5,c6,c7,"",0,"","","N",iReg, "","","","","","","0","1" ))    ' añadido para facturaplus
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If
                '
            Case cMENim: ' tbMensajesClientes
                c1="":  c2="": c3="": c4="": c5="": c6="": c7="": c8="": c9="":c10=""
                c11="":c12="":c13=""
                If (cLin.Trim).Length>0 Then              
                    c1  = cLin.SubString2(0,10)            :  c1  = c1.Trim.ToUpperCase    ' el código del cliente
                    c2  = cLin.SubString2(10,11)        :  c2  = c2.Trim  
                    c3  = cLin.SubString2(11,71)        :  c3  = c3.Trim  
                    c4  = cLin.SubString2(71,72)        :  c4  = c4.Trim
                    c5  = cLin.SubString2(72,132)        :  c5  = c5.Trim
                    c6  = cLin.SubString2(132,133)    :  c6  = c6.Trim
                    c7  = cLin.SubString2(133,193)    :  c7  = c7.Trim
                    c8  = cLin.SubString2(193,194)    :  c8  = c8.Trim
                    c9  = cLin.SubString2(194,254)    :  c9  = c9.Trim
                    c10 = cLin.SubString2(254,255)    :  c10 = c10.Trim
                    c11 = cLin.SubString2(255,315)    :  c11 = c11.Trim
                    c12 = cLin.SubString2(315,316)    :  c12 = c12.Trim
                    c13 = cLin.SubString2(316,376)    :  c13 = c13.Trim
                    ' insertamos en la BD                  
                    cSql = "insert into '" & cTabla & "' values (?,?,?,?,?,?,?,?,?,?,?,?,?) "
                    Try
                        globales.DBconex.ExecNonQuery2( cSql, Array As String(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13))
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If              
                '
            Case cCONim: ' tbArticulosConsumidos
                c1="":  c2="": c3="": c4="": c5="": c6="": c7="": c8="": c9=""
                If (cLin.Trim).Length>0 Then              
                    c1  = cLin.SubString2(0,10)            :  c1  = c1.Trim.ToUpperCase    ' el código del cliente 10
                    c2  = cLin.SubString2(10,28)        :  c2  = c2.Trim.ToUpperCase    ' el código del artículo 18
                    c3  = cLin.SubString2(28,58)        :  c3  = c3.Trim    ' nombre 30
                    c4  = cLin.SubString2(58,68)        :  c4  = c4.Trim  ' fecha 10
                    c5  = cLin.SubString2(68,75)        :  c5  = c5.Trim  ' precio      double ¿ 7 ?
                    c6  = cLin.SubString2(75,82)        :  c6  = c6.Trim  ' unidades    double ¿ 7 ?
                    c7  = cLin.SubString2(82,135)        :  c7  = c7.Trim  ' observaciones 53
                    c8  = cLin.SubString2(135,142)    :  c8  = c8.Trim    ' codigogrupo 7
                    Try
                        c9  = cLin.SubString(142)          :    c9  = c9.Trim  ' descuento  double ¿ 7 ?
                    Catch                      
                      c9 = "0"' Por si acaso usa esta versión con un fichero que no traiga ese dato.    19.05.2014
                    End Try
                    ' insertamos en la BD
                    cSql = "insert into '" & cTabla & "' values (?,?,?,?,?,?,?,?,?) "
                    Try
                        globales.DBconex.ExecNonQuery2( cSql, Array As String(c1,c2,c3,c4,c5,c6,c7,c8,c9))
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If  
                '
            Case cTEXim: ' tbTextosObservaciones                                                                                                                              
                c1="":c2=""
                If (cLin.Trim).Length>0 Then
                    c1 = cLin.SubString2(0,10)        :  c1 = c1.Trim.ToUpperCase ' codigo
                    c2 = cLin.SubString2(10,70)        :  c2 = c2.Trim
                    ' insertamos en la BD
                    Try
                        globales.DBconex.ExecNonQuery2("insert into '" & cTabla & "' values (?,?)",Array As String(c1,c2))                  
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If          
                '
            Case cRUTim: ' tbRutas
                c1="":c2="":c3=""
                If (cLin.Trim).Length>0 Then
                    c1 = cLin.SubString2(0,3)        :  c1 = c1.Trim.ToUpperCase ' codigo
                    c2 = cLin.SubString2(3,4)        :  c2 = c2.Trim
                    c3 = cLin.SubString2(4,14)    :  c3 = c3.Trim
                    ' insertamos en la BD
                    Try
                        globales.DBconex.ExecNonQuery2("insert into '" & cTabla & "' values (?,?,?,?,?)",Array As String(c1,c2,c3,"",iReg))
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If          
                '
            Case cOFEim: ' tbOfertas
                c1="":  c2="": c3="": c4="": c5="": c6="": c7="": c8="": c9="":c10=""
                c11="":c12="":c13="":c14="":c15=""              
                If (cLin.Trim).Length>0 Then
                    c1  = cLin.SubString2(0,1)            :  c1  = c1.Trim  
                    c2  = cLin.SubString2(1,11)            :  c2  = c2.Trim.ToUpperCase    ' código del cliente
                    c3  = cLin.SubString2(11,29)        :  c3  = c3.Trim.ToUpperCase  ' código del artículo o familia
                    c4  = cLin.SubString2(29,36)        :  c4  = c4.Trim
                    c5  = cLin.SubString2(36,43)        :  c5  = c5.Trim
                    c6  = cLin.SubString2(43,48)        :  c6  = c6.Trim
                    c7  = cLin.SubString2(48,53)        :  c7  = c7.Trim
                    c8  = cLin.SubString2(53,60)        :  c8  = c8.Trim
                    c9  = cLin.SubString2(60,67)        :  c9  = c9.Trim
                    c10 = cLin.SubString2(67,85)        :  c10 = c10.Trim
                    c11 = cLin.SubString2(85,95)        :  c11 = c11.Trim
                    c12 = cLin.SubString2(95,105)        :  c12 = c12.Trim
                    c13 = cLin.SubString2(105,112)    :  c13 = c13.Trim
                    c14 = cLin.SubString2(112,119)    :  c14 = c14.Trim
                    c15 = cLin.SubString2(119,126)    :  c15 = c15.Trim
                    ' insertamos en la BD              
                    cSql = "insert into '" & cTabla & "' values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "
                    Try
                        globales.DBconex.ExecNonQuery2( cSql, Array As String(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15))                  
                    Catch
                        Log(cTabla&": "&LastException.Message)
                    End Try
                End If
            '  
            '  
            Case Else: Log(cTabla&" (else): "&LastException.Message)
            '
            '
        End Select
  Loop
'    Next
    '
'    lista.Clear
    '
  If Reader.IsInitialized Then
        Reader.Close  
    End If
    ' ---  
End Sub

 

[DonManfred]

Maybe the use of transactions may be helpful cause it is much faster if you have a lot of queries which will be send to the database.

 

[vecino]

Thanks for answering.

Before it was fast, now it's slow. I have not changed anything. B4A version only.
The problem also occurs with just 50 records, even less.

 

[vecino]

Example: text file with only 47 lines. And with only 2 fields.
It also takes a long time before and was almost instantaneous.

Sub Tratar( cRuta, cFile, cPrefijo As String ) As Boolean
'    Dim lista As List   
    Dim Reader As TextReader
'    Dim iX As Int
    Dim cTabla, cLin, cSql As String
    Dim iReg As Int = 0
    '   
    Dim  c1, c2 As String
    '   
'    lista.Initialize
    '
    Select cPrefijo
      Case cFAMim: cTabla = "tbFamilias"
    End Select
    '
'    Log("cRuta: "&cRuta&" ")
'    Log("cFile: "&cFile&" ")
'    Log("cPrefijo: "&cPrefijo&" ")
'    Log("cTabla: "&cTabla&" ")   
    '
    If cTabla="" Then
      Return
    End If
    ' borramos los datos de la tabla
    globales.DBconex.ExecNonQuery("delete from '" & cTabla & "' ")       
    ' ------------------------------
    lvLog.AddSingleLine(cTabla&"...")               
    lvLog.SetSelection( lvLog.Size-1 )   
    DoEvents   
  '   
    Reader.Initialize2(File.OpenInput(cRuta, cFile),"8859-1")   
'    lista = Reader.ReadList            : Log(lista.Size)
'    Reader.Close
    '
    '
  cLin=""   
  Do While cLin <> Null       
'    For iX=0 To lista.Size-1
        '
    cLin = Reader.ReadLine       
'        cLin = lista.Get(iX)
        If cLin=Null Then Exit
        '
        '
        iReg = iReg+1
        If (iReg Mod 16)=0 Then
            DoEvents
        End If
'        If (iX Mod 16)=0 Then
'            DoEvents
'        End If
        '       
        '
        cLin = cLin.Replace(",",".")
        '
        '
        Select cPrefijo
            '
          Case cFAMim: ' tbFamilias                                                                                                                                   
                c1="":c2=""
                If (cLin.Trim).Length>0 Then
                    c1 = cLin.SubString2(0,7)        :  c1 = c1.Trim.ToUpperCase    ' todos los campos claves los paso a mayúsculas (por las búsquedas)
                    c2 = cLin.SubString2(7,37)    :  c2 = c2.Trim
                    ' insertamos en la BD
                    Try
                        globales.DBconex.ExecNonQuery2("insert into '" & cTabla & "' values (?,?)",Array As String(c1,c2))                   
                    Catch
                        Log(cTabla&": "&LastException.Message)                       
                    End Try
                End If
                '
        End Select
  Loop
'    Next
    '
'    lista.Clear
    '
  If Reader.IsInitialized Then
        Reader.Close   
    End If
    ' ---   
End Sub

 

[picenainformatica]

remove the line with execnonquery to isolate text file timing from database timing.

 

[vecino]

Hi, I'm really surprised, it was solved by using "Transactions".
Now it is very fast.
Thank you very much, friends

 

[keirS]

If you want to make it even faster enable Write Ahead Logging (WAL) in you database for Android versions >= Honeycomb.

EexcNonQuery("PRAGMA journal_mode = WAL")

 

[vecino]

Great, I'll try.

 

[Erel]

As you see above you should always use transactions if you execute more than a single non-query statement. Otherwise a transaction will be created implicitly for each statement.

Note that not nothing has changed in v3.82 that affects the performance here (and it also has nothing to do with text processing).

 

[vecino]

I'll remember. Thanks for the information.
I had never used sqlite.
However, this import was fast before installing version 3.82.
I do not know why.

 

[vecino]

Thank you very much to all.
Every day I learn something ...

 

[keirS]

As you see above you should always use transactions if you execute more than a single non-query statement. Otherwise a transaction will be created implicitly for each statement.

Note that not nothing has changed in v3.82 that affects the performance here (and it also has nothing to do with text processing).

In theory you should wrap multiple SELECT statements in a transaction as well. That way you only have to get one shared lock for all the queries. Otherwise each query will obtain a shared lock then release it.