I understand the rs.MoveFirst, etc. commands. But I want to load a recordset and then go directly to, let’s say, the fifth record, or perhaps to the record that has the primary field value of “123”.
As far as I can tell, the only way to do this is to load all the primary keys into an array, go to the line in the array I want, and then do a SELECT to get the actual data from the database.
This seems extremely inefficient, doing a disk read every time I want data, and especially because I can make all the data sit in the recordset.
So my question: Is there a way to go to a specific record in a recordset without paging through from the beginning?
I ran into this quite a bit when I converted my VB6 product to Xojo (RS at the time). I just had to get over it, because as you said, a lot of places in the VB6 code, I had loaded up recordsets, then just worked from the recordset. Took some getting used to, but I finally arrived at a place where the performance is acceptable and I just do SELECTs when I need another record.
it’s the most annoying thing I never had with a so called “database” program : not beeing able to go to a specific record
come on xojo we are in 2014 ? you just dropped 10.6 support it’s time to support a function wa have in almost every database program except your ? a recordset.moveto please ?
is nice sql is fast, but what if I want to get a recordset with 1 million records , even 100000 ? wait an hour each time I open the list ?
einhugur has a nice datagrid class that supports a infinite listbox. but without a moveto function it’s quite useless
the only database that’s worth it is valentina, and the developper wrote a moveto function in it at the beginning. nice.
Most database servers do not support bi-directional cursors and XOJO takes the correct approach in doing the same with it’s RecordSet, as to do otherwise would result in very poor performance.
And in any case using ‘Go To’ style functions on a RecordSet is not the best way to populate a virtual listbox such as the one you are referring to, since you would basically end up delivering the same poor performance of a standard listbox in such cases - you’d be loading the same amount of data, but just showing a few rows.
As for Valentina, the few times I run serious stress tests on it, it died a horrible death, so I would not even consider it for my projects.
Writing your own MoveTo is easy, and much faster than you think. I did it for the examples that come with my Data-On-Demand ListBox, and basically just calculated the shortest distance between the current record and the target record and started from there. So if are on index 1001 and want to get to 1000, the shortest distance is to start from where you are and call MovePrevious. But if you want to get to index 2 then the shortest is to call MoveFirst, then MoveNext twice.
By the way, using this technique with Data-On-Demand ListBox and a 5 million record SQLite database, the data will display instantly and give you smooth scrolling. The performance is certainly not poor.
[quote=57353:@Kem Tekinay]Writing your own MoveTo is easy, and much faster than you think. I did it for the examples that come with my Data-On-Demand ListBox, and basically just calculated the shortest distance between the current record and the target record and started from there. So if are on index 1001 and want to get to 1000, the shortest distance is to start from where you are and call MovePrevious. But if you want to get to index 2 then the shortest is to call MoveFirst, then MoveNext twice.
By the way, using this technique with Data-On-Demand ListBox and a 5 million record SQLite database, the data will display instantly and give you smooth scrolling. The performance is certainly not poor.[/quote]
really nice example your listbox
only drawback is that only movenext is supported by real databases plugins.
so for example a database like postgres that does not have a moveprevious and is awfully slow if you jump one record back and are at the middle of the list.
because you may have a recordset with 1 million or more rows … will take a huge time to put them in a listbox, and even more if you are on a distant network.
Yes, it can be done, but involves some creativity and copying data to some local structure. You’re also better off querying in chunks and refreshing when you need to display something that is not in the current chunk.