I have a listbox displaying records that match search results. It is multi-column displaying about 10 fields.
Works fine for most searches but since some users have 60,000 records a search for a single character or All Records takes a very long time, up to 20 seconds on some systems. It is not the search it is the fill part.
Because I hide the listbox while filling to speed things up the user is left completely in the dark while waiting making the wait seem even longer or causing user panic (Ctrl- Alt + Del It’s not responding, how do I get out of this?).
Refreshing the listbox every 1000 records looks good but the listbox is useless as you can’t select a record until it finishes anyway and it takes twice as long to fill. Displaying a Wait message box again slows things down.
Any ideas to make this user friendly.
PS. Is filling a row with an array quicker than filling cell by cell? I get inconsistent results, perhaps affected by data size or background programs.
I would start by showing a “Loading” indicator for the user, and disabling the Listbox and any other controls (as opposed to hiding it) which may interrupt the process.
You might also think about limiting your search parameters to only perform a search if the criteria is, say, three characters or more.
I’ve not seen a discernable difference in filling by Array or Cell as I haven’t done the legwork, but I would expect an array to be faster.
Another option is to paginate the data. Instead of filling 60000 rows, do 10000 with a page selector and use SQL’s OFFSET and LIMIT.
60,000 records… first off… NO user is going to scroll thru 60,000 records…
You best bet is to create a “paged” format… return say 100 records, and if they press page down, load another 100.
But even then… if it takes 20 seconds to read 60,000 records… it will take that long to jump to the last 100 records (at least in SQLite, other DB engines would be MUCH faster)
Now if you are ABSOLUTLY POSITIVE that the ROWID has no holes or gaps, you could use that as a selection key, this will speed the paging up, but you are not guarateed of the order…
All of this is from experience… I helped design a system that had a database of 3.4 million doctors and hosptials, and we used techniques similar to above… but we were also using Oracle and an array of very fast processors.
[quote=376803:@Dave S]Now if you are ABSOLUTLY POSITIVE that the ROWID has no holes or gaps, you could use that as a selection key, this will speed the paging up, but you are not guarateed of the order…
[/quote]
Wouldn’t using ORDERBY, OFFSET, and LIMIT negate that? I’m no database expert, just what I’ve used in implementations in the past.
Yes, but again the downfall is the ORDER IN WHICH THERE ARE INSERTED
1=A001
2=A005
3=A009
...
90=Q937
...
100=X932
101=A010 <----- limit 100 would not read this, even though it is within the 100 lowest values, but NOT within the 1st 100 records
I feel like there’s a solution here, but as I’m not extraordinarily well-versed in all of the potential choices for DB engine, I’ll just smile and nod.
First off hiding the vertical scroll bar while adding items drastically reduces the amount of time it takes to insert.
This code take 7.28 seconds to add 60,000 records.
dim start as integer = ticks
listbox1.DeleteAllRows
for i as Integer = 1 to 60000
listbox1.AddRow("Row " + cstr(i))
next
MsgBox cstr((ticks-start)/60) + " seconds"
Hiding the scroll bar makes it run 25 times faster and takes a total of 0.3 seconds.
[code] dim start as integer = ticks
listbox1.ScrollBarVertical = False
listbox1.DeleteAllRows
for i as Integer = 1 to 60000
listbox1.AddRow("Row " + i.ToText)
next
listbox1.ScrollBarVertical = true
It seems like this thread is getting very complicated and we don’t even have enough information!
@Craig Grech you say that you’re certain the slowdown isn’t from the listbox drawing because you do the famous listbox blink trick. That’s great, that means that the slowdown is entirely on the engine. So, let’s cut out Xojo from the mix for now.
How long does it take for you to pull up 60,000 records with your search query in a database tool like Navicat?
It shouldn’t take very long at all. If you get a slowdown here then it’s certainly the database at fault.
What does the code that populates the listbox actually look like?
It could be poor design is unnecessarily slowing down the population of the listbox.
Instead of jumping head first into the cost / benefit of using LIMIT and ORDER BY in the query, let’s start by ruling things out and trying to narrow down where the issue really is.
then that should be part of the selection criteria
SELECT * from myTABLE where xyz like "H%"
and while Tim is correct… I can tell you that 99% of slowdown will be any manipulation done after you have selected the records (regardless of the amount)… a 60,000 long recordset should be a few seconds… it what happens AFTER
Your right there no time is wasted retrieving the recordset.
If I debug, the code to load the recordset is over in less than a second.
Pausing at any point after that it is still busy adding rows to the listbox.
You might also want to look at the techniques in this thread. The piDog DataView control comes with a demo project that among other things has an example of using a SQLite database for data on demand.
And Kem’s Data On Demand listbox approach is also very fast.
The main point is you do NOT want to load the whole database into the listbox, even if the users thinks you are. You only want to get the data for the rows you want visible at any given instant.
What a weird thing for you to have experienced. I have a client project that loads 1-2 thousand records into a listbox from time to time, and I’ve never had to do any extra scrollbar workarounds.
To correct an issue where the listbox was taking multiple seconds to display all the records I employ the blink trick:
Listbox.Visible = false
for each record
addrow(record)
next
Listbox.Visible = true
Nothing to do with the scroll bar, but the result is now instant. It’s good to know that the scroll bar may be part of it, so I’ll keep an eye out for that in the future, thanks!
I would add that before you go looking into third party controls, I might recommend profiling the code to find out where the slowdown(s) may be.
Amazing!!
Hiding both scrollbars while loading reduced time to load 60,000 records by at least half on my machine - 7 seconds down to 3 seconds (not instant but much better).
Re previous comments about why I allow users to search by all records, it’s because they are a paranoid lot.
If you worked hard entering records for hours wouldn’t you like to check periodically to re-assure yourself that they are all there and congratulate yourself on the big list you’ve made? Of course you would.
We tend to page our data. So no way we’d let users see all those rose at the same time. Using some simple controls and simple SQL it’s very easy to show them say 500 at a time.
[quote=376803:@Dave S]60,000 records… first off… NO user is going to scroll thru 60,000 records…
[/quote]
I regularly scroll through 445000 records, and this number is going up each month.
But yes, it’s a convenient way to search for something, after having made a sql query.
