I’ve read several topics on this subject but I can’t seem to find a straight forwards solution. What I’m trying to do (I think) is pretty straightforwards and probably very commonly done.
I have a listbox on a window, the contents of which comes from a MySQL database. When the user clicks a refresh button, I run a query against the database and populate the listbox. This works during testing but when in production, it is likely that there will be 5000 - 10,000 rows to add and I’m worried this will lock the UI for a few seconds.
I have a module that contains all of my database interaction code, it has a method which runs the database query and returns (as an array) the data to add to the listbox. How should I best run this method (and the code to update the listbox) using threads and timers?
When worrying about performance, I believe in that situation I would not loop through the data twice. i.e. 1 loop to build the array and then a 2nd loop through the array to consume the data. I wonder if it wouldn’t be a bit better to do something like:
Dim rs As RecordSet = MyDbModule.GetRecordsForListBox()
While Not rs.EOF
MyListBox.AddRow(rs.Field("field1").StringValue, rs.Field("field2").StringValue)
MyListBox.RowTag(MyListBox.LastIndex) = rs.Field("id").IntegerValue
rs.MoveNext
Wend
rs.Close
The code do perform the database interaction still resides in your database module. The difference is you are only looping through the data once.
I would try the above method on the amount of data that you are expecting in production, and if it is a problem at that point, then start looking into threads.
Also, as another general note… Typically users can not comprehend, consume or want to consume such mass amounts of data. Does your user really want to scroll through 10,000 records? Maybe a user interface change should be thought about? Just a general idea, many times there are specific implementations which must break general rules.
To help the user understand a long task is going to be performed, you can also do something like:
Me.Cursor = System.Cursors.Wait
// Long Running Code
Me.Cursor = System.Cursors.StandardPointer
(Me) in the above code being your dialog or window you are performing the action in. There are many other tricks that can be done in situations like these if you find it takes too long. I’ll let the more experienced in threads/long running methods speak up to those. But first, see if it is a real problem or not.
I agree with Jeremy. 10,000 records is a huge volume to display to a user.
Can you not limit your SQL selection process to display the data that he actually needs to see? Some ‘out of the box’ thinking may be needed here to interrogate your user’s needs to obtain a shortened version of the data. You can subsequently invoke a further drill-down from a double-click on a line item in the listbox to go deeper.
I also agree that it is better to populate the listbox directly following the db call. If the db call is in a module then extend the method to take the listbox as a parameter and then you can make the sql call and populate the listbox within the module method. An overloaded method would work here too.
Using threads for this is useless. The slowest part in getting the record set (all database classes are fetching their result sets synchronously). Looping over 10.000 of records is happening very fast (as long as you don’t do anything time consuming within the loop). So to put the loop over the record set within a thread is merely useless (I’ve been there, I’ve seen it).
What you can do is to not add the data to the listbox. Since you load the data into an array, just add the records of your array as RowTags and use CellTextPaint to draw the content of the visible rows.
I disagree with the Thread assessment. You certainly could put the data retrieval into a thread and then use a timer on the page where the listbox is to retrieve 100 rows at a time and display them.
I write the table data into a dictionary in the thread and use a timer to write the dictionary data into the listbox once the thread has finished running.
if there is a way to categorize the data (hopefully on multiple levels) the first thing that springs to mind is to use a hierarchical listbox… but that depends on what the end users needs are.
I’m not so sure about that. The data retrieval has two parts:
Database.SQLSelect sends the statement to the server, the server fetches the rows, and sends them back. This is an “atomic” process within the Xojo framework. Until the RecordSet is fully filled, the thread will be blocked.
Iterating over the RecordSet. This is where you could apply a Timer to update the UI.
The issue is, that the first part is the large part of the data retrieval when you fetch a lot of records. While developing this might not show so clearly, especially when the test database server is running on the same computer as the development environment.
A quick test loading 20.000 records with 14 columns (the German bank routing numbers), gave me this result (for a dedicated OS X server with MySQL community server installed and on a regular Ethernet cabled network):
Time consumption of Database.SQLSelect to Iteration over RecordSet is approximately 80% to 20%.
Any new update on this except Kem’s Data-On-Demand listbox ?
I recently discovered that one of the customer created a huge database , around 38000 to be more exactly, so before the populating code was in the open event of the listbox and it was ok, now due to that amount of data that has to be queried it takes even 5 min to query specially if it`s on a busy network, if the server is in the same pc it takes less but still a lot of tine and as you already guess, the interface is either frozen or missing until the listbox gets filled.
90% of the cases the server part is on another pc so you get the point , all the environment is MAC.
If you say the network itself is an issue, there isn’t much you can do on the Xojo side.
If your UI is responsive or not, the user has to wait.
It depends if your listbox does sorts, searches etc. but if possible, I would try to do the calculations on the DB, LIMIT the result set to small chunks and request the next chunk when the scrollposition starts to get near the bottom.
Well i assume that the network could face issues as well i`m not sure, as for the loading i was thinking as well to load it in small chunks as well so that i reduce the load anyway they will not read the whole table in the same time, from a first look the can see up to 7 records on a time so if i make chunks of 50 it will be fast enough to load the db without them to see the difference i guess, i will have to do some tests first .
Well, unless you do a lot of math and calculations in your Xojo App, I would start with chunks of 500-1000 otherwise you create new delays because you’re doing tons of queries.
And try to do most of the calculations the DB server so it arrives pretty much ready-to-use.
(And maybe you can avoid using the ListBox as a container for your data because it slows down with so many records. If possible, use dictionaries or a structure. And then fill the ListBox with what you actually need to display.)