Visual Basic Database - VB CGI query of MySQL... best method?

Author

VB CGI query of MySQL... best method?

2005-04-06, 12:46 pm

What's the best method to use in a VB CGI access a MySQL DB for a high
volume web site?

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.

PCIVO

2005-04-06, 12:46 pm

>> (a couple thousand rows with 20+ columns of data for each row)
if your db is set up right (and it looks that way) you will see no notable
speed decrease with a couple of hundred thousand rows

Taylor

2005-05-04, 4:01 am

you could just download MYSQL DB and install it on the same machine.
I use VB-CGI and use MYSQL DB on the same machine everything was just as
fast if not faster. may need to change the Connection string. personally i
use ADO but i havent seem much of a difference when going from DOA to ADO but
you shouldn't have any problems.

best of luck to ya!

"PCIVO" wrote:

> if your db is set up right (and it looks that way) you will see no notable
> speed decrease with a couple of hundred thousand rows
>
>
>

Sponsored Links