List View with many records

[cirollo]

Hi!

I'm having a real big problem.
I have a listview that show data from customer table
For testing purpose I used approx 100 record.

Going to my customer, the table was filled with 2940 records.
Each record has 15 fields
When I open the ListView the process is closed and the program crashed

what could be????

 

[thedesolatesoul]

Does it take more than 5 seconds to fill the listview with 2940 records?
You can try to turn the debugger off, that should make the process faster.

 

[cirollo]

the debugger is off

i compiled the project in release mode

the problem is that when I click on the button that should load the activity with the listview on it, it freezes, I cannot see the listview because the process is closed and all the application with it!

 

[thedesolatesoul]

What are you using to load the listview with the records?
Maybe DoEvents may help in keeping the application responding, but will be even slower.
Maybe you should add only the first 1000 records initially.
Do you have some code you could post?

 

[cirollo]

here's the code

this is the code under the button to open the listview

Sub BtnRicerca_Click
   ' componiamo prima la stringa SQL che conterrà la Select
   If TxtCodDes.Text <> "" Then
      TxtCodDes.Text = TxtCodDes.Text.Trim &"%"
      If RadioButton3.Checked=True Then
         Main.stringa_SQL="SELECT IdCli, Ragsoc, Status FROM Clienti where IdCli LIKE " & "'" & TxtCodDes.Text &"'"
      Else
         Main.stringa_SQL="SELECT IdCli, Ragsoc, Status FROM Clienti where Ragsoc LIKE " & "'" & TxtCodDes.Text &"'"
      End If
   Else
      If RadioButton1.Checked=True Then
         Main.stringa_SQL="SELECT IdCli, Ragsoc, Status FROM Clienti where Giorno = " & "'" & SpGiorno.SelectedItem &"'"
      Else
         If RadioButton2.Checked=True Then
            Main.stringa_SQL="SELECT IdCli, Ragsoc, Status FROM Clienti where Iniziale= " & "'" & SpLettere.SelectedItem &"'"
         Else
            Main.stringa_SQL="SELECT IdCli, Ragsoc, Status FROM Clienti where IdCli <> " & "''" 'tutti i clienti
         End If
      End If
   End If
   ' vediamo se devo mostrare tutti i clienti o solo quelli dell'agente
   Dim cur As Cursor
     cur = SQL1.ExecQuery("SELECT IdAge, FiltroCli FROM Impostazioni" )
     For i = 0 To cur.RowCount-1
         cur.Position = i
      If cur.GetString("FiltroCli") = "T" Then
         Main.stringa_SQL=Main.stringa_SQL &" order by Ragsoc"   
      Else
         Main.stringa_SQL=Main.stringa_SQL &" and IdAge =" & "'" & cur.GetString("IdAge") &"'"  &" order by Ragsoc"
      End If
   Next
   cur.Close

   If SQL1.ExecQuerySingleResult("SELECT count(IdCli) " &Main.stringa_SQL.SubString(29)) = 0 Then
      Msgbox2("Non ci Sono Clienti che soddisfano i criteri di Ricerca Impostati!",Main.nomeprog,"","Ok","",LoadBitmap (File.DirAssets, "warning_256.png"))
   Else
      StartActivity(RicercaClienti)
   End If
End Sub

and this is the activity with the listview code

Sub Activity_Create(FirstTime As Boolean)
   Activity.LoadLayout("RicercaClienti")
   ListView1.Initialize("ListView1")
   ListView1.TwoLinesAndBitmap.ItemHeight = 70dip
    ListView1.TwoLinesAndBitmap.Label.TextSize = 20
   ListView1.TwoLinesAndBitmap.Label.TextColor = Colors.Black
   ListView1.FastScrollEnabled = True
   ListView1.Color = Colors.White
   ListView1.ScrollingBackgroundColor = Colors.Transparent
   Dim cur As Cursor
   'Msgbox(stringa_SQL,"")
     cur = SQL1.ExecQuery("" &stringa_SQL)
   Dim Bitmap1 As Bitmap
   Dim status As String
   For i = 0 To cur.RowCount-1
         cur.Position = i
      status = "N"
      If SQL1.ExecQuerySingleResult("SELECT count(IdPar) FROM PARAPE where IdCli= " & "'" & cur.GetString("IdCli") &"'") > 0 Then
         status = "P" 'partite aperte
      End If
      If cur.GetString("Status")="B" Then
         status = "B"
      End If
      'associamo la bmp giusta
      If status = "N" Then
         Bitmap1.Initialize(File.DirAssets, "Circle_Green.png") 'normale
      End If
      If status = "P" Then
         Bitmap1.Initialize(File.DirAssets, "Circle_Yellow.png") 'partite aperte
      End If
      If status = "B" Then
         Bitmap1.Initialize(File.DirAssets, "Circle_Red.png") 'bloccato fido
      End If
         
      ListView1.AddTwoLinesAndBitmap(cur.GetString("IdCli"),cur.GetString("Ragsoc"),Bitmap1)
   Next
   Activity.AddView(ListView1, 0, 0, 100%x, 90%y)
