Slow response from MySQL compared to Access

MySQL 10 times slower than Access

[randallxski]

I've had a bout of 'unspecified errors' with the ODBC connection to MS Access data. I'm considering switching over to MySQL, but I'm having some performance problems in my test environment. For example, a query that takes about 1-2 seconds with Access is consistently taking 14 seconds with MySQL. Everything else is the same. All I do for testing is switch the DSN from Access to MySQL and back.

Any ideas on how to improve the performance of MySQL would be appreciated.

[coopster]

Welcome to WebmasterWorld, randallxski!

Which ODBC [dev.mysql.com] driver are you using?

[randallxski]

Thanks. I downloaded and installed the MyODBC package that contained "MySQL ODBC 3.51 Driver" dated 6/21/04.

[txbakers]

Hi Randallxski:

You must have some installation issues because mySQL is 10 times faster than Access, and more suitable to web based work.

[coopster]

Never thought of that. Since there was no mention of a server, I was assuming this was over a network, outside of using a webserver. What is your environment, randallxski?

[randallxski]

One reason I'm experimenting with converting to MySQL is for speed and reliability. I'd really like to get away from Access. I hope it's just an installation issue....

This application is running on a local server that has Windows Server 2003 Web Edition with IIS 6.

[stevenmusumeche]

Why are you using ODBC?

[randallxski]

I'm using ODBC primarily because it's the only mySQL driver I've found available for Windows. Is there a better option available that uses OLEDB or ADO? I don't recall seeing any mention of other drivers in the mySQL manual, and this is my first time administrating mySQL. I'm certainly open to other ideas, especially if they could be related to query speed.

[txbakers]

Even if you don't use ODBC, you still need to install the mySQL connector which does provide an ODBC connection if you want it.

You can also connect directly to the database using a connection string:

var conn="Driver={MySQL ODBC 3.51 Driver};server=localhost;Database=charms;Option=1";

[ogletree]

Access is faster in some cases. It can run a query real fast on a medium sized table on a slower machine. There are times where access is the best choice. You would need a pretty fast server for some querys in mySQL to get the same performance on an access db on a slower machine. That has been my experience. What are you doing. For a query to take 14 secs with mySQL out of the box means you are doing something intensive not just pulling one indexed record out of a db.

[randallxski]

txbakers, I tried the DSN-less connection, but am getting an error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I also tried the able-consulting DSN-less connection string variations, but I get the same error. Here's another one that I tried:
oConn.Open "Driver={mySQL};" & _
"Server=localhost;" & _
"Option=16834;" & _
"Database=mydb"

Is there additional driver software necessary other than MyODBC? Is the path to the DB required? (I tried that and it didn't help.) Should a DSN-less connection be faster than with a DSN? I found an OLEDB3 driver on the mysql site, but haven't tried that yet.

[randallxski]

ogletree,

The SQL that is taking 14 seconds is suprisingly simple. Out of a table with 62000 records, I'm pulling out about 25-30 records with:
"select * from T where B = '" & session("bNum") & "' and NPI = " & PI & " order by NI"

My test server hardware is old. I'll try the same setup on newer hardware and see how much of a difference that makes.

Thanks for the ideas.

[dive into perl]

randallxski,

"select * from T where B = '" & session("bNum") & "' and NPI = " & PI & " order by NI"

Even if you have an index on each of those fields, MySQL will only ever use 1 index, so when doing queries like this it's important that you define an index which spans the 3 fields. Using your query I would create an index using :

CREATE INDEX ind_BNPINI ON T (B, NPI, NI);

this way MySQL will use the index ind_BNPINI to quickly find all the records.

Take a look at the MySQL manual :- 7.4.5 How MySQL Uses Indexes
[dev.mysql.com...]

[randallxski]

I haven't made it through much of the 1150 pages of the mySQL manual yet. I certainly appreciate the index suggestion. It has increased my query result speed to match the Access speed. Thanks, dive into perl, for that suggestion. It didn't seem like adding the ORDER BY field to the index reduced the time at all, but indexing the first 2 fields together that are used in the query certainly helped. I left the third field in the index anyway.

I'm relieved that mySQL has turned into a viable alternative so I don't have to continue troubleshooting the dreaded and elusive MS Access 'unspecified error'!

[randallxski]

I have a non-DSN connection working now that I think I'll use in production, that I got working after installing and configuring the MyOLEDB3 drivers. From what I've read, I would not expect this to be substantially faster than a DSN in the test environment, but could be faster in production with more activity on the server. There are a few interesting articles on "4 Guys..." about various types of database connections (see [4guysfromrolla.com...] ).

Thanks for the advice.

[edited by: DaveAtIFG at 5:48 am (utc) on July 3, 2004]
[edit reason] Repaired broken link [/edit]

[txbakers]

Glad everything is working now. As Obi-Wan said, you ahve taken the first step into a bigger world!