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.*,
    ERA.*,
    ORDR.*,
ITEM.COnTY1,
(SELECT TOP 1 New FROM MAPPING WHERE Old = ITEM.COnTY1) AS NewConTy1,
ITEM.COnValue1,
(SELECT TOP 1 KBETR FROM GEN_KUMV WHERE KNUMV = ERA.DOCCOND AND KSCHL = (SELECT TOP 1     New FROM MAPPING WHERE Old = ITEM.COnTY1)) AS NewCOnValue1
--... etc: this continues until ConTy40

FROM
GEN_ITEMS AS ITEM,
GEN_ORDERS AS ORDR,
GEN_ERASALES AS ERA

WHERE
ORDR.ORDER_NUM = ITEM.ORDER_NUM AND  -- link between ITEM and ORDR
ERA.concat = ITEM.concat -- link between ERA and ITEM

I won't provide you with the tables schema since the query works, what I'd like to know is if there's a way to add the NewConTy1 and NewConValue1 using another technique to make it more efficient. The thing is that the Con* fields goes from 1 to 40 so I've to align them along开发者_JAVA技巧 (NewConTy1 next to ConTy1 with NewConValue1 next to new ConValue2... etc until 40). ConTy# and ConTyValue# are in ITEMS (each in a field) NewConty# and NewConValue# are in ERA (each in a record)

I really hope my explanation is enough to figure out my issue, Looking forward to hearing from you guys

EDIT:

Ignore the TOP 1 in the SELECTS, it's because current dumps of data I have aren't accurate it's going to be removed later

EDIT 2:

Another thing my query returns up to 230 fields also lol

Thanks Miloud

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.

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

INNER JOIN ON 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.