where not exists (Is there a better way?)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 07 Apr 2006 14:27 GMT

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

Reply to this Message

Jerry Whittle - 07 Apr 2006 15:11 GMT

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);

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> 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

Reply to this Message

OfficeDev18 - 07 Apr 2006 16:14 GMT

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:
[quoted text clipped - 28 lines]
>>
>> Dave

Signature

Sam

Reply to this Message

Ken Sheridan - 07 Apr 2006 18:34 GMT

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

> 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

Reply to this Message