Geek Answers Handbook

How can I get MS-Access to choose a different / correct execution plan for my request

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

Request 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 lines. A and B have several thousand lines.

A query that returns 2 rows (not sure if this is appropriate) is very slow. It works after 17 seconds. If I delete the part of AND D.d = 2the where clause, the query now returns 4 rows and starts instantly.

So, I understand that the JET engine can start a query without a filter on Dd immediately, and then execute the specified filter instantly (only 4 lines for filtering). Therefore, D.d = 2there should not be too much time to run a query with a filter .

I tried to create an additional query without a filter and include it in another query that would just filter the result, but it is still slow. I guess the JET engine is smart enough to smooth out subqueries so that the result is the same.

Since I was unable to complete the request as I wanted, I used JETSHOWPLAN to have Access output its execution plan. Here is what I found:

For a quick query (without D.d = 2), the first step in the query plan is to apply a filter A.primaryKey = 1in table A. This results in a data set of 1 row from more than 30,000. Then the connections appear to be made from A to D using an index with a data set that never does not exceed 4 lines.

, , . D C , D.d = 2. C A. , , D C, C B B A, . JOIN A.primaryKey=1 120K .

?

, . , . , .

,

+5
2

, , , , , . "A.primaryKey = 1" , , A B. :

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

VBA? , , , sql.

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

, VBA . @HansUp , , , . , , , , .

, qryFast , qryFast.dkey = d, "select * from tableD, d = 2 ', , tableD, , , , 17 , .

, , qryFast, , dkey = 2 ( -, pk tableD)

: 3 , qryFast, qryD qryFastWithD, . , .

, , , , , , . , .

+2

More articles:


All Articles