Android Question Code optimization for speed?

[JGiunta]

Hello,

I am developing an app that reads content from a CSV file and puts it into a SQLite DB.
Now, the process was extremely slow in debug, but after trying in release, it was almost instantaneous, now it takes about 2 seconds for 100 rows to be completed, and my hypothetical standard file has something more than 31.000 rows.
Now, after modifying other parts of the code, the same process is significantly slower even in release.

I have tried different things:

• executing a query to check for uniqueness in the DB prior to inserting the value
• Executing all the inserts in a batch after the parsing was done
• Executing one at the time when the parsing of one row was done.

How can i optimize the code to speed this up?

ConnessioneDB.Initialize(File.DirInternal, "DatabaseListino.sqlite",False)
        ConnessioneDB.BeginTransaction
        If(File.Exists(File.DirInternal,"Listino.csv") = True) Then
     
            ListaCSV = File.ReadList(File.DirInternal,"Listino.csv")
            Function.Logga("Linee nel CSV: " & ListaCSV.Size)
            If(ListaCSV.Size > 0) Then
         
                For i = 1 To ListaCSV.Size - 1
                    Dim Riga As RigaCSV
                    Riga.Initialize
                    If(ListaCSV.Get(i) <> "") Then
                        If(Riga.Inizializza(ListaCSV.Get(i), i) = True) Then
                            ListaRigheCSV.Add(Riga)
                            Try
                                'Dim Cursore2 As Cursor = ConnessioneDB.ExecQuery("Select count(*) as Quanti from Giacenza where barcode = '" & Riga.Barcode & "'")
                                'Cursore2.Position = 0
                                'If(Cursore2.GetInt("Quanti") <1) Then
                                    ConnessioneDB.AddNonQueryToBatch(Riga.Insert,Null)
                                'End If
                            Catch
                                Function.Logga("Problemi nel barcode: " & Riga.Barcode & " - " & LastException)
                            End Try
                        Else
                            InserimentoPerfetto = False
                            Counter = Counter + 1
                        End If
                    End If
                Next
         
            End If
         
            ConnessioneDB.ExecNonQueryBatch("")
         
         
            Dim OrarioFine As Long = DateTime.Now
            Dim OrarioTotale As Long = OrarioFine - OrarioInizio
            DateTime.TimeFormat = "hh:mm:ss"
         
            Function.Logga("Inserimento completato - Durata: " & DateTime.Time(OrarioTotale))
         
            LastUpdate = File.LastModified(File.DirInternal,"Listino.csv")
            File.Delete(File.DirInternal,"Listino.csv")
            ConnessioneDB.TransactionSuccessful
            ConnessioneDB.EndTransaction
            ConnessioneDB.Close

Public Sub Inizializza(pRiga As String, pNumRiga As Int) As Boolean
    ListaParametri.Initialize
    Try
        If( pRiga <> "" ) Then
            Riga = pRiga
            ListaParametri = Regex.Split(";",Riga)
            If(ListaParametri.Size >44) Then
                If(ListaParametri.Get(13).As(String).Trim <> "") Then
                    Produttore             = ListaParametri.Get(4)
                    Note                 = ListaParametri.Get(6)
                    Descrizione         = ListaParametri.Get(9)
                    Barcode             = ListaParametri.Get(13)
                    UnitaMisura         = ListaParametri.Get(16)
                    Fornitore             = ListaParametri.Get(24)
                 
                    Dim PrezzoStringa As String = ListaParametri.Get(38).As(String).Replace("� ","").Replace("€ ","").Trim
                    If(PrezzoStringa= "") Then
                        PrezzoStringa = "0"
                    End If
                    PrezzoVenditaIvato     = PrezzoStringa
             
                    Dim StringaGiacenza As String = ListaParametri.Get(43).As(String).Replace(",",".").Replace("� ","").Replace("€ ","")
                    If(StringaGiacenza = "" Or IsNumber(StringaGiacenza) = False) Then
                        StringaGiacenza = "0"
                    End If
                    Giacenza             = StringaGiacenza.As(Double)
                Else
                    Function.Logga("Riga con Barcode vuoto: - " & pNumRiga)
                    Return False
                End If
            Else
                Function.Logga("Riga con numero parametri errati: - " & pNumRiga)
                Return False
            End If
        End If
    Catch
        Function.Logga("Riga con errore specifico: " & LastException)
        Return False
    End Try
    Return True
End Sub

 

[John Naylor]

Hard to say given what you've posted. Any chance of an actual bit of code that we can run and data to run it with? Where's taking the time?

For / Next loops are better done with For Each...

From what you've posted it is *really* difficult to give any advice.

Add some timers and see where the bottleneck is.

 

[MicroDrie]

Now, after modifying other parts of the code, the same process is significantly slower even in release.

It therefore seems more obvious to me that the modified program is the cause of the slower processing.

Now, the process was extremely slow in debug, but after trying in release, it was almost instantaneous, now it takes about 2 seconds for 100 rows to be completed, and my hypothetical standard file has something more than 31.000 rows.

Opening and closing a file takes a relatively long time. If you do that once, the duration of 10 times more records does not have to take 10 times longer.

You could see if using SQLite string functions is faster

And yes, flip-thinking, if you can't get thousands of records read faster by doing all kinds of string editing, you might want to lazy loading with a limited number of records with slow string conversion to camouflage the slowness .