Android Question CustomListView Performance using SQLite

[Bernard Harris]

I am having a performance issue with an app that is using the CustomListView. I am not certain if it's the SQLite database slowing it down or just building the CustomListView.
I have only two labels being populated with text and a button on each list item. Normally it takes just a second to load up but when there are more than a hundred items or so it slows down. In my example I have almost 300 items and it takes about 5 seconds to finally load and show the CustomListView. I've tried adding an index to the SQLite table but that didn't appear to have any affect. What would be the best recommendation for getting subsecond load up? I've thought about putting the data as a List in memory or somehow just updating the background color of the list item and moving it to the bottom, not sure how to do that though.
As a side note - the app has a scan function that when an item is scanned that list item moves to the bottom when it refreshes the list view. Because they can scan quickly I need to have it available immediately. They need the scanned items in the list to move out of the way.

 

[Peter Simpson]

Can you post an example of your code?
From experience I would say that 300 items from an SQLite DB should only take about 2 or 3/10 of a second or there about to read and populate. I would also say 1 second to load up to items is also too slow as well, especially if you are not loading any images or large data strings.

 

[Bernard Harris]

Thanks Peter. Here is a sample of the code being used to load the CustomListView:

            SQLCursor = SQL1.ExecQuery2("SELECT * FROM Orders WHERE LOADNO = ? AND WSSTOP = ? ORDER BY SCANNED, MRGTKT, ORDNO, ITMNO, ITMSEQ", Array As String(CurrentLoad, CurrentStopNo))
            For i = 0 To SQLCursor.RowCount-1
                SQLCursor.Position = i
                If SQLCursor.GetLong("MRGTKT") > 0 Then
                    TicketNumber = SQLCursor.GetLong("MRGTKT")
                Else
                    TicketNumber = SQLCursor.GetLong("TKTNO")
                End If
                ' Only include the merge ticket once.  All others will have a different ticket each time and will get included.
                If TicketNumber <> PrvTicketNo Then
                    CounterText = (i+1) & " of " & SQLCursor.RowCount
                    Text1 = "Order #: " & CounterText & CRLF & SQLCursor.GetLong("ORDNO") & "-" & SQLCursor.GetInt("ITMNO") & "-" & SQLCursor.GetInt("ITMSEQ") & CRLF & "Ticket #: " & TicketNumber & " - " & SQLCursor.GetString("TKTTYPE")
                    Text2 = SQLCursor.GetString("PRODDESC").Trim
                    If SQLCursor.GetString("SCANNED").Trim = "N" And SQLCursor.GetString("ISSUE").Trim = "" Then
                        EnableSignature = False
                    End If
                    clvOrders.Add(CreateListItemOrders(Text1, Text2, clvOrders.AsView.Width, 100dip, SQLCursor.GetString("SCANNED").Trim), TicketNumber)
                End If
                PrvTicketNo = TicketNumber
            Next

 

[Bernard Harris]

I just did a test commenting out the clvOrders.add and doing a Message after the SQL loop. It took 3 seconds to loop through the records in the table, on average.

 

[OliverA]

In release mode?

 

[Erel]

Measure your code in release mode. If it is still slow then measure it with and without adding the items to the list.

 

[Bernard Harris]

I take that back. I found out the tablet was updating so I retested the commenting out of the clvOrders.Add and now I am getting immediate response just looping the SQL data. It appears the loading of the CustomListView is the culprit.

 

[Bernard Harris]

I retested in Release mode and measured both with the CustomListView.Add and without. Here are my results:
With CustomListView.Add: 5 seconds
Without CustomListView.Add: subsecond
This tells me that the SQLite database access is as Peter stated above - immediate, and that the CustomListView load up is slow.
So what is the best method to handle the loadup of the CustomListView? My Text boxes, which are two, have approximately 50 to 100 characters each.

 

[Peter Simpson]

@Bernard Harris you should use Lazy Loading with xCustomListView, looking at your code in post #3 your CLV should still be running quicker than you say but it could just be your 50 to 100 characters per Editext view, but even then it should still be running quicker.

Adapt your code using the following post https://www.b4x.com/android/forum/t...ew-lazy-loading-virtualization.87930/#content

 

[Bernard Harris]

Thanks @Peter Simpson. I will give that a try. I was looking at it earlier and also the Type class to use in a list. Could it be that it's because I'm using an older version of the clv or B4A itself? I'm not sure what version I am using at the moment.

 

[Peter Simpson]

It should not really make that much difference.

Here you go @Bernard Harris https://www.b4x.com/android/forum/attachments/xclsviewcardviewll-zip.71617/

 

[Bernard Harris]

I've examined this code a bit and it is doing a mass load of the CustomListView, similar to how I am. The biggest difference is that the panel I create is a type Panel whereas this sample code is a type xui.CreatePanel(""). Is it that difference that is making mine take so much time? I'm not sure the difference between them, and why, but the sample code is loading 1000 items in the CLV in less than a second and mine takes over 5 seconds for less than 300 items. And mine is only text. How can it vary so much?

 

[npsonic]

I've examined this code a bit and it is doing a mass load of the CustomListView, similar to how I am. The biggest difference is that the panel I create is a type Panel whereas this sample code is a type xui.CreatePanel(""). Is it that difference that is making mine take so much time? I'm not sure the difference between them, and why, but the sample code is loading 1000 items in the CLV in less than a second and mine takes over 5 seconds for less than 300 items. And mine is only text. How can it vary so much?

