I'm currently using a DAO interface to connect to an access database. This database has a table with 27 columns and 650,000 entrys. The entrys are displayed in a listview. The program cannot load 650k entrys within a second, therefore it just dynamically
opens the recordset so I can "scroll through the database". The user is also able to sort the listview which will trigger a requery with filter and sort propertys.
So far so good. When I set a filter, it requerys within a second. However, when I try to SORT the entrys WITH a filter, it is extremely slow(between 10-120 seconds). There are huge differences between sorting ascending and descending. It is not the program
that causes it to be slow. It's access executing the query too slowly(tested it myself using access 2010).
My question to you guys it, if you know any alternative(MUST be standalone and support dynamic recordsets - not go through the whole table, lets me scroll through it) OR a way to optimize the access database to make it execute the sort faster.
Recoding is not an option. The three options are:
* Another standalone database
* Optimize the access database
* Fix the ado recordset
I did try using ADO. However, I was unable of opening it dynamically, kept needing 1 1/2 minutes to just open the recordset...