VB/MySQL HUGE Problem

[aac11]

We have an Inventory system running on Visual Basic and Access MDB for the database. Because of frequent corruption of the Access data MDB, we implemented the same VB program on MySQL.

There were no major changes in the program since we used ADO. In fact, the only relevant change was simply the connection string. The programs were done in such a way it would work on any ODBC-compliant database and Access MDB.

We have a query form being used to query items. The Inventory table right now has 55,000 line items. In the VB/Access version, running a query to "Select * from inventory" takes about 7 seconds to display the items on a datagrid.

In the same form, using MySQL - the results were displayed after more than 7 MINUTES! What did we do wrong? Do I have to tweak anything on MySQL? The size of the inventory table was just 25MB.

We were not running Apache or any webserver. The PC which hosts the MySQL database runs on Windows ME. THe client is running on Windows 98. There was only 1 current connection. We are using TCP/IP. We are using MyODBC and the ADO data control.

This is a disaster. Please help!

 

[rycamor]

OK, let's start at the beginning:

1. It looks to me like you are querying over a network. Were you querying Access on a network before? Maybe there is a network problem. 55,000 records is nothing to MySQL.

2. Most likely it's not a MySQL problem at all but a hostname-lookup problem. Many Unix-y type programs like MySQL don't like to give in to remote requests, unless they can instantly resolve the hostname. If you local nameserver is not configured right, this can be a problem. A simple way to deal with this is to manually set the "HOSTS" file on each machine. (Do a search for "hosts" and you will see what I mean)

3. Another likely culprit is lack of indexing. Did you index the primary key in your MySQL tables? Indexes make all the difference in MySQL.

4. Windows ME is probably the singe worst OS you could use for a database server. Why not at least use Windows NT? Or if you want to actually use MySQL on the platform for which it really works best, take the plunge and learn Linux. And, there are a number of configuration options for MySQL that go in the "my.cnf" file. (Look for a file called "my-example", that looks like it has a windows Speed Dial terminal icon. That filename is actually my-example.cnf, but Windows likes to hide that extension. Open it in an editor, look at the configuration options, and then save it as "my.cnf&quot

5. MySQL is not necessarily the best replacement for Access. It actually lacks a few essentials, such as foreign key constraints (Relationships), and views (In Access they are called "queries", but once you save them they function as views). MySQL is working hard at providing this functionality soon, and some of these features are available for certain table types in the new Beta version 4.0.1. There are a few other open source databases that do have these features, such as PostgreSQL (my favorite), Interbase/Firebird, SAPDB etc... But in your case, you might just want to consider the time and effort that you will save if you just spend a bit of cash and get Microsoft SQL Server. It's the easiest solution to your situation. -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student