Slow INNER JOIN Query in MS-Access 2016, Workaround?-CodePudding

This is my first question here, please be gentle.

At work, I inherited responsibility for a MS Access database, which is crucial for my department.

That database was grown over 20 years, with things added, removed and changed. Shortly, it's a convoluted mess. The VBA code contains great stuff like this, I kid you not:

Dim p, strText, A, B, C, d, E, F, G, H, i, j, K, L, M, N, O, Z, R, Q, kd, AfGb, T, LN, DC, EntBez, TP, pack, Press, Fehler, ksoll, Y, zeileninhalt, dateipfad, auslesezeile As String

I'm slowly cleaning it all up, but... anyways:

The Problem

It is slow when opening some forms (7-10 seconds loading time). I was able to narrow it down to the recordsource of these forms, which all use basically the same query or a variation of it.

The user enters a job number in the Main form and hits enter. The underlying query then pulls data from two tables based on the unique key JobNr. The result is a single row containing all the info for this job. These infos are displayed in an Editor form, using the query as recordsource.

The database is split into frontend and backend, t1 and t2 are backend tables each with about 20k entries. Backend sits somewhere on the company servers, frontend is saved locally on each user computer.

This is the query:

SELECT *
FROM t1 
INNER JOIN t2 ON t1.JobNr = t2.JobNr
WHERE JobNr = [Forms]![Main]![JobNr];

t1 has JobNr as primary key, t2 has an ID as primary key, JobNr is not indexed. I want to try indexing it in hope of better performance, but currently can't make changes to the backend during busy work days...

This simple query is stupidly slow for what it is. The problem seems to be the order of execution. Instead of getting the single entries from t1 and t2 and joining these to a single dataset, Access seems to first join both friggin tables as a whole and only after that looks up the single dataset the user is interested in.

I was not able to find a solution to dictate the execution order. I tried different ways, like rewriting the SQL code with nested Selects, something like:

SELECT *
FROM 
    (SELECT * FROM t1 
     WHERE t1.JobNr = [Forms]![Main]![JobNr]) AS q1
INNER JOIN
    (SELECT * FROM t2 
     WHERE t2.JobNr = [Forms]![Main]![JobNr]) AS q2 ON q1.JobNr = q2.JobNr;

Still slow...

I wanted to try WITH to partition the SQL code, but that's apparently not supported by MS Access SQL.

I tried splitting the query into two queries q1 and q2 in access, that pull the data from t1 resp. t2 with a third query q3 that does the joining of these supposed subsets... to no avail. q1 and q2 individually run blazingly fast with the expected data result, but q3 takes the usual 7-10 seconds.

The current approach I'm working on is running q1 and q2 and saving the acquired data to two temp tables tq1 and tq2 and then joining these in a last query. This works very well in as it rapidly loads the data and displays it in the editor (< 0.5 seconds, hurray!). The problem I'm facing now is updating any changes the user makes in the editor form to the backend tables t1 and t2... Right now, user changes don't take and are lost when closing and reopening the job/editor.

Soooo, what am I missing/doing wrong? Is there any way to make this INNER JOIN query fast without the whole temp table workaround?

If not, how would I go about updating the backend tables from the local temp tables? Changes in the Editor are saved in the temp tables until overwritten by reopening the editor.

I already added intermediary queries, that add the resp. primary keys to the temp tables (this cannot be done directly in the Create Table queries....) but...

I also tried using an Update query when closing the Editor, which doesn't seem to work either, but I might have to debug that one, I'm not sure it even dies anything right now...

Sorry for the long text!

Kind regards and thanks for any help in advance!

CodePudding user response：

The most obvious rework is to move the filter into the join:

SELECT *
FROM t1 
INNER JOIN t2 ON (t1.JobNr = t2.JobNr AND t2.JobNr = [Forms]![Main]![JobNr])

My guess is that it's irrelevant if you filter on t1 or t2, but then my guess would also be that Access is smart enough to filter while joining and that appears to be untrue, so check that.

For more detailed performance analysis, a query plan tends to help. See How to get query plans (showplan.out) from Access 2010?

Of course, adjust 14 to your version number.

CodePudding user response：

You need to add a unique index to t2.JobNr, even better make it the primary key.

Everything else is just a waste of time at this point.

Set a date and time for the users to quit their frontends, kick them out if necessary: Force all users to disconnect from 2010 Access backend database

In the long run, moving from an Access backend to a server backend (like the free SQL Server Express) will be a good idea.

Edit: have you tried what happens if you don't do JOIN at all?

SELECT *
FROM t1, t2
WHERE t1.JobNr = [Forms]![Main]![JobNr]
  AND t2.JobNr = [Forms]![Main]![JobNr]

Normally you want to avoid this, but it might help in this case.