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.
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…
Wouldn’t using ORDERBY, OFFSET, and LIMIT negate that? I’m no database expert, just what I’ve used in implementations in the past.
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.
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.
[quote=376803:@Dave S]60,000 records… first off… NO user is going to scroll thru 60,000 records…
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.