Android Question CustomListview and Sql, my code is slow.

[Sub7]

Hello, i am noticing an extremely slow response while executing the following pseudocode.
Every search can pontentially hold more than 1000 results, if i load 1000 rows at begin the app become irresponsive so for that at begin when i start the activity, i execute the query to populate the customlistview with a limit set to 20.
After scrolling down and reaching the bottom i load more 20 rows by increasing the query limit.
It works, but is damn slow.

Sub Globals
   Private queryLimit As Int = 10
End Sub

Sub Activity_Create(FirstTime As Boolean)

   Activity.LoadLayout("listview")

   lst_customlistview.Initialize(Me, "lst_customlistview")
   Activity.AddView(lst_customlistview.AsView, 0, 60dip , 100%x, 100%y -60dip)

fetchDB(queryLimit, False)
End Sub


Sub fetchDB(query_Limit As Int, loadMore As Boolean)

     Dim quote_text As String
     If loadMore = False Then
  cursor2 = Main.SQL1.ExecQuery("SELECT ID, fruit, icecream, snacks, starred FROM tablename WHERE fruit LIKE '%" & Main.keywords &"%' OR icecream LIKE '%" & Main.keywords &"%' ORDER BY ID DESC limit "&queryLimit&";")
     Log("True!")
     Else
     ProgressDialogShow2("Loading more results please wait....", False)
     cursor2 = Main.SQL1.ExecQuery("SELECT ID,fruit, icecream, snacks, starred FROM tablename WHERE fruit LIKE '%" & Main.keywords &"%' OR icecream LIKE '%" & Main.keywords &"%' AND id > "&queryLimit&" ORDER BY ID DESC limit "&queryLimit&";")
     Log("false!")
     End If
 
     For i = 0 To cursor2.RowCount - 1
     cursor2.Position = i

     quote_text = cursor2.GetString("fruit")
 
     If quote_text.Length < PreviewMaxLenght Then
     quote_text = quote_text.SubString2(0,quote_text.Length)&"....continue reading"
     Else If quote_text.Length > PreviewMaxLenght Then
     quote_text = quote_text.SubString2(0,PreviewMaxLenght)&"....continue reading"
     End If

   
      Header.Initialize("{White}{B}data: "&cursor2.GetString("snacks")&" - "&cursor2.GetString("starred")&"{B}{White}")
       Header.Color2(Colors.White,"{White}")
       Header.Style2(Header.STYLE_BOLD, "{B}" )


      Entry.Initialize("{Grey}data:"&cursor2.GetString("fruit") &" / data:"&cursor2.GetString("fruit")&"'' / data:"&cursor2.GetString("icecream")&CRLF&"Date:"&cursor2.GetString("snack")&"{Grey}")
       Entry.Color2(Colors.Green,"{Green}")
       Entry.Color2(Colors.RGB(221,221,221),"{Grey}")
       Entry.Style2(Entry.STYLE_BOLD, "{B}" )
   
   
           Dim p As Panel
           p.Initialize("")
           p.Color = Colors.ARGB(255,0,0,0)
           lst_customlistview.Add(p,75dip, 10)
           p.LoadLayout("clvlayout")
           Label1.Color = Colors.RGB(0,0,0)
           Label2.Color = Colors.RGB(0,0,0)
           Label1.Text = Header
           Label2.Text = Entry
           Label1.Typeface = Typeface.LoadFromAssets("font_1.otf")
           Label2.Typeface = Typeface.LoadFromAssets("font_2.otf")

Next
if loadmore = True Then 
ProgressDialogHide
End if

End sub

When the listview position reach the bottom i execute this sub

Sub lst_customlistview_AddItems
  Log("AddItems")
  queryLimit = queryLimit + queryLimit
  fetchDB(queryLimit, True)
End Sub

This sub is inside customlistview class:

Private Sub SV_ScrollChanged(Position As Int)
  If Position + sv.Height >= sv.Panel.Height Then
  If DateTime.Now > lastAddItemsTime + 200 Then
  lastAddItemsTime = DateTime.Now
  CallSub(CallBack, EventName & "_AddItems")
  End If
  End If
End Sub

 

[Erel]

Test performance in Release mode.

You need to find out which part is slow. Note that there is no reason to load two new fonts for each item. You can load them once.

Which version of B4A are you using?

 

[keirS]

You can do the query for the next 20 records in the background using ExecQueryAsync. If you called this straight after the first query the results would be ready when the user scrolls down. You should find some other way rather than using LIMIT as you are not speeding up the query time as SQLite limit works by generating the whole result set and then just returning the results to the given limit. It does not include the limit clause in it's optimization. Also make sure you have an index on the ID row which is descending.

 

[Sub7]

Thanks both of you,
beside loading the fonts only once the query execution time is acceptable (120ms) considering that i have up to 120000 rows in the db.
There is an index on the row ID it is the first thing i checked but thanks for remembering this.
One problem is this: queryLimit = queryLimit + queryLimit this doubles the querylimit at every execution, i want only increase it by 20 so: queryLimit = queryLimit + 20.
This little change has improved the performance but i still notice a slowdown when i reach 100/120 rows.
I always had problems with this but in other application i was loading images too, here i have only text i tought it would run faster. Maybe there is a better road than this.
@keirS i don't have any idea in how bypass the limit, any suggestion?

thanks

 

[keirS]

Without knowing the exact data structure it's difficult to say. I suspect your main problem is with the CustomListView. I have never used this but I can tell you that UltimateListtViewh will handle what you want to do very easily. I do something very similar to what you are doing with the customlistview and have no performance issues. That's loading 1000 records at a time.

 

[Sub7]

There is nothing special, an autoincrement, 3 text field, 1 int filed.
Since you can load 1000+ records without problems using customlistview then i want to investigate more.
After some changes i tried to set the limit to 500, app takes too much time to complete the task, (10 seconds) the query execution is fast, the rest no.

thx

 

[keirS]

There is nothing special, an autoincrement, 3 text field, 1 int filed.
Since you can load 1000+ records without problems using customlistview then i want to investigate more.
After some changes i tried to set the limit to 500, app takes too much time to complete the task, (10 seconds) the query execution is fast, the rest no.

thx

Sorry perhaps my post wasn't clear. I use UltimateListView not CustomListView. Instead of adding more and more panels when scrolling I would try and reuse the panels you have already added.

 

[Sub7]

Okay thanks

 

[Sub7]

I had to give up with clv, now i am using a simple listview it load 2000+ records in 2/3 seconds, i also changed the query type with ExecQueryAsync.

However if i add this code it take up to 25 seconds to finish (without 2/3), i don't know how to optimize this, i need to trim the text at some lenght.

      Dim PreviewMaxLenght As Int = 80
 
  If quote_text.Length > PreviewMaxLenght Then
  quote_text = quote_text.SubString2(0,PreviewMaxLenght)
  End If

 

[keirS]

You could try using the SQLite substr function in your SELECT statement to limit the length.

 

[Sub7]

The following query works perfectly on the ide i use to work with sqllite db's.

Main.SQL1.ExecQueryAsync("SQL", "SELECT ID, SUBSTR(fruit,1,30), icecream, snacks, starred FROM table WHERE fruit LIKE '%" & Main.keywords &"%' OR fruit LIKE '%" & Main.keywords &"%' ORDER BY ID DESC", Null)

But it closed and crash the app without any error in log, any idea?

Thanks

 

[Sub7]

SELECT ID, SUBSTR(fruit,1,30) as ALIAS.

Problem solved, thanks for all the help.