End Sub

I'm waiting some hints to get this trouble solved ;-)

 

[thedesolatesoul]

It would be useful to get the Logs as well. I didnt realize bitmaps were involved. We need to get the exact reason for crashing with the Logs.

I can see that you are executing another query in a for loop. Depending on the number of records, this could get difficult.
I am not too good with databases, maybe there is a way to optimise the query.

One thing I can suggest is to move out the Bitmap1.Initialize from the loop. You have only 3 bitmaps you do not need to load them from assets each time. If you move them out to bmpGreen, bmpRed, bmpYellow you will gain a lot of speed.

Also, you should put a hard limit on the number of records to be added using Min(1000, cur.rowcount-1) since at some point you will have too many records.

 

[NJDude]

I would change this part of the code like this:

'associamo la bmp giusta        
If status = "N" Then

   'normale
   ListView1.AddTwoLinesAndBitmap(cur.GetString("IdCli"),cur.GetString("Ragsoc"),LoadBitmap(File.DirAssets, "Circle_Green.png"))

End If

If status = "P" Then

   'partite aperte
   ListView1.AddTwoLinesAndBitmap(cur.GetString("IdCli"),cur.GetString("Ragsoc"),LoadBitmap(File.DirAssets, "Circle_Yellow.png"))


End If

If status = "B" Then

   'bloccato fido
   ListView1.AddTwoLinesAndBitmap(cur.GetString("IdCli"),cur.GetString("Ragsoc"),LoadBitmap(File.DirAssets, "Circle_Red.png"))

End If

 

[thedesolatesoul]

NJ, why load the bitmaps from file 2000 times?

I would do: (outside the loop)

Dim bmpGrn as Bitmap
Dim bmpYlw as Bitmap
Dim bmpRed as Bitmap

bmpGrn.Initialize(File.DirAssets, "Circle_Green.png") 'normale
bmpYlw.Initialize(File.DirAssets, "Circle_Yellow.png") 'partite aperte
bmpRed.Initialize(File.DirAssets, "Circle_Red.png") 'bloccato fido

And within the loop:

If status = "N" Then
            Bitmap1 = bmpGrn

Else If status = "P" Then
            Bitmap1 = bmpYlw

Else If status = "B" Then
            Bitmap1 = bmpRed
End If
ListView1.AddTwoLinesAndBitmap(cur.GetString("IdCli"),cur.GetString("Ragsoc"),Bitmap1)

 

[NJDude]

I think your code and mine does the same thing, but my point was NOT to initialize them everytime like he put it in his code.

I've used the code I posted on several apps that load a ton of pics on a list view, runs fast.

 

[cirollo]

I'll try

ok guys!

this evening I'll try your code and give you a response....

for now...thank you!

 

[thedesolatesoul]

hmm...I would have thought reducing disk activity will make it faster, but since NJDude has the advantage of experience i will leave that

cirollo,
you now have the obligation to let us know whether NJDude's code is faster or mine ...if it does solve your problem!

 

[cirollo]

TRIED NJDUDE but doesn't work!

as topic, tried your suggestion but doesn't work!

can I load first 500 records and when scrolling the others????

but if i put in the stringa_SQL the first part of the record, for example select..... where ragsoc like 'imp%' it works

the problem could be the SQL sintax or the number of records?

 

[cirollo]

TRIED THEDESOLATEDSOUL doesn't work!

What else I can try????

 

[thedesolatesoul]

you can try and optimise your query.

Or you can load the first 1000 items at Activity create and load the remaining using a timer (500 records on each tick). Keep track of how many records were loaded in a variable. And add a 'Loading...' indication somewhere.

Another thing you can try is using a scrollview instead and doing a 'Pull to load more'. Every time the user reaches the end of the scrollview you can load 500 more records.

 

[Penko]

I'd say I agree with the two colleagues and their suggestions.

If I were you, I'd go for the solution to add additional records when the user is at the bottom of your listview. Even the Market(oops Play) application works this way.

Another approach is what you said - to do paging. What are you doing with the listview? Is it handling events?

You may use a HTML table too. Check what has been written Here http://www.b4x.com/forum/basic4android-getting-started-tutorials/8475-dbutils-android-databases-now-simple.html#post47359 (by Erel).

Unfortunately, there is much to experimentiate before you find the fastest and cleanest solution.

P.S You are aware of what you are trying to achieve. Put "LIMIT" to queries which are expected to return a definite number of rows. This would speed up the situation when working with large amounts of data.

P.S.2 What of the main rules when working with database is that you fill the database with as much rows as it will be usually working with. It is not rare that everything works correctly on your server and when you release it for customer use, it is clumsy.

 

[cirollo]

I found the problem

If I remove the lines regarding the second select inside my loop:

   For i = 0 To cur.RowCount-1
         cur.Position = i
      status = "N"
'      If SQL1.ExecQuerySingleResult("SELECT count(IdPar) FROM PARAPE where IdCli= " & "'" & cur.GetString("IdCli") &"'") > 0 Then
'         status = "P" 'partite aperte
'      End If
      If cur.GetString("Status")="B" Then
         status = "B"
      End If
      'associamo la bmp giusta
      If status = "N" Then
   '      Bitmap1.Initialize(File.DirAssets, "Circle_Green.png") 'normale
      'normale
         Bitmap1 = bmpGrn
      End If
      If status = "P" Then
   '      Bitmap1.Initialize(File.DirAssets, "Circle_Yellow.png") 'partite aperte
         'partite aperte
          Bitmap1 = bmpYlw
      End If
      If status = "B" Then
'         Bitmap1.Initialize(File.DirAssets, "Circle_Red.png") 'bloccato fido
         'bloccato fido
            Bitmap1 = bmpRed
      End If
      ListView1.AddTwoLinesAndBitmap(cur.GetString("IdCli"),cur.GetString("Ragsoc"),Bitmap1)
   Next
   Activity.AddView(ListView1, 0, 0, 100%x, 90%y)

it works! but i need that information, so i ask you:

- is there any other method to do the same?
- penko solution to load more records when at the bottom of the list is good, but I need an example, how can I check i'm at the bottom???

 

[Penko]

Check the Wiki page: ScrollView - Basic4android Wiki

You may also check Erel's ScrollView tutorial: http://www.b4x.com/forum/basic4android-getting-started-tutorials/6612-scrollview-example.html

At the very first glance I see there is a "scrollPosition" property which is settable. I haven't worked myself but you may try using it to check when the user has reached the bottom.

You are using ListView but it should have something for the Scrolls as well.

 

[cirollo]

I need to fix this issue!

Hello Guys!

I'm still here stucked in this problem....

I have 2 tables: clienti (customers) and parape (invoice that need to be paid!)

each customer can have one or more parape records (one or more payment he has to do!)

when I call the customer list to choose the record to work with, I use a listview, as you can see in my previous reply...

First, I tried to do this way:

populating the listview and record by record, do a second sql query to check if there are records related to that customer in parape
It crashed the application, due to timeout problem, I suppose.

Then, I removed the second query and all worked.
But I need to display the count of records in parape of each customer.
I tried to change the SQL query in this way:

"SELECT Clienti.IdCli, Clienti.Ragsoc, Clienti.Status, count(Parape.IdPar) FROM Clienti LEFT OUTER JOIN Parape ON Clienti.IdCli=Parape.IdCli where Clienti.IdCli <> " & "''"

but the listview shows me only the last record (the last customer).
If I remove the count statemente I see all the customers but, if they'll have more than one record in parape I suppose to find duplicate items in the listview.

Any suggestion? I need the count of parape records of each customer.
thanks!

 

[cirollo]

HI! nobody uses joins???

.

Hello Guys!

I'm still here stucked in this problem....

I have 2 tables: clienti (customers) and parape (invoice that need to be paid!)

each customer can have one or more parape records (one or more payment he has to do!)

when I call the customer list to choose the record to work with, I use a listview, as you can see in my previous reply...

First, I tried to do this way:

populating the listview and record by record, do a second sql query to check if there are records related to that customer in parape
It crashed the application, due to timeout problem, I suppose.

Then, I removed the second query and all worked.
But I need to display the count of records in parape of each customer.
I tried to change the SQL query in this way:

"SELECT Clienti.IdCli, Clienti.Ragsoc, Clienti.Status, count(Parape.IdPar) FROM Clienti LEFT OUTER JOIN Parape ON Clienti.IdCli=Parape.IdCli where Clienti.IdCli <> " & "''"

but the listview shows me only the last record (the last customer).
If I remove the count statemente I see all the customers but, if they'll have more than one record in parape I suppose to find duplicate items in the listview.

Any suggestion? I need the count of parape records of each customer.
thanks!

 

[thedesolatesoul]

Try adding DoEvents after the query. This might keep the application alive but will be even more slower.
The 'Load more' solution is more complicated and I have not seen an example of it yet.
You should also think about how you want your user experience to be. Do you really want him scrolling through 2000 records? Can you not re-organise things, either alphabetically, chronologically or categorically?