2

I have a ms access application that links mysql tables vía ODBC. The mysql db used to be in the same LAN and performance was spot on, but was outsourced to a different location with a 100Mbs connection. Version:

CentOs: 6.7

Mysql: 5.6.26

Ms access: 2010 (Also tested in a ms access 2003 )

ODBC: 5.3 unicode driver version in a windows 7 PC

I have tested both a myIsam table and an InnoDB table with 3 fields, being the first one an integer type which works as the PK.

test_localTable is a local table created in ms access with 30 rows

-> Using this commands from an ms access query:

insert into mysqlInnoDb_remoteTable select * from test_localTable

insert into mysqlMyIsam_remoteTable select * from test_localTable

delete * from mysqlInnoDb_remoteTable

delete * from mysqlMyIsam_remoteTable

Both the innodb and the myIsam tables take on average 10 seconds to insert, 11 seconds to delete. Same goes with updates.

The interesting thing is that any kind of select query runs as fast as if I were working with the server locally.

I assume my server is missing some vital tweaking as I don't consider that performance acceptable.

Could you help me out?

thank you

auth private
  • 1,318
  • 1
  • 9
  • 22
Fran
  • 123
  • 8
  • 1
    Can you enable the MySQL general log (general_log = 1) to see what MySQL is doing. ODBC can also write everything to Log. Look for the 'Trace' tab in ODBC Administrator. Check the queries run match what you expect. – Steve E. Sep 17 '15 at 19:39

1 Answers1

2

Under some circumstances Access will actually send one native SQL statement for each row when executing a single Access SQL statement against an ODBC linked table. If you were to enable the general_log on the MySQL server you would see that, unfortunately, the Access SQL statements

INSERT INTO LinkedTable ... SELECT ... FROM LocalTable

and

DELETE * FROM LinkedTable

are two such cases.

When dealing with large numbers of rows and/or slow connections it may be necessary to use workarounds involving pass-through queries. For example, say you had an ODBC linked table named LinkedTable in Access that pointed to a MySQL table named RemoteTable. If

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "DELETE * FROM LinkedTable"
Set cdb = Nothing

proves to be too slow then this might be considerably faster

Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("LinkedTable").Connect
qdf.SQL = "TRUNCATE TABLE RemoteTable"
qdf.ReturnsRecords = False
qdf.Execute
Set qdf = Nothing
Set cdb = Nothing

A sort-of similar workaround for INSERT ... SELECT can be found in a related answer here.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • The insert into X select from Y you suggested worked like a charm. Now, I need to optimize all my queries to this procedure. Where did you learn to do this? I mean, I need to understand more about the topic to learn how to perform updates and deletes using where. How could I start looking for more documentation about this? ____ Fantastic help, thank you. – Fran Sep 18 '15 at 08:54
  • I reply myself. Ms access pass through querys are the answer. thanks again – Fran Sep 18 '15 at 13:44