2

I've a MySQL database and a MS Access front end. MySQL database tables are linked via ODBC connection to MS Access.

ANY query with multiple joined tables will run extremely slow in case of having anything in "WHERE" (or "HAVING") clause.

For example:

SELECT tblGuests.GuestName, Sum(tblPayments.Payment) AS SumOfPayment, tblRooms.RoomName
FROM (tblGuests LEFT JOIN tblPayments ON tblGuests.GuestID = tblPayments.GuestNo) LEFT JOIN tblRooms ON tblGuests.RoomNo = tblRooms.RoomID
WHERE tblGuests.NoShow=False
GROUP BY tblGuests.GuestName, tblRooms.RoomName;

will take for ages (approx. 3 minutes for 20K records.) Exactly the same script takes for 1-1.5 seconds in case of Pass Through Query, so the problem shouldn't be related to indexes or settings on server side. (By the way, indexes are set up on the necessary columns and relations are set up, too.)

The problem happens ONLY if there are more than 2 tables involved in the query AND there is something in the "WHERE" clause or in "HAVING".

For example if you modify the code above like

SELECT tblGuests.GuestName, Sum(tblPayments.Payment) AS SumOfPayment
FROM tblGuests LEFT JOIN tblPayments ON tblGuests.GuestID = tblPayments.GuestNo
WHERE tblGuests.NoShow=False
GROUP BY tblGuests.GuestName;

then it will be very quick again. (Only 2 tables are involved to the query.) Also 

SELECT tblGuests.GuestName, Sum(tblPayments.HUFpayment) AS SumOfPayment, tblGuests.NoShow, tblRooms.RoomName
FROM (tblGuests LEFT JOIN tblPayments ON tblGuests.GuestID = tblPayments.GuestNo) LEFT JOIN tblRooms ON tblGuests.RoomNo = tblRooms.RoomID
GROUP BY tblGuests.GuestName, tblGuests.NoShow, tblRooms.RoomName;

will have no problem at all because there is no "WHERE" clause. However the very similar code I mentioned in the beginning of the post will be very slow, unless I run it directly on the server (or via Pass Through Query).

Do you have any idea what can cause this problem and how to avoid it (except to run Pass Through Queries all the time)?

David P.
  • 77
  • 7
  • ODBC linked tables have the option of specifying the primary key for the table, but that it is not required and may not be properly set. Access can/may not be able to determine primary keys on a linked table, so cannot use such information to optimize performance. Instead it must load full tables of data then create its own temporary indexes and joins. Ensuring the primary keys are identified on all linked tables may improve join efficiency. But immediately as I started reading your question my first thought was "pass through query", so why not leverage the native DB and just use them? – C Perkins Aug 14 '17 at 04:36
  • Further to the advice from @CPerkins, you may also try in your query to replace table tblGuests with a saved query (in Access) which does the filtering: `Select * From tblGuests Where NoShow = False`. – Gustav Aug 14 '17 at 08:04
  • I've had similar experiences with queries that use multiple LEFT JOINs. If you can't use INNER JOINs instead, the best (or only) solution may indeed be to use a Pass-Through query instead. – Andre Aug 14 '17 at 08:44
  • Thanks for the suggestions, guys! @CPerkins: The primary keys are set on each linked table in MS Access, so the reason of the problem must be something else. – David P. Aug 14 '17 at 22:47
  • @Gustav: Thank you for your suggestion! I've tried that already, but strangely the problem is the same once I run the final query (using a saved query as a table). – David P. Aug 14 '17 at 22:51
  • @Andre: Thanks for you, too! Unfortunately I need LEFT JOINs. However you are right, I don't experience the same problem in case of INNER JOINs. – David P. Aug 14 '17 at 22:51
  • 1
    An alternative to a Pass-Through query is to create a View on the server, and link that as table in Access. It also has the major advantage over PT queries of being updateable in Access (though, with left joins, only some columns may be updateable). – Andre Aug 15 '17 at 06:22
  • Your mysql tables have a `Timestamp`-Field (with Default Current Timestamp and On Update Current Timestamp)? – BitAccesser Aug 27 '17 at 22:55
  • @BitAccesser: some of the tables have, some don't have. Would that help you think? – David P. Aug 28 '17 at 23:57
  • Every table should have a Timestamp-Field (with Default Current Timestamp and On Update Current Timestamp),See[https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.htm](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html) Don't forget to refresh links. Use ODBC trace log to see what's going on. [ODBC is explained](https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx) for `MS-SQL`but similar to `MySQL`. – BitAccesser Sep 02 '17 at 15:48
  • Thanks a lot for your suggestion about TimeStamp! Unfortunately it didn't help, I think Timestamp field matters only when you want to update some fields. (I gave a try, though.) – David P. Sep 04 '17 at 01:54

0 Answers0