Android Question B4XTable DataUpdated Sub with a Blob Column from a SQLite Table

[Mahares]

The below code in B4XTable1_DataUpdated sub works, but it seems very inefficient as the query to display the imageview with the blob has to be run for every record on the visible ids. Is there a better way to get the blobs, convert to bmp and display onto the imageviews.

Sub B4XTable1_DataUpdated
    For i = 0 To B4XTable1.VisibleRowIds.Size - 1
        Dim RowId As Long = B4XTable1.VisibleRowIds.Get(i)
        Dim pnl As B4XView = FlagsColumn.CellsLayouts.Get(i + 1)
        Dim iv As B4XView = pnl.GetView(1)
        If RowId > 0 Then      
            Dim row As Map = B4XTable1.GetRow(RowId)
            Dim cursor1 As Cursor
            cursor1=Starter.SQL1.ExecQuery("SELECT FLAG FROM " &   Starter.DBTableName & " WHERE ID =" & row.Get("ID"))
            cursor1.Position=0
            Dim buffer() As Byte =cursor1.GetBlob("FLAG")
            iv.SetBitmap(BytesToImage(buffer))
            cursor1.Close       
        Else
            iv.SetBitmap(Null)
        End If
    Next  
End Sub

Table structure: ID INTEGER AUTOINCREMENT, COUNTRY TEXT , FLAG BLOB
Thank you

 

[Erel]

but it seems very inefficient

First step is to measure it.

Dim n As Long = DateTime.Now
 For i = 0 To B4XTable1.VisibleRowIds.Size - 1
 
 Next
Log($"Time: ${DateTime.Now - n}ms"$)

Run it in release mode. What is the output?

If it is too slow then you should comment the BytesToImage line and check the timing again. This will help you understand where is the bottleneck.

 

[Mahares]

First step is to measure it

It is not slow even in debug because we are only talking about a table with a total of 225 flags. But I am questioning this query to be done for every visible ID:
cursor1=Starter.SQL1.ExecQuery("SELECT FLAG FROM " & Starter.DBTableName & " WHERE ID =" & row.Get("ID"))
Is there a way to store the ID and Blob in a map when first run for all items, then use the map to extract the blob ( as a value) using the ID as the key or a list or something. This way I can just retrieve the bytes of the blob (map value) based on the ID ( map key)
in the B4XTable1_DataUpdated sub. If you think what I currently have is OK, I can live with that. It just does not look kosher.

 

[Erel]

The total number of rows is not important. Only the number of visible rows. This will never be more than 10 or 20.

If this solution is fast enough then there is no reason to choose a more complicated solution. You will not gain anything.