Android Question Quick Search on Table view - fastest method

[GeoffT660]

I have a SqlLite table with up to 10K records that are presented in a tableview using Scroll2D. I am scanning a barcode and would like to learn the fastest method to find that record and position the tableview to that row. I also want to be able to search on different columns with it finding the 1st instance and then the next instance of the text for every scan. The value may not exist in which case I just want a value not found message. Please let me know the best method for executing the fastest search on this table.

 

[Erel]

1. Make sure that there is a primary key in the SQL table.
2. Make sure that there is an index on the column that you want to search.
3. When you populate the TableView, you should also fill a Map that maps between the primary key and the table view row.
4. Make a query with the search term, find the primary key. Use the above Map to find the table view row.

 

[GeoffT660]

1. Make sure that there is a primary key in the SQL table.
2. Make sure that there is an index on the column that you want to search.
3. When you populate the TableView, you should also fill a Map that maps between the primary key and the table view row.
4. Make a query with the search term, find the primary key. Use the above Map to find the table view row.

 

[GeoffT660]

Thanks so much. I've got 1 and 2 but am new to B4a. Are their any code examples of this method?

 

[Erel]

Can you post the code that fills the table?

 

[GeoffT660]

I just use dbutils with:

tblAssets.LoadSQLiteDB(sqlBN,"SELECT ASSETNO As Asset, MfgCatNo As Part, AssetSN As Serial, QtyOrd As Ordered, AsQty As Received, QtyOB As OutBound, QtyDEL As Delivered FROM wcUdfAsTbl WHERE JobNo = '" & mJobNo & "'",True)

When importing the table, I use:

Sub HandleFile(job As HttpJob)
   
    If job.Success = False Then
        ToastMessageShow("Error Downloading CSV File.", True)
        ProgressDialogHide
        Return
    End If

    Dim out As OutputStream
    out = File.OpenOutput(File.DirDefaultExternal,strTableName , False)
    File.Copy2(job.GetInputStream,out)
    out.close
   
    Log("finished writing csv file to external default directory")
   
    Dim ListOfMaps As List
    ListOfMaps.Initialize
    Dim existemap As Map
    existemap.Initialize
    Dim start, finish As Int
    start = 0
    finish = 9
       
    Dim readcsv As List
    Dim csv As StringUtils
    readcsv.Initialize
   
    'Read the saved csv file into a list
    readcsv = csv.LoadCSV(File.DirDefaultExternal, strTableName, ",")
   
    Dim columnlist As List
    Dim cols() As String
    columnlist.Initialize
   
    'read the first line of the list which contains the column names into cols variable
    cols = readcsv.Get(0)
   
    'place the column names into another list to be able to build a map with column names and field values
    For l  = 0 To cols.Length - 1
        columnlist.Add(cols(l))
    Next
    Dim cnt As Int
    'build a map with column names and field values
    For i = 1 To readcsv.Size - 1
        Dim m As Map
       
        m.Initialize
        Dim reading() As String
        'read each field value into an array of strings
        reading = readcsv.Get(i)
        For j = 0 To reading.Length - 1
            'place the each column name and each field value in a map
            m.Put(columnlist.Get(j), reading(j))
        Next
        'check if records exist, so we won't insert them again.
        'Dim existe As Int
        'existe = DBUtils.RecordExists(SQL1, "wcJobLog", m)
        'If existe > 0 Then
            'Log("Record already exists, skipping...")
        'Else
            'add the map into a list
            ListOfMaps.Add(m)
            cnt= cnt + 1
            Log(cnt)
        'End If
       
    Next
    If ListOfMaps.Size > 0 Then
        DBUtils.InsertMaps(SQL1, strTable, ListOfMaps)
        ProgressDialogHide
    Else
        ProgressDialogHide
    End If
       
End Sub

 

[Erel]

Create a global map object named TableMap.

Fill it in the For loop (before the end):

TableMap.Put(m.Get("PrimaryKeyColumnName"), ListOfMaps.Size)

TableMap will map between the primary key of each record and the row index.

 

[GeoffT660]

