Select Optimization In Access

I'm in serious trouble, I've a huge subtle query that takes huge time to execute. Actually it freezes Access and sometimes I have to kill it the query looks like: SELECT ITEM.*

Solution 1:

Have you considered a union query to normalize items?

SELECT "ConTy1"As CTName, Conty1 As CTVal, 
       "ConTyValue1"As CTVName,  ConTyValue1" As CTVVal
       FROM ITEMS 
UNION ALL 
SELECT "ConTy2" As CTName, Conty2 As CTVal, 
       "ConTyValue2" As CTVName,  ConTyValue2"As CTVVal
       FROM ITEMS
<...>
UNION ALL 
SELECT "ConTy40"As CTName, Conty40 As CTVal, 
       "ConTyValue40"As CTVName,  ConTyValue40" As CTVVal
       FROM ITEMS

This can either be a separate query that links in to your main query, or a sub query of your main query, if that is more convenient. It should then be easy enough to draw in the relationship to the NewConty# and NewConValue# in ERA.

Solution 2:

Remou's answer gives what you want - significantly different approach. It's been a while since I've meddled with MS Access query optimization, and had forgot about the details of its planner, but you might want to try a trivial suggestion to actually make your

WHERE conditions

into

INNERJOINON conditions

You are firing 40ish correlated subqueries so the above probably will not help (again Remou's answer takes significantly different approach and you might see real improvements there), but do let us know as it is trivial to test.

Another approach that you can take is to materialize expensive part and take Remou's idea but split it into different parts where you can join directly.

For example your first subquery is correlated on ITEM.COnTY1, your second is correlated on ERA.DOCCOND and ITEM.ConTY1.

If you classify your subqueries according to correlated keys then you can save them as queries (or materialize them as make table queries) and join on them (or the newly created tables), which should might perform much faster (and in the case of make tables will perform much faster, at the expense of materializing - so you'll have to run some queries before getting latest data - this can be encapsulated in a macro or VBA function/sub).

Otherwise (for example if you run the above query regularly as a part of your normal business use case) - redesign your DB.