A2003 jet multiple subqueries takes for-ever

[madhouse]

Hi
Desperately need advice: (project deadline in 2 days!!!)

Have complex query (generated from a UI query generator) which can generate
multiple subquries.
In A2003 the query below takes 1 HOUR to complete during which time A2003
takes >90% processor and A2003 appears to hang!
If identical data imported into SQL Server and identical Query run, it
returns in about 4 seconds!
If 2nd of the 2 subselects is removed A2003 returns all results in about 4
secs in A2003. If 1st subselect removed and 2nd (NOT) subselect is run, it
returns first few rows in about 10 secs then takes HALF HOUR to return all
rows.
(17200 rows returned for each.)
There are 250,000 rows in invoice-items table and 55,000 rows in
invoice-header table.
I have all required Indexes and have compacted & repaired and set
Subdatasheet to [none] on each table.

Query:
SELECT Company.CoID, Company.CoName
FROM Company
WHERE Company.CoID IN
(Select SalesInvItem.CoID from SalesInvHdr INNER JOIN SalesInvItem on
SalesInvHdr.SalesInvID=SalesInvItem.SalesInvID WHERE (( SalesInvItem.ProdID
= 1) OR ( SalesInvItem.ProdID = 2)) Group By
SalesInvItem.CoID,SalesInvItem.ProdID, SalesInvItem.ProdDerivID,
SalesInvItem.ProdDerivID Having Sum(SalesInvItem.Qty) >0)
AND NOT Company.CoID IN (Select SalesInvItem.CoID from SalesInvHdr INNER
JOIN SalesInvItem on SalesInvHdr.SalesInvID=SalesInvItem.SalesInvID WHERE (
SalesInvItem.ProdID = 4) Group By SalesInvItem.CoID,SalesInvItem.ProdID,
SalesInvItem.ProdDerivID, SalesInvItem.ProdDerivID Having
Sum(SalesInvItem.Qty) >0);

Thanks in advance,
Madhouse.

 

[[MVP] S.Clark]

I'd blame the performance on the NOT ... IN. Try to write it a different
way, or break it up somehow.