Optimise Microsoft Access Nested Queries

Question

I'm trying to run a set of nested or stacked queries in Microsoft Access.

As in I run Query 1
-->I use the results of Query 1 in Query 2
---->I use the results of Query 2 in Query 3
------>I use the results of Query 3 in Query 4

Query 3 takes less than 1 second to run.

-- Query 3 results -- 
PARTID  INFO
266 156-10
266 165-10
266 183-10
266 200-10
266 205-10
266 219-10
266 228-10
266 230-10
--end Query 3 results --

When I run Query 4, it takes over a minute to run. Pretty slow. So I tried making a new table (Test_Table) with the results from Query 3. I modified Query 4 to use this table rather than Query 3. Now Query 4 runs in less than 1 second.

Original Slow Query 4 code:

SELECT INVENTORYLOG.TABLEID, INVENTORYLOG.RECORDID, TIINVENTORYLOG.INFO, INVENTORYLOG.TYPEID
FROM Query3 INNER JOIN (TIINVENTORYLOG INNER JOIN INVENTORYLOG ON TIINVENTORYLOG.INVENTORYLOGID = INVENTORYLOG.ID) ON (Query3.PARTID = INVENTORYLOG.PARTID) AND (Query3.INFO = TIINVENTORYLOG.INFO)
WHERE (((INVENTORYLOG.TYPEID)<>40))
ORDER BY TIINVENTORYLOG.INFO;

Modified Fast Query 4 code:

SELECT INVENTORYLOG.TABLEID, INVENTORYLOG.RECORDID, TIINVENTORYLOG.INFO, INVENTORYLOG.TYPEID
FROM Test_Table INNER JOIN (TIINVENTORYLOG INNER JOIN INVENTORYLOG ON TIINVENTORYLOG.INVENTORYLOGID = INVENTORYLOG.ID) ON (Test_Table.info = TIINVENTORYLOG.INFO) AND (Test_Table.partid = INVENTORYLOG.PARTID)
WHERE (((INVENTORYLOG.TYPEID)<>40))
ORDER BY TIINVENTORYLOG.INFO;

Inventorylog has about 23,000 records tiinventorylog has about 18,000 records

So I guess the question is: How do I make Query 4 run quickly using Query 3 instead of my test table?

Thanks for any advice you can give.

Answer 1

Without knowing the first two queries, it's difficult to see right away where the issue is. However, I will say that things always slow down when you have a table joining to two other tables. (joining in a long chain is faster than joining in a circle, which is what you have here.) As you can see, you have query3 joining to both of your two tables. Perhaps something like this MIGHT work better. But it really depends on the indexing, the size of the tables, and what your other two queries are - if the two inventory tables are huge, this might actually slow it down.

So once you give a little more info I might revise this thought.

SELECT *
from (select INVENTORYLOG.TABLEID, 
            INVENTORYLOG.RECORDID, 
            TIINVENTORYLOG.INFO,
            INVENTORYLOG.TYPEID,
            INVENTORYLOG.PARTID
          from INVENTORYLOG 
          inner join TIINVENTORYLOG 
            on TIINVENTORYLOG.INVENTORYLOGID = INVENTORYLOG.ID
          where INVENTORYLOG.TYPEID<>40)a     
inner join query3 
    on query3.partid = a.partid
    and query3.info = a.info

Answer 2

You've taken a complicated query, broken it into different parts, and created separate queries which can be reused later. All of which is good except you are taking a performance hit. I've noticed this problem in SQL Server when views are nested 3 deep or more. You rarely get something for nothing.

Now that you know what to expect from your queries and could easily test them, try combining the first two. You may have to do this in the SQL text editor. If you're lucky, you can copy the SQL from from Query1 and place it in Query 2

Select * from Query1;

becomes

Select * from (Select whatever you pasted from Query1) as Query1;

I know this can become complicated and more difficult to maintain, but if you want to improve the performance, you ususally have to give something up.