I've tried but am not getting this and all I get is "java.lang.OutOfMemoryError: Failed to allocate a 8388620 byte allocation with 8427048 free bytes and 8MB until OOM; failed due to fragmentation (required continguous free 8392704 bytes where largest contiguous free 1437696 bytes)". The import functionality is a separate activity from the Table Load/barcode scanning/search Activity. Do I add the TableMap on import and then File.WriteMap and File.ReadMap when I load the table through DbUtils. Basically I am lost so if you can please be more explicit on the entire process of creating the map and then loading and searching the table. Thanks.

 

[klaus]

It seems that you are downloading a csv file and convert it into a SQLite database.
Does the filling of the TableView from the SQLite database work?
You may have a look at the SQLiteLight examples (SQLiteLight2), link in my signature, where I use a list to memorize the rowids as PrimaryKeys. It doesn't use the TableView but the principle is the same.

 

[GeoffT660]

Thanks I'll take a look at those examples. Yes loading works fine, I load it through dbUtils as I showed early. Do I put the TableMap in dbutils? I tried creating a cursor iterating through each item to get the Row but, although it works, takes way too long.

 

[klaus]

I'm not sure you understood my questions.
This line tblAssets.LoadSQLiteDB(sqlBN,"SELECT ASSETNO As Asset, MfgCatNo As Part, ...
has nothing to do with DBUtils, it's a routine in the TableView class.
Do you fill the TableView with this routine?

 

[GeoffT660]

Sorry for my ignorance. Yes I do fill the tableview with that routine. Is there a different method/view I should be using for this quick search or find functionality. This has always been easy in other languages for Palm and WM and have already spent several days on this.

 

[klaus]

Do you filter databse in the SQL query with ... FROM wcUdfAsTbl WHERE JobNo = '" & mJobNo & "'",True) ?
How many results do you get?
I use this routine in You should read the rowids in a list then you know the roeid for each line in the table.

'Reads the database rowids in RowIDList
Private Sub ReadDataBaseRowIDs
    Private ResultSet1 As ResultSet
   
    If Filter.flagFilterActive = False Then
        ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM persons")
    Else
        ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM persons"  & Filter.Query)
    End If
   
    'We read only the rwoid and put them in the RowIDList
    RowIDList.Initialize                                'initialize the ID list
    Do While ResultSet1.NextRow
        RowIDList.Add(ResultSet1.GetInt2(0))        'add the rowid's to the RowID list
    Loop
    If RowIDList.Size > 0 Then
        CurrentIndex = 0                            'set the current index to 0
    Else
        CurrentIndex = -1                            'set the current index to -1, no selected item
        ToastMessageShow("No items found", False)
    End If
    ResultSet1.Close                                                'close the ResultSet, we don't need it anymore
End Sub

Extracted from the SQLiteLight2 example.

 

[GeoffT660]

No I'm not filtering the database with 6000+ records and I've been working with the same example but still can't figure how I would lookup a part # or a Serial # and then move it to the row with that part # or Serial # when all I have is the row ID related to a numeric key field. I must be missing something and wish there were just a simple search or find feature.

 

[GeoffT660]

Thanks for all your help trying to get me to understand this concept but I don't think I'm competent enough in B4a to grasp this. I will go ahead and post on the Job Board and see if I can get someone to create this and then will donate it back to the forum if this fast search with Tableview is even possible. I can see plenty of opportunity if I'm not using a table or list that I have to find and move to a row but my app has always had that and I find it useful. All I am really looking for is a way to search (in under a second) on different columns (fields) in any table with the value supplied. Something like RowNum = SearchVal("Table","Column","Value") which would return the rownum in the table that I can set the position to and supply a -1 if the item is not available. Please let me know if this is not possible.

 

[klaus]

Do you really have 10K records in the TableView?
I would search in the database if there are records and display those.

Or, filter the database and save the rowids in a List.
Attached a small demonstartor.
Enter 2 or more characters and press Go.
This will search in the database for cities containing these letters and then you can jump to the different rows.
I made a small change in the Table module, I commented line 929:
'''' Return ' comment this line if you want to unselect a line

 

[GeoffT660]

Thanks again so much for your help but I did find an excellent solution provided by stevel05 that allowed me to search exactly as I had envisioned and finds the value or substring on any column and also does an incremental search. All searches take milliseconds on a table with 22,572 rows. I found this with a post on the Job Board where you can also find 2 samples. https://www.b4x.com/android/forum/threads/create-a-fast-search-function.85833/