Visual Basic 6 + MySQL + ADO too slow

Posted on 2004-12-01
Medium Priority
Last Modified: 2013-11-25
I have a visual basic application. I usually work with MS Access and ADO, but I decided to migrate to MySQL trying to find something faster and better. I only changed the connection string and what I found is that the application used to be faster with MS Access. I hope I could be doing something wrong, because I can't imagine MySQL being slower than MS Access. Any idea?
Thanx, M
Question by:mjimeno
LVL 101

Expert Comment

ID: 12716863
What are the other differences?  

Was the Access database local?

Are the databases, queries, etc identical?


Author Comment

ID: 12716895
Application is the same, data structure is the same, fields are the same, both DB are local, all is identical, except by the type of DB. For example, I run a process for 70000 records and it takes about 30 minutes with MS Access and 60 minutes with MySQL. Any suggestion?

Expert Comment

ID: 12719008
do you have indexes on your fields in mysql - I believe that ms access does its own indexing automatically
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

ID: 12719818
what do you mean with indexing?. And I imported the ms access db using DB Manager Professional, a tool to manage mysql dbs, and it created the db structure the way it wanted based on the ms access db fields. I didn't changed it.

Expert Comment

ID: 12719884
you need to add indexes to the table for the fields that you are searching one in mysql.

to put it simply indexes are a sorted arrary that will speed up your query. once you add the proper indexes to your table it will be much faster than access.

this is a good article about mysql admin

ALTER TABLE <tablename> ADD INDEX(<fieldname>);

fields in your where clause should be indexed - number fields, dates, unique indexes all all good things to index

Expert Comment

ID: 12719902

the mysql online documentation is great

I sugguest you browse throught it - its a great place to learn about real relational databases.

Author Comment

ID: 12721359
I've read your urls Marv, but I'm not sure if you mean using primary keys. I am using PK now, so do you mean another type of indexing? I also created an index like explained in the mysql site: CREATE INDEX part_of_name ON customer (name(10)); but still the same.

Expert Comment

ID: 12724921
I could help better if i had an example of your table structure and the query you are trying to do.

primary keys and index are kinda the same things. you can have multipil indexes on a table.

that sql statement you used will only work if you have a table called customer and a field called name otherwise it wnot make a difference.

Author Comment

ID: 12726014
I know I must use the right column and table name, don-t worry about it. Here is the structure, the query is just inserting all the fields when reading from a text file:
CREATE TABLE `products` (
  `Action` varchar(3) default NULL,
  `Ingram` varchar(12) NOT NULL default '',
  `VendorNumber` varchar(50) default NULL,
  `VendorName` varchar(250) default NULL,
  `Description1` text,
  `Description2` text,
  `RetailPrice` double default '0',
  `VendorPN` varchar(50) default NULL,
  `Weight` double default '0',
  `UPCCode` varchar(50) default NULL,
  `Lenght` double default '0',
  `Width` double default '0',
  `Height` double default '0',
  `PriceChangeFlag` varchar(50) default '0',
  `CustomerPrice` double default '0',
  `SpecialPriceFlag` varchar(50) default NULL,
  `AvailabilityFlag` varchar(50) default NULL,
  `Status` varchar(50) default '0',
  `AllianceFlag` varchar(50) default NULL,
  `CPUCode` varchar(50) default NULL,
  `MediaType` varchar(50) default NULL,
  `Category` varchar(80) default NULL,
  `NewItemReceiptFlag` varchar(5) default NULL,
  `InstantRebate` varchar(50) default NULL,
  `SubstitutePN` varchar(10) default NULL,
  `Indice` double default '0',
  PRIMARY KEY  (`Ingram`),
  KEY `PartNumber_index` (`Ingram`)

Accepted Solution

Marv-in earned 400 total points
ID: 12726104
ahh you should use a different table engine


this part of the online doc will explain it:

Author Comment

ID: 12726338
I changed the engine to MyISAM and I'm testing it now, it should take now about 12 minutes to do what it took 60 minutes to do with the other type of engine, so it's now faster than with Access as it supossed to be. I've read some lines about both engines, but what I understood is that InnoDB is safer, isn't it? my question is: why is the MyISAM engine so faster and should the InnoDB be better?

Expert Comment

ID: 12727444
InnoDB uses safe transactions which to put simply save the database state in between commits. so you can undo mistakes and such. however this adds lots of overhead processing. This would be for extreame amounts of data with extreame amounts of queries for very critical systems.

MyISAM is just fine for most applications

backup your db nightly:
backup db from batch file that can be scheduled in task scheduler:
c:\mysql\bin\mysqldump --opt products > c:\db_backup\products.sql
c:\mysql\bin\mysql products < c:\db_backup\products.sql

to answer your question MyIASM has alot less processing overhead and InnoDB should only be used if you dont need the speed and want the extensive desaster recovery abilities which i have never used .

I preferr speed to safty in most cases but thats just me.

Expert Comment

ID: 12727830
Another point to consider is how you are connecting to the MySQL database.  The standard VB6 approach is via the MyODBC connector (which is slow, ODBC being old tech.)

There are some direct connection OCX/DLLs out there, e.g. MyX from SciBit that are much faster (I should add I can't get it to work on my PC right now but figure that's my fault not its).

Another is called VBMySQLDirect - haven't played with this yet.

Personally I prefer the InnoDB - the Commit/Rollback gives you more oiptions as a programmer to avoid creating nonsense in your db.  

It does sound like your upload would be hard work for Inno though, 70,000 rows each inserted one at a time (at least that's how it read to me?)

Maybe a Bulk Insert might be worth considering if you are going to be uploading those sorts of numbers regularly.  I know such things exist, haven't got that far yet but the MySQL site will doubtless have info.


Author Comment

ID: 12729773
I think using MyISAM would be enough for me as I don't do complicated querys, so using this engine solved my slowness problem and I can work like that. Thanx Rogue for your recommendations. Using another type of connection would drive me to change the code, which I don't want due to the amount of code I'm dealing with. Thanks for your response Marv-in

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question