I have 2 tables, Customers and Countries, like so:

Customers:

     ---- ------ ----- --------------- ---------------- 
    | ID | Name | ... | OfficeCountry | BillingCountry |
     ---- ------ ----- --------------- ---------------- 
    | 1  | Bill | ... | 1             | 1              |
    | 2  | Joe  | ... | 2             | 1              |
     ---- ------ ----- --------------- ---------------- 

Countries:

     ---- ------------- 
    | ID | Name        |
     ---- ------------- 
    | 1  | USA         |
    | 2  | Netherlands |
     ---- ------------- 

(I stripped some columns from the Customers table to only have some relevant columns for this question) The purpose of those two columns are that the country for billing and the physical office location could be different. We also have more address information in this table, but stripped for this example.

I JOIN these two tables with a query resulting in something like this:

SELECT
    ID,
    some, 
    fields,
    Countries_1.Name AS OfficeCountryName,
    Countries_2.Name AS BillingCountryName
    
FROM
    Customers
    
    LEFT JOIN
        Countries AS Countries_1
    ON
        Customers.OfficeCountry = Countries_1.ID    
    
    LEFT JOIN
        Countries AS Countries_2
    ON
        Customers.BillingCountry = Countries_2.ID

The application we are using is a MS Access front end with a MySQL back-end. This is done with ODBC.

The Customers table contains roughly 15,000 records.

The problem is that the application has a bad performance. I enabled the query log, and I can see the following queries being executed when I am loading the data (from a DynaSet) into a form:

• The Query as written above
• An extra query, with an OUTER JOIN, written in the old legacy {oj ...} syntax
• 30.000 queries (2x the COUNT from Customers) to the Countries table. This exact query: SELECT ID FROM Countries WHERE ID = 2 (or ID = 1, depending on the Customer).

The last two queries amaze me.

1. First, WHERE is the OUTER JOIN query coming from? I never specified any OUTER JOIN in Access. Also, the old legacy {oj ..} syntax gives me the feeling that something's up. Also, this query is not needed. I do not use it's data in the Access front end, and I don't know where it's coming from.
2. Second, WHY is Access query'ing the Countries table for every record? The data isn't needed, and also not helpful. It's only SELECTing the ID which it already knows (as seen in the WHERE clause)

As you can imagine, 30,000 queries is greatly slowing down the performance.

I know that it's not good practice to load 15,000 records into one form (with navigation controls and such), but it's a very old legacy application and a lot of work to re-write.

EDIT I see now that for very simple queries, with just a purpose build very clean form, it generates a few queries:

1. A query that selects all ID's (so for the Customer, and twice the JOIN'ed table
2. A query that selects all neccesary fields PER RECORD RETURNED from query 1, FOR EVERY TABLE. So a SELECT FROM Customers WHERE Id = record_currently_viewed

CodePudding user response：

Access queries with multiple LEFT JOINs on linked ODBC tables are notorious for bad performance. The MySql ODBC driver may be even worse than the Sql Server driver, if it fails on such a simple query.

How to fix:

1- In this particular case, there really should be no reason to use LEFT JOIN at all. How can there be country FKs without matching countries?

You should fix any orphaned FKs and create relationships with referential integrity, then use INNER JOIN, and the problem will be gone.

2- If this isn't possible, move the processing to the server. Create a View that does the joining, link it in Access, and base the form on it.

3- If you can't modify the backend DB at all, you can also use a PassThrough query. This will be read-only though.

CodePudding user response：

Have created a small fiddle where you still could use Left join - With SQL example.

You can try to use with statement - than should load data in memory for further processing:

with memCountry (ID, Name) as (select ID, Name from Countries) 
select cu.ID, cu.Name, c1.Name, c2.Name from Customer cu
left join memCountry c1 on cu.OfficeCountry = c1.id
left join memCountry c2 on cu.BillingCountry = c2.id