How can I make MS-Access choose a different/the right execution plan for my query

Question

I have a problem with a relatively simple query and the execution plan Access choose for it.

The query is of this form

SELECT somethings
FROM A INNER JOIN (B INNER JOIN (C INNER JOIN D ON ...) ON ...) ON ...
WHERE A.primaryKey= 1 AND D.d = 2;

C and D have relatively few rows. A and B have a few thousands rows.

The query, which returns 2 rows (not sure if this is pertinent) is really slow. It runs in 17 seconds. If I remove the AND D.d = 2 part of the where clause, the query now returns 4 rows and run instantly.

So my understanding is that the JET engine could run the query without the filter on D.d instantly, then execute the said filter instantly (only 4 rows to filter). Therefor it should not be too much longer to run the query with the D.d = 2 filter.

I tried to create a sub query without the filter and include this in another query that would just filter the result, but it's still slow. My guess is that the JET engine is smart enough to "flatten" the sub-queries so the result is the same.

Since I was unable to make the query run as I wished I used the JETSHOWPLAN thingy so that Access would output it's execution plan. Here is what I found:

For the fast query (the one without D.d = 2) the first step of the query plan is to apply the A.primaryKey = 1 filter on the A table. This result in a data set of 1 row out of more than 30000. Then the joins seems to be executed from A to D using index with a data set that never goes over 4 rows.

The slow query seems to be executed in the revers order. D and C are joined first then D.d = 2 is tested. After that, the joins from C to A are executed. By doing this this way the data that needs to be joined from D to C, from C to B and from B to A is much larger. When all the JOIN are executed and before A.primaryKey=1 is executed the data set will have 120K rows.

Is there a way I could force the right query plan on Access?

I hope I was clear. Let me know if I should post the query plans. I did not because they are quite large.

Thanks in advance,

mp

Answer 1

I finally got it to work by mixing things up until the query planner agreed with me. I isolated the "A.primaryKey= 1" in a sub-query to ensure it's executed before A is joined to B. It's something like this :

SELECT ... 
FROM (SELECT ... FROM A WHERE a.primaryKey=1) AS qryA
   INNER JOIN B ...
WHERE D.d = 2;

Answer 2

Do it in VBA code? The idea would be to take out the part that's slow and execute the fast-returning query, then append the slow part in sql.

db.execute "select * from qryFast inner join d on qryfast.dkey = d.d where d.d = 2

---

No, VBA code in a module is different from a sub-query. @HansUp has clarified for us that executing the code in one step, as I've shown above, won't improve the performance. You should be able to get the results in memory quickly, if you're familiar with writing code in modules, but then getting the output where you need it to go might slow you down more.

---

in other words, you should be able to get the results of qryFast into a recordset in memory quickly, and then apply a filter on qryFast.dkey = d, and also get a recordset quickly from 'select * from tableD where d=2' to look up the related info you want from tableD, but getting all that stuff out of memory and to a place where your front-end can access it might take longer than the 17 seconds they're waiting now.

---

In fact, it might kick it in the pants enough if you change qryFast to include a condition where dkey = 2 (or whatever the pk is on tableD)

---

another idea: have 3 queries, qryFast, qryD, and qryFastWithD joining the two. I'm just tossing out ideas, here.

---

or, as you say in your comments, try containing different parts of the query in sub-queries, but I would think the optimizer wouldn't be fooled by such a trick, if moving a piece of it into a sub-query didn't work. By all means, whatever works, take it.