Visual Basic 6 + MySQL + ADO too slow

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
mjimenoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
What are the other differences?  

Was the Access database local?

Are the databases, queries, etc identical?

mlmcc
0
mjimenoAuthor Commented:
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?
0
Marv-inCommented:
do you have indexes on your fields in mysql - I believe that ms access does its own indexing automatically
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mjimenoAuthor Commented:
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.
M
0
Marv-inCommented:
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
http://www.databasejournal.com/features/mysql/article.php/1382791

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
0
Marv-inCommented:
also
http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html

the mysql online documentation is great
http://dev.mysql.com/doc/mysql/en/index.html

I sugguest you browse throught it - its a great place to learn about real relational databases.
0
mjimenoAuthor Commented:
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.
M
0
Marv-inCommented:
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.
0
mjimenoAuthor Commented:
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
0
Marv-inCommented:
ahh you should use a different table engine

ALTER TABLE products ENGINE = MYISAM;


this part of the online doc will explain it:
http://dev.mysql.com/doc/mysql/en/Storage_engines.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mjimenoAuthor Commented:
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?
M
0
Marv-inCommented:
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
restore:
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.
0
RogueSolutionsCommented:
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.

0
mjimenoAuthor Commented:
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
M
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.