Android Question Flexible table class slow sort

[RB Smissaert]

Using this class a lot and found the table sort (clicking the column header) quite slow, when the number of rows gets more than a few thousand.
Found that code like this makes it a lot faster:

Sub ClearStringNull(strNull As String, strReplace As String) As String
 
 If strNull <> Null Then
  Return strNull
 Else
  Return strReplace
 End If
 
End Sub

Public Sub SortTable(col As Int, bAscending As Boolean)

 Dim i As Long
 Dim lUB As Long
 Dim DataSorted As List
 
 lUB = Data.Size - 1
 
 If mbFastSort Then 'this is a lot faster, about 25x
  DataSorted.Initialize
  For i = 0 To lUB
   SortCursor.Position = i
   'can get here:
   'net.sqlcipher.StaleDataException: Access closed cursor
   'to avoid this need to make sure that the cursor is not closed
   '-------------------------------------------------------------
   DataSorted.add(setSortPairString(i, ClearStringNull(SortCursor.GetString2(col), "")))
  Next
  DataSorted.SortType("strString", bAscending)
 End If
 
 ClearSelection
 debug_counter = 0

 If mbFastSort Then
  For i = 0 To lUB
   Dim tDS As SortString = DataSorted.Get(i)
   'DataTemp is populated in LoadSQLDB4, so keep using
   'the same original Data list with different sorts
   '--------------------------------------------------
   Data.Set(i, DataTemp.Get(tDS.IDX))
  Next
 Else
  SelectionSort(col, bAscending)
 End If

 RefreshTable
 
End Sub

Sub setSortPairString(lIDX As Long, strValue As String) As SortString
 
 Dim tSS As SortString
 
 tSS.initialize
 tSS.IDX = lIDX
 tSS.strString = strValue
 Return tSS
 
End Sub

Sub setSortPairLong(lIDX As Long, lValue As Long) As SortLong
 
 Dim tL As SortLong
 
 tL.initialize
 tL.IDX = lIDX
 tL.lLong = lValue
 Return tL
 
End Sub

Sub setSortPairDouble(lIDX As Long, dValue As Double) As SortDouble
 
 Dim tD As SortDouble
 
 tD.initialize
 tD.IDX = lIDX
 tD.dDouble = dValue
 Return tD
 
End Sub

Public Sub SortTableNum(col As Int, bAscending As Boolean)
 
 Dim i As Long
 Dim lUB As Long
 Dim DataSorted As List
 Dim strColumnDataType As String
 
 'strColumnDataTypes is set in LoadSQLDB4
 '---------------------------------------
 strColumnDataType = strColumnDataTypes(col)
 General.RunLog("SortTableNum, strColumnDataType: " & strColumnDataType)
 
 lUB = Data.Size - 1
 
 If mbFastSort Then 'this is a lot faster, about 25x
  DataSorted.Initialize
  If strColumnDataType = "I" Then
   For i = 0 To lUB
    SortCursor.Position = i
    DataSorted.add(setSortPairLong(i, SortCursor.GetInt2(col)))
   Next
   DataSorted.SortType("lLong", bAscending)
  Else
   For i = 0 To lUB
    SortCursor.Position = i
    DataSorted.add(setSortPairDouble(i, SortCursor.GetDouble2(col)))
   Next
   'this will take a bit less than half of the total time of SortTableNum
   '---------------------------------------------------------------------
   DataSorted.SortType("dDouble", bAscending)
  End If
 End If
 
 ClearSelection

 debug_counter = 0
 
 If mbFastSort Then
  If strColumnDataType = "I" Then
   For i = 0 To lUB
    Dim tL As SortLong = DataSorted.Get(i)
    'DataTemp is populated in LoadSQLDB4, so keep using
    'the same original Data list with different sorts
    '--------------------------------------------------
    Data.Set(i, DataTemp.Get(tL.IDX))
   Next
  Else
   'will be column type real - "R"
   For i = 0 To lUB
    Dim tD As SortDouble = DataSorted.Get(i)
    'DataTemp is populated in LoadSQLDB4, so keep using
    'the same original Data list with different sorts
    '--------------------------------------------------
    Data.Set(i, DataTemp.Get(tD.IDX))
   Next
  End If
 Else
  SelectionSortNum(col, bAscending)
 End If
 
 RefreshTable
 
End Sub

SortCursor is the cursor supplied to LoadSQLDB.
strColumnDataTypes is a string array and comes from LoadSQLDB as well.

Maybe this could be added to the flexible table class.


RBS

 

[klaus]

Can you please post the Table.bas file?

 

[RB Smissaert]

Can you please post the Table.bas file?

Will post in a bit when I am back home.
My Table.bas file has been customized quite a bit.

RBS

 

[RB Smissaert]

Can you please post the Table.bas file?

Attached Table.bas and also a general purpose module that holds some procedures used in Table.bas.
In Table.bas I am using Public Sub LoadSQLiteDB4, so that is the one that works with the fast sort.

RBS

 

[RB Smissaert]

Can you please post the Table.bas file?

Had a look if it could be made faster by using arrays instead of lists, thinking that maybe sorting simple values rather than
types might be faster. This used a combined quicksort and insertionsort, producing an index array. A lot more code but no faster
really, so I think this might be the best way.

RBS

 

[klaus]

Thank you.
I haven't looked in detail your improvements yet.
I am about leaving for a week snowshoe hiking.
Will look at in february.

 

[RB Smissaert]

Thank you.
I haven't looked in detail your improvements yet.
I am about leaving for a week snowshoe hiking.
Will look at in february.

Have a nice hike.
Will do some more testing.

RBS