SQL Speed

[manofsteel00]

I have VB6 program that uses SQL queries and an ADO connection to connect to a MS Access 2002 database. The database contains on an average 40,000+ records. The first screen in the program loads the contents of the database to a listbox; obviously this takes a while. The users want to maintain the ability to see everything, but want it to load faster. Is there a way to load faster via using a stored procedure in Access or something of that nature? Any suggestions are welcomed. Thanks.

[RobDog888]

Access .mdb files do not support Stored Procedures. There are a few things you can do to make it appear as loading faster but not much else. Are you using ADO Command Objects and Forward-Only and Read-Only recordset? They are faster.

[Hack]

Although Rob has made about the only recommendation to speed things up that I can think of, even making his changes really isn't going to get you much.

Why in the world do your customers want to see 40,000 items in a listbox? That is insane. Not only will it take time to load, but it will also take time to find stuff. Is this database going to grow? A listbox has a limit of 32K

[sevenhalo]

The other thing is (on top of what's been mentioned), make sure you index the tables by the fields you use in your where clause. It'll slow your INSERT and UPDATE slightly, but your SELECTs and DELETEs get a huge boost.

[RobDog888]

Why not give them some options for filtering the data and seeing it all too. So at the startup you would load only the most recent data maybe and then if they want to look at it all they would use the filter to remove the filtering condition. After a while they may realize how slow it is to see everything and not use it or want it much.

[manofsteel00]

The program is for a cycle count (an audit for food). The user wants to be able to chose from multiple Pick slots, which is how they want to see the data. It just takes around two entire minutes to load the selection screen.

[Hack]

It just takes around two entire minutes to load the selection screen.

I can certainly understand that, but with that many records, I don't see how you are going to get around it.

What about growth? As I mentioned, if it gets too big you could wind up with a problem. You are only going to get 32K worth of data in a standard VB listbox.

If you foresee the number of items growing, I would encourage you to begin, now, to look around for a third party replacement for that listbox.

[sevenhalo]

One of the things you may be able to do is mimic a full search without grabbing all of the records. I don't know if this is possible in VB6, but only query the TOP 1000 initially and then if they scroll to the bottom, add the next 1000 (TOP 1000 WHERE field > 'LastValueInListBox') to the listbox and continue with that. You're still heavy on the DB end, but atleast your startup cost will be lighter.

[manofsteel00]

How could I implement the ADO Command Objects and Forward-Only and Read-Only recordsets?

[szlamany]

We stop loading all listbox and combo boxes at 200 row - more then that is painful for the user.

Why are you against the suggestions you've gotten to query the user for data that will help to lessen the number of rows retrieved from the database?

[dee-u]

You may try the Listview control instead...