where not exists (Is there a better way?)

[Guest]

I have a query using a where not exists shown below. This query keeps on
choking when runnning. It takes around 3 minutes to run on my machine and on
slower PC's it takes forever. My question is is there an alternative to a
where not exists? Is there a way I can speed up the query? I will tell you
that it is comparing approx. 5000 records from the result of (M or F parts
Without Matching Bradley BOM's_new) and checking against about 90,000 records
in the table (TblDistinctPartandNHA)

SELECT [M or F parts Without Matching Bradley BOM's_new].PART, [M or F parts
Without Matching Bradley BOM's_new].DESCR, [M or F parts Without Matching
Bradley BOM's_new].PCC, [M or F parts Without Matching Bradley
BOM's_new].SOURCEPLT
FROM [M or F parts Without Matching Bradley BOM's_new]
WHERE (((Exists (select * from TblDistinctPartandNHA
where TblDistinctPartandNHA.nha = [M or F parts Without Matching Bradley
BOM's_new].PART))=False));

Dave

 

[Guest]

Try taking off the =False part and making it a straight NOT EXISTS. I took
the liberty of cleaning up the SQL some:

SELECT MF.PART,
MF.DESCR,
MF.PCC,
MF.SOURCEPLT
FROM [M or F parts Without Matching Bradley BOM's_new] as MF
WHERE NOT EXISTS (
SELECT *
FROM TblDistinctPartandNHA
WHERE TblDistinctPartandNHA.nha = MF.PART);

Make sure that both the nha and PART fields are indexed.

If that doesn't work, try making it a NOT IN statement. Sometimes that can
speed things up.

SELECT MF.PART,
MF.DESCR,
MF.PCC,
MF.SOURCEPLT
FROM [M or F parts Without Matching Bradley BOM's_new] as MF
WHERE MF.PART NOT IN (
SELECT TblDistinctPartandNHA.nha
FROM TblDistinctPartandNHA);

 

[OfficeDev18 via AccessMonster.com]

Hi, Dave,

If there's some way to whittle down that 90,000-record table, do it. If
there's some criterion, such as before some date or below some sequence
number, that you know can't possibly be returned by the current query, use
that information to cut TblDistinctPartandNHA by making a new, smaller table
consisting of only the records that could possibly be returned. then use the
new table in your query, instead of TblDistinctPartandNHA. Of course, if you
run this periodically, you'll have to make sure you delete the information in
the small table each time you append to it.

Hope this helps,

Sam

Try taking off the =False part and making it a straight NOT EXISTS. I took
the liberty of cleaning up the SQL some:

SELECT MF.PART,
MF.DESCR,
MF.PCC,
MF.SOURCEPLT
FROM [M or F parts Without Matching Bradley BOM's_new] as MF
WHERE NOT EXISTS (
SELECT *
FROM TblDistinctPartandNHA
WHERE TblDistinctPartandNHA.nha = MF.PART);

Make sure that both the nha and PART fields are indexed.

If that doesn't work, try making it a NOT IN statement. Sometimes that can
speed things up.

SELECT MF.PART,
MF.DESCR,
MF.PCC,
MF.SOURCEPLT
FROM [M or F parts Without Matching Bradley BOM's_new] as MF
WHERE MF.PART NOT IN (
SELECT TblDistinctPartandNHA.nha
FROM TblDistinctPartandNHA);

I have a query using a where not exists shown below. This query keeps on
choking when runnning. It takes around 3 minutes to run on my machine and on

[quoted text clipped - 14 lines]

Dave

 

[Guest]

Dave:

Can't you do this with an OUTER JOIN:

SELECT
[M or F parts Without Matching Bradley BOM's_new].PART,
[M or F parts Without Matching Bradley BOM's_new].DESCR,
[M or F parts Without Matching Bradley BOM's_new].PCC,
[M or F parts Without Matching Bradley BOM's_new].SOURCEPLT
FROM [M or F parts Without Matching Bradley BOM's_new]
LEFT JOIN TblDistinctPartandNHA
ON [M or F parts Without Matching Bradley BOM's_new].PART]
= TblDistinctPartandNHA.nha
WHERE TblDistinctPartandNHA.nha IS NULL;

Ken Sheridan
Stafford, England