Re: VB CGI query of MySQL... best method?

Me wrote:
> 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.

  If you are not currently doing it, be sure to index all   columns that are used in your query-joins.   FWIW, I have Access databases that have 2 to 4 million rows   and I have no serious flaws... but YMMV.

> 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?
>

 I don't think CGI is exactly the term normally used if you are  using VB and IIS, but I don't know; as you didn't specify what  web server you're using and I'm a Perl/Apache guy.

 Just curious; Do you use ASP pages? I ask because that might  serve you better than calling a VB program to satisfy a lot of  hits.... You also have to tell us how many hits per day you  are anticipating... makes a BIG difference in your architecture..  if you are winding up for a couple thousand hits per hour you  might want to look at C# or ASP pages.

 I don't think you'll have any problem if you access your new  database via a named ODBC user/system connection... depending  upon what you ultimately end up using you can even specify  connection pooling to speed up the startup.

 Why not use SQL server? You can run pretty much any kind  of business on it and it'l make your application migration  from Access virtually seamless.

> Thanks much for your opinions and ideas.

Namaste,
kdot Received on Thu Apr 07 2005 - 21:04:05 CEST