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

Question

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!

BTW, that Dim statement is awesome. :( – Andre Dec 14 '21 at 17:35 — Andre, Dec 14 '21 at 17:35

score 0 · Answer 1 · answered Dec 14 '21 at 15:02

0

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.

answered Dec 14 '21 at 15:02

Erik A

31,639
12
42
67

Thanks a lot for your answer. This speeds up the initial query, Editor form and Data load very fast with it. Sadly, the fields are now non editable. I don't understand why though. I switched between the slow version and this one multiple times without changing anything else, but with the fast one I can't even make entries into the textfields. There's a message in the bot left corner "Recordset is not updatable". Very strange, I will have to look into that... – spiegelt Dec 14 '21 at 15:38
Well, with temp tables it wasn't editable either since a temporary copy of the table would be edited. Editing data across multiple tables in a single form joined by anything but a shared primary key is a very, very dubious thing to do and comes with substantial locking overhead. Consider splitting the forms in a main form and a subform, both directly bound to a table. That would be a substantial rework but come with even more performance benefits. – Erik A Dec 14 '21 at 15:49
As far as I can tell, the whole "Recordset is not updatable" seems to be another Access SQL specific thing. In "Lightning Guide to Databases with Microsoft Access and SQL" by Arturo Azcorra, Page 345ff., there's a chapter on "How do I write the "ON" clause?" that details how prickly Access is about the ON clause. (Excerpt available on Google Books) Additionaly I get an error message, when trying to switch that SQL statement from SQL to Design view. I see that as proof that Access cant really resolve this, but that's just my best guess... – spiegelt Dec 16 '21 at 08:42
See [link](https://books.google.de/books?id=TYlCEAAAQBAJ&pg=PA346&lpg=PA346&dq=access+join+on+boolean+expression&source=bl&ots=83_ilmxMGt&sig=ACfU3U15QiPMKgXqiKdpR44DhSm-CQBKIw&hl=de&sa=X&ved=2ahUKEwiNpaWk8uf0AhUC_aQKHTEeCcIQ6AF6BAgaEAM#v=onepage&q=access%20join%20on%20boolean%20expression&f=false) (I hope this link works correctly...) – spiegelt Dec 16 '21 at 08:45
I'm well aware what causes this. The point I'm trying to make is: you don't want updateable queries that join on non-primary keys. You get all kind of oddities if you do. As said, the best way to resolve this is a subform and direct bindings to tables, since you don't get the locking/latching/updating overhead as you work directly and lock only a single table. Access can't represent lots of queries in design view, and you generally shouldn't use design view if you're serious about writing queries, even if it's just because of the parentheses overload. – Erik A Dec 16 '21 at 08:51
I spent the whole of yesterday and took your advice to heart about splitting the form into MainForm + SubForm. That proofed to be simpler than expected and I was able to seperate all the relevant stuff, now avoiding the crucial INNER JOIN. The DB runs fast and smooth and Backend updates the entered data correctly. Thanks again! – spiegelt Dec 16 '21 at 08:52

Andre · Answer 2 · 2021-12-14T17:45:55.347

0

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.

edited Dec 14 '21 at 17:45

answered Dec 14 '21 at 17:34

Andre

26,751
7
36
80

Thank you for your answer! I came in early this morning and made t2.JobNr the primary key. This didn't increase performance at all :( – spiegelt Dec 15 '21 at 06:21
I also tried your SQL solution. It pulls the data amazingly fast (Yay!) but then I face the same problems as with Erik A's approach: "Recordset is not updatable" anymore. I seem to have a general problem there, I will have to look into it today.... Server Backend sounds awesome, but i just don't have the time for large changes, this is just a side project... Sadly, 'quick and dirty' was and prob. always will be the Modus Operandi here :-/ – spiegelt Dec 15 '21 at 06:32
It's quite surprising that the PK doesn't change performance. I don't think I have any other ideas. It might be time to hire a consultant to actually look at the db and the queries. -- Re: "quick and dirty" - I don't think this will cut it in the long run for something *which is crucial for my department*. – Andre Dec 15 '21 at 14:54
I was able to resolve my issue by taking Erik A's advice about avoiding the JOIN by instead splitting things into MainForm (t1) and SubForm (t2), which was a tediuous work but probably the best solution here. Thanks again for your help! – spiegelt Dec 16 '21 at 09:01

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

2 Answers2