Listbox to Listbox move puzzle

[davegri]

ListBoxParadox.zip



For large selections, the move time from one listbox to the other varies greatly, depending on how the rows are selected. For example, if manual selection of all 10000 rows is made, the move (clicking the > or < ) will complete in about 5 seconds.
However, if the selection is made via the 'Select All' button, not only will the selection take a long time, but now the move (< or >) will take at least 60 seconds instead of the previous 5 seconds. Both moves use the same code (the > button).
Why would this be so?

[Micron]

I'd say it's because you're looping through 10K rows to select them, then probably looping over again to move them. Manually selecting them avoids the first loop. Just a theory. I suspect you're not looping over an array but over the controls instead (I mean their item lists). You might get a speed gain by copying selected items into an array. I'd also see if there's a faster way to create an array/collection/dictionary other than looping over the selected items when all are selected. Perhaps make the 2nd control bound to a query that returns everything instead.

EDIT - going to leave my reply as is for now. I just noticed that you posted a db copy.

[Micron]

So yeah, as long as the lists are not value lists I think I'd just set the controlsource property and let a query populate each control when moving the entire list.

[davegri]

So yeah, as long as the lists are not value lists I think I'd just set the controlsource property and let a query populate each control when moving the entire list.

I was doing it that way and it was VERY fast for even 65000 rows.

However, that's not the answer that I am seeking. I'd like to know why the exact same code that moves the rows is 10 times faster when the source rows are selected manually versus selection by iterating code. You would think that once selected by either method, the move time would be equal. However, it is not.

I ran into this behavior when developing and testing and am just curious if anyone knows why it might be so.

[Micron]

All I have is the theory I posted. Putting that therory another way, 10K rows are already selected in a gui vs having to select them by looping over them in code. I suspect that pre-selecting has already put them into some sort of memory space, be that a collection or array or something similar. Code isn't part of the gui. (end of theory).

On my laptop, the transfer process takes 72 seconds in case you're interested in performance elsewhere.

[davegri]

All I have is the theory I posted. Putting that therory another way, 10K rows are already selected in a gui vs having to select them by looping over them in code. I suspect that pre-selecting has already put them into some sort of memory space, be that a collection or array or something similar. Code isn't part of the gui. (end of theory).

That does seem plausible. Something is obviously different after the selections.

[CJ_London]

Micron is correct. There are even comments in the code saying this will be slow over 3500 records.

' If fcnTooBig(LB_Left) Then
' If MsgBox("Selecting over 3500 rows may be too slow for your liking." & vbCrLf & vbCrLf _
' & "Instead of 'Select All', you can manually select fewer rows with multipe moves." & vbCrLf & vbCrLf _
' & "Do you want to continue with the large select?" _
' , vbQuestion + vbYesNo, " C O N T I N U E ?") = vbNo Then
' Exit Sub
' End If
' End If

Looking at the code, the method used for selecting all is to loop through the listbox items and changing the selected property from false to true. Which as he says is going to be a lot slower that the GUI

This looks like fairly old code you have got from somewhere. A better method is to use a datasheet subform and use sql. You would also have a separate table called say tblSelected and populate that with the record's PK. Your recordsource for the left form would then left join to that table - not null means selected, null means not selected. Datasheets don't quite have the same functionality as an extended multi select listbox although they can be programmed in, but they have other benefits - for example if you wanted to scroll down to the 'w's is easy with a datasheet, try doing that with the listbox.

There are better ways to select from a table of 10,000 names if that is a requirement rather than just curiosity calling.

[davegri]

Thanks to all for insights. I'll probably post an enhanced DB to the 'Sample Databases' forum soon with code that mostly avoids time consuming looping over the listboxes.

[orange]

Here's a link to a youtube video on Moving To/From Listboxes This is a resurrection of an old video from Datapig Technologies.
I added the link because Moves Listbox to/from Listbox is quite common. Will be watching for davegri's 'Sample Database'.

[davegri]

Here's a link to a youtube video on Moving To/From Listboxes This is a resurrection of an old video from Datapig Technologies.
I added the link because Moves Listbox to/from Listbox is quite common. Will be watching for davegri's 'Sample Database'.

I remember the datapig website. Video tutorials from before YouTube had them all.
My technique is somewhat like Mike Alexander's in that I use a Flag field in the table to determine which listbox (if either) to show the table rows.

[orange]

Hi Dave,

Here is a link to a thread where I mentioned the loss of DataPig Technologies videos. It is interesting because my initial concern was that DatabaseAnswers.org (variety of datamodels) was no longer online. Happy to say that we worked with Crystal Long/strive4peace to get the DataPig videos resurrected and hosted on her site.