Very slow

Hi all

Assume there are two tables with each 2 columns. TableA has 3000 rows
and TableB has 30'000 rows and each row of TableA.Col1 is linked as a
foreign key to TableB.Col1. Primary keys, indexes etc are all well
done.

Now I want to find out if there are some items in TableA which ARE NOT
found in TableB (zombie records). I know this shouldn't happen but
there are...

So I do following query:

SELECT DISTINCT TableA.Col1, TableB.Col1 FROM TableA
LEFT OUTER JOIN TableB ON TableB.Col1 = TableA.Col1 WHERE TableB.Col1
IS NULL

In case there is a zombie record it will look like:
TableA.Col1 TableB.Col1
====================
1234 NULL

If I let run this query on MSDE it is very fast.

If I let run this query in VS 2005 on a Compact Edition database it is
veeeeery slow. It takes aprox. 1 minute and the CPU is 100%.

Has someone an explanation? Or better: has someone a better solution?

Thanks
Daniel

.

Relevant Pages

  • Re: Problem with Dual subselect
    ... So I've constructed a UNION QUERY as follows. ... FROM TABLEA AS A ... B.STATUS_DATE FROM [TABLEB] As B ... A Lookup property on the foreign-key field ...
    (microsoft.public.access.queries)
  • Re: use a result as a FIELD in the design grid
    ... I have a key TableA that maps each station's flow (and ... Station: FlowField: TankLevel: PumpRun: ... OK, now from the previous query, I know that the FlowField ... So in a new query grid where TableA is linked to TableB ...
    (microsoft.public.access.queries)
  • Re: Advanced query issue
    ... and then write a query linking just by your new ... FROM tablea As a RIGHT JOIN tableb as b ... That assumes tableb has all the possible occurrence of the two ... I also used UNION ALL, ...
    (microsoft.public.access.queries)
  • Re: Finding matching records
    ... >> An inner join in a query will return only records where the join ... Suppose you have two tables, TableA and TableB, with these fields: ... A_ShouldMatch has a match in the B_ShouldMatch field in TableB. ... create a new query in Design View and add both TableA and TableB to the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Advanced query issue
    ... my mistake, in the second query, should have been UNION not UNION ALL, ... If lots of a.c are empty in the first query, it is because they appear ONLY ... FROM tablea As a RIGHT JOIN tableb as b ...
    (microsoft.public.access.queries)
Loading