Left Join query returns with null values

[Tallia]

I am trying to merge data into an existing database. The existing table has
about 70,000 rows and the data I want to merge has about 80,000 rows. They
have about 30,000 rows in common.

In order to avoid duplicating any of the data, I have constructed a query
that will only return the rows that are not duplicate. The query returns with
the 50,000 rows that I expect, but they do not contain any data, rather it
all of the fields seem to be blank.

I thought it was a problem with the way I constructed the query, but using
the query construction wizard provided the same result.

Queries to identify the common rows return without any issue.

 

[Tom van Stiphout]

On Thu, 13 Nov 2008 20:01:17 -0800, Tallia

I might use:
insert into Destination
select * from Source
where SomeField not in (select SomeField from Destination)

-Tom.
Microsoft Access MVP

 

[Tallia]

I tested that query (sans the insert) and it seems to working, if extremely
slowly. If I understand the statement you suggested, for each row in the
source table it will query the destination table. it will then search the
results of that query to see if the value of the field is present.

It seems that this will work. However, I am disappointed by the performace.
I will be receiving similarly sized dataset every other week or so. I may
just accept that it will take much time to insert the new data, but I wonder
if there is a better solution.

I will need to experiment. Will seting a primary key in the source table
increase performance at all? The destination table has a primary key defined.

 

[Tom van Stiphout]

On Thu, 13 Nov 2008 20:37:26 -0800, Tallia

Subqueries can indeed be slower. Your outer join query (or a variant
thereof) should work as well; perhaps better. OTOH you wrote back 7
minutes after my post, so it can't be THAT slow.

Indexes on fields listed in the where clause (in my case SomeField)
will definitely help. If you are joining in the Relationships window
there are already indexes on the fields on each side of the relation.

-Tom.
Microsoft Access MVP

 

[Tallia]

Sorry to reply to my own post. It turns out that Access has frozen both times
I have tried to run the query suggested by Tom. It does seem to work, but the
query never finishes and Access stops responding.

 

[John Spencer]

It would help if you posted your original query (View: SQL from menu, cut and
paste).

Generically, you need an unmatched query to find all records in the merge
table that are not in the existing table. I will call the tables Source and
Target.

Assuming that you identify duplicates between the two tables based on two
fields, your query generically would look like the following.

INSERT INTO Source
SELECT Target.*
FROM Target LEFT JOIN Source
ON Target.AField = Source.AField
AND Target.BField = Source.BField
WHERE Source.AField is Nulln

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

I might use:
insert into Destination
select * from Source
where SomeField not in (select SomeField from Destination)

My experience is that the Jet query optimizer does a very poor job
of using indexes with NOT IN clauses (though it does a find job of
it with IN clauses). I'd avoid that if I could.

 

[David W. Fenton]

I am trying to merge data into an existing database. The existing
table has about 70,000 rows and the data I want to merge has about
80,000 rows. They have about 30,000 rows in common.

In order to avoid duplicating any of the data, I have constructed
a query that will only return the rows that are not duplicate. The
query returns with the 50,000 rows that I expect, but they do not
contain any data, rather it all of the fields seem to be blank.

I thought it was a problem with the way I constructed the query,
but using the query construction wizard provided the same result.

Post your SQL. There really isn't any reason why this shouldn't work
-- I do it all the time with datasets of all sizes.

 

[David W. Fenton]

Indexes on fields listed in the where clause (in my case
SomeField) will definitely help. If you are joining in the
Relationships window there are already indexes on the fields on
each side of the relation.

But NOT IN doesn't use indexes in all the cases where IN uses them.
I've never been able to figure out why, though.

 

[David W. Fenton]

Sorry to reply to my own post. It turns out that Access has frozen
both times I have tried to run the query suggested by Tom. It does
seem to work, but the query never finishes and Access stops
responding.

Do you have indexes in both tables on the field(s) you're joining
on? If not, that will make it very slow.

 

[David W. Fenton]

I tested that query (sans the insert) and it seems to working, if
extremely slowly. If I understand the statement you suggested, for
each row in the source table it will query the destination table.
it will then search the results of that query to see if the value
of the field is present.

No, what it does is the subquery returns a list of the values in the
field the subselect is returning and that list is matched against
the values in the field in the main body of the query.

If both sides are not indexed, it will be very slow, because it has
to do it all row-by-row.

If both sides are indexed, then it ought to be very fast, though, as
I've said in two other posts, NOT sometimes causes indexes to not be
used with IN clauses. If there *are* indexes and Jet uses them, it
will perform whatever the opposite of an INDEX JOIN is, and that
*ought* to be extremely fast.

I'd definitely try to make the outer join work, as it will *always*
use indexes in all cases where they are available.

 

[Tom van Stiphout]

On 15 Nov 2008 00:13:30 GMT, "David W. Fenton"

Interesting. As evidenced by what?

-Tom.

 

[David W. Fenton]

Interesting. As evidenced by what?

Experience. No way to prove it as SHOWPLAN doesn't indicate how Jet
optimizes subqueries.