Could you provide more code, so we can test how it could be made run faster.

Also could you provide code for method "CreateListItemOrders" and do you use LoadLayout? If, so you should know that it's 10 times slower than creating layout programmatically.

For your question about example of the lazy loading. Only value is saved and empty panel in added to list. Only panels visible on screen are actually created.

 

[npsonic]

If you want to do something similar then do this.

Sub FillList
    SQLCursor = SQL1.ExecQuery2("SELECT * FROM Orders WHERE LOADNO = ? AND WSSTOP = ? ORDER BY SCANNED, MRGTKT, ORDNO, ITMNO, ITMSEQ", Array As String(CurrentLoad, CurrentStopNo))
    For i = 0 To SQLCursor.RowCount-1
        SQLCursor.Position = i
        If SQLCursor.GetLong("MRGTKT") > 0 Then
            TicketNumber = SQLCursor.GetLong("MRGTKT")
        Else
            TicketNumber = SQLCursor.GetLong("TKTNO")
        End If
        ' Only include the merge ticket once.  All others will have a different ticket each time and will get included.
        If TicketNumber <> PrvTicketNo Then
            CounterText = (i+1) & " of " & SQLCursor.RowCount
            Text1 = "Order #: " & CounterText & CRLF & SQLCursor.GetLong("ORDNO") & "-" & SQLCursor.GetInt("ITMNO") & "-" & SQLCursor.GetInt("ITMSEQ") & CRLF & "Ticket #: " & TicketNumber & " - " & SQLCursor.GetString("TKTTYPE")
            Text2 = SQLCursor.GetString("PRODDESC").Trim
            If SQLCursor.GetString("SCANNED").Trim = "N" And SQLCursor.GetString("ISSUE").Trim = "" Then
                EnableSignature = False
            End If
            Dim p As B4XView = xui.CreatePanel("")
            p.SetLayoutAnimated(0, 0, 0, clvOrders.AsView.Width, 100dip)
            clvOrders.Add(p, CreateMap("Text1": Text1, "Text2": Text2, "SCANNED": SQLCursor.GetString("SCANNED").Trim, "TicketNumber": TicketNumber))
        End If
        PrvTicketNo = TicketNumber
    Next
End Sub

Sub clvOrders_VisibleRangeChanged (FirstIndex As Int, LastIndex As Int)
    Dim ExtraSize As Int = 20
    For i = 0 To clvOrders.Size - 1
        Dim P As B4XView = clvOrders.GetPanel(i)
        If i > FirstIndex - ExtraSize And i < LastIndex + ExtraSize Then
            If p.NumberOfViews = 0 Then
                Dim m As Map = clvOrders.GetValue(i)
                p.AddView(CreateListItemOrders(m.Get("Text1"), m.Get("Text2"), p.Width, p.Height, m.Get("SCANNED")),0,0,p.Width,p.Height)
            End If
        Else
            If p.NumberOfViews > 0 Then p.RemoveAllViews
        End If
    Next
End Sub

 

[Bernard Harris]

Here is a sample of the CreateListItemOrders. The rest of the code is above, where it loads the CLV. And yes, it is using LoadLayout. So by not using the load layout I could get better performance and then use the lazy loading to get the visible items when needed?

Sub CreateListItemOrders(Text1 As String, Text2 As String, Width As Int, Height As Int, Scanned As String) As Panel
    
    Dim p As Panel
    p.Initialize("")
    p.SetLayout(0, 0, Width, Height)
    p.LoadLayout("ListItemOrders")
    lblOrder1.Text = Text1
    lblOrder2.Text = Text2
    If Scanned = "Y" Then
        lblOrder1.Color = Colors.Green
        lblOrder2.Color = Colors.Green
    End If
    Return p
    
End Sub

 

[Bernard Harris]

I think I am now following how this could work. And you answered my question in your last post. This makes sense and I will give it a try.

 

[npsonic]

Here is a sample of the CreateListItemOrders. The rest of the code is above, where it loads the CLV. And yes, it is using LoadLayout. So by not using the load layout I could get better performance and then use the lazy loading to get the visible items when needed?

Sub CreateListItemOrders(Text1 As String, Text2 As String, Width As Int, Height As Int, Scanned As String) As Panel
  
    Dim p As Panel
    p.Initialize("")
    p.SetLayout(0, 0, Width, Height)
    p.LoadLayout("ListItemOrders")
    lblOrder1.Text = Text1
    lblOrder2.Text = Text2
    If Scanned = "Y" Then
        lblOrder1.Color = Colors.Green
        lblOrder2.Color = Colors.Green
    End If
    Return p
  
End Sub

Without LoadLayout you will get about 10 times faster times, but it shouldn't matter at all. If you use lazy loading example everything should be fast enough.
I use LoadLayout in many lists and speed is never problem if everything is implemented right. Typical loading time for LoadLayout is about 10 - 30ms in release mode.

 

[Bernard Harris]

This code change is much faster, thanks npsonic!

I had to upgrade my CustomListView.bas because the VisibleRangeChanged wasn't in the older version. Once I did that it loads correctly and takes a bit more than 1 second to get the 300 items. This is much faster than the 5 seconds before.