MySQL & Access

[Scorpio11]

Hi there



I have a question.

I have been using access for quite a while now. Some of the databases i have built up over the ages have gotten quite big and as a result - quite slow (partyly because of some of the formulae i have)

I have been looking for ways to try and improve the speed and filtering the queries has helped, but not enough and i have settled on using a SQL server (or an open source version of it - MySQL). As I understanding it, have a server attached helps alot.

My question is. If i build my tables in the MySQL Server and use Access as the front end (connected by ODBC) and i run the same queries as before - would i get any performance improvements or would i get the same performance issues because the speed is coming from the queries and not the location of the tables?

[orange]

You haven't indicated what exactly is slow. Do you have a multiuser environment?
Have you split your FE/BE? Do you have a copy of FE on each client machine?
Is it one or two queries that are slow or something else?

Have you looked at your indexes?
If your base problem has to do with table, query or index structure, then changing software is probably not the answer.

Here's a link that may help.
http://www.fmsinc.com/microsoftaccess/performance.html

[ajetrumpet]

My question is. If i build my tables in the MySQL Server and use Access as the front end (connected by ODBC) and i run the same queries as before - would i get any performance improvements or would i get the same performance issues because the speed is coming from the queries and not the location of the tables?

scorp,

this is common sense my friend. is doing ANYTHING remotely slower than doing it locally?? YES. There is nothing else to it (all other factors aside).

if your tables are related, you would use the INNODB engine in mysql, if not it'll be MYISAM. but it doesn't matter really I wouldn't think, because the speed differential is totally going to depend on the ODBC connection (and also what driver version you use). the processing time differences for queries in access vs. mysql I'm sure are minimal.

[khalid]

Hi,
As Orange and Adam explain, there are lot of factors involves in speeding your data, one of which is your LAN and WAN servers speed, if your database is located on server on the network and distributed over heavy loaded servers that would also cause your database slow. Moreover the query structure and query on query (congested queries design) will also make your data processing slow.
Transferring your data to MYSQL or SQL will not solve the problem, rather it will slow down the process coz you are making bridge through ODBC connection.
The DAO (Ms-Access internal engine) is more faster than ADO, because it will process the data locally, however ADO has more advantage over DAO.