VB CGI query of MySQL... best method?

A little history:
A couple of years ago I wrote a VB CGI that retrieves data (a product catalog) from a MS Access DB for display to the web. It accesses the Access DB via DAO. It works flawlessly and it's fast as lightning. It'll search all data in the whole DB (a couple thousand rows with 20+ columns of data for each row), return a list of ~500 product numbers that match the query, and have that list on your browser in less than a second if you have a fast connection. Another site using ASP with a similar size DB will return a similar query in about 13 second consistently. There's absolutely no comparison, and I've had customers comment on the notable speed difference between my site and my competitions site. Of course, the DB is actually on the web server in this case, which helps.

Current situation:
Everything is running smoothly and it's still quite fast, but the volume on the site is steadily climbing. I expect it to climb sharply when I make a serious push to get higher ranking in the search engines to get the volume up. I expect that Access DB to completely fall apart when that happens. Obviously I need to migrate to a more solid database. This is where MySQL comes into play.

My new requirements:
1. Obviously, I'd like to maintain my notable speed advantage over my competition so I'd prefer to stay away from scripted languages and stick to compiled executable CGIs, preferably in VB because I know it reasonably well.
2. I need to move the DB to another server on the same LAN that the web server is connected to. I intend to have the business software operating on the same DB as the website so the website DB never has to be updated. Products, pricing, availability, etc... will be updated as changes are made to the DB via the business software during the normal course of business. In this scenario the queries/data to/from the DB will have to travel over the LAN (100 mbs) instead of being retrieved straight from the servers hard drive.

So, I'm currently using DAO in a VB CGI to interact with an Access DB. Considering my new requirements, what's the best method to use in a VB CGI to allow a high volume website to interact with a MySQL DB?

Thanks much for your opinions and ideas. Received on Sun Apr 03 2005 - 17:19:48 CEST