Please help me figure out what I’m doing wrong. I have a WebListBox with one column. I create a rowset from an Sqlite database, with 13,000 records of 1 field. When I initially tried to loading these records into the WebListBox, I discovered that it took 4 seconds to add 1000 records, 12 seconds to add 2000 records, and 25 seconds to add 3000. When trying to add 4000 records, I ran out of patience after 2 minutes!
So, I went to a “paging” arrangement, loading “pages” of 500 records, and clearing the WebListBox with the RemoveAllRecords method before loading a new page. So have been less than 4 seconds per page. However, I continue to see the non-linear, increasing time. Again loading Page 1 takes about 3 seconds. But then clearing out the WebListBox and adding Page 2 of 500 records takes 12 seconds. Third page takes 30 seconds. since I’m not adding records but rather I’m clearing and loading new records, the progressive time increase doesn’t make any sense. I’m sure I’m overlooking something.
It’s not the Sqlite query. The un-limited query returns all 13,000 records in just over a second. My paging queries use “limit” and “offset” but they don’t add an appreciable time to the query.
I’m using V2022R3.2_58445. I’ve tested on both Linux (Debian 11) and Windows 10. No differences.
I’ve tried using timing messages, but even the “beginning” time message does not appear in the browser until after the WebListBox has updated (after seconds after calling the paging function). So I imagine that there must be some updates batching going on between the browser and the server.
Here’s my code:
Method FetchPage(page as integer)
var UpperLimit as integer
var Offset As integer
UpperLimit = page * 500
Offset = ((page - 1) * 500) + 1
Var d As DateTime = DateTime.Now
messagebox("starting: " + d.Second.ToString)
var rs as rowset = session.LMISDatabase.SelectSQL("select EventID from Event order by EventID limit " + UpperLimit.ToString + " Offset " + Offset.ToString)
d = DateTime.Now
messagebox("done: " + d.Second.ToString)
SearchListBox.RemoveAllRows
While Not rs.AfterLastRow
SearchListBox.AddRow( rs.Column("EventID").StringValue)
rs.MoveToNextRow
Wend
This method on the window is called by my “Page Forward” button. I’d love to be able to load all 13,000 records if it took 5 seconds or less. Failing that, I’d like a paging arrangement that loads each page-worth of 500 records in less than 3 seconds.
Thanks, I will. However, I don’t think my problem is totally related to the number of records being pulled from the database. It occurs at as few as 500 records (of one integer field each) Or does the Datasource update the rows of a WebListBox faster than AddRow?
Can you point me to an example of using DataSource? I’m afraid I don’t fully understand it just from the documentation link you provided. Didn’t find a lot Googling.
I forgot to mention in my original post that I also tried some standard hacks when populating a data control; I tried setting the WebListBox disabled in the Inspector, loading the 500 records and then enabling the WebListBox from code. No change in timing or behavior. Same with setting the WebListBox not Visible before adding the records and then changing it to Visible. Not only did it not change the timing or behavior, I didn’t even see the WebListBox flicker.
Forget about the request for an example of Data Source. I just found “Listbox with a Data Source” in the Xojo Examples. Clear enough how it works now. I’ll try changing my code to use the Data Source approach and see if this solves my problem. Again, thank you,
Bear in mind that nothing is sent to the browser until the event finishes, so if you have
// delete all rows
// make not visible
for i as integer = 1 to 500
AddRow
next
// make visible
All of that is sent to the browser as one packet. By the time the browser refreshes the page, the weblistbox is still visible. Setting it not visible has no affect.
Also, those tricks are applicable to desktop apps, not web.
That’s not true in web 2. In the new WebListBox, when you use AddRow, it creates an in-memory SQLite database, adds all of the data that you add to that, basically setting up a basic data source. The problem when you have 13,000 rows is that all you’re doing is transferring the data from one to the other, and the listbox requests what it needs to display, which is usually about 100 rows to start.
It’s likely that the bottleneck really is the pulling and re-storing of all that data. Remember, with such a tight loop, nothing else can happen on the server. Another downside is that each of the sessions get their own in-memory database and the memory requirements are going to grow very quickly.
As I said, create your own data source and only pull the data it asks you for. You’ll be much happier with the results.