[AccessD] Optimizing query when select part requires a calculation

William Benson (VBACreations.Com) vbacreations at gmail.com
Mon Oct 3 14:20:49 CDT 2011


Optimizing a  query that compares calculated result from each table. I have
2 tables Tbl1 and Tbl2, each with the same two columns [A] and [B]; I wanted
to ask Access what values from the combination of these two fields exist in
one but not the other. I used "|" as a separator. I want to know

(1) Why method 1a and 1b are equivalent
(2) Why Method 2 is not required (ie, why can I test either item for Null
and it represents the combination is null, not the single field on its own)
(3) Why Method 3 takes hundreds of times longer



'METHOD 1a
SELECT [Tbl1].[A] & "|" & [Tbl1].[B] AS ResultX
FROM Tbl1 LEFT JOIN Tbl2 ON (Tbl1.[B] = Tbl2.[B]) AND (Tbl1.[A] = Tbl2.[A])
WHERE Tbl2.A Is Null

'METHOD 1a
SELECT [Tbl1].[A] & "|" & [Tbl1].[B] AS ResultX
FROM Tbl1 LEFT JOIN Tbl2 ON (Tbl1.[B] = Tbl2.[B]) AND (Tbl1.[A] = Tbl2.[A])
WHERE Tbl2.B Is Null

'METHOD 2
SELECT [Tbl1].[A] & "|" & [Tbl1].[B] AS ResultX
FROM Tbl1 LEFT JOIN Tbl2 ON (Tbl1.[B] = Tbl2.[B]) AND (Tbl1.[A] = Tbl2.[A])
WHERE (Tbl2.A Is Null  OR Tbl2.B Is Null)    <--- Testing both seems not
necessary

'METHOD 3
Select [Tbl1].[A] & "|" & [Tbl1].[B] as ResultX 
>From [Tbl1]
Where 
     [Tbl1].[A] & "|" & [Tbl1].[B] Not IN  (Select [Tbl2].[A] & "|" &
[Tbl2].[B] as ResultY From [Tbl2])





More information about the AccessD mailing list