Query running extremely slowly after slight change

[Nick Gilbert]

Hi,

I'm experiencing a strange problem when using a nested query in Access:

SELECT *
FROM tblProduct_Prices
WHERE Product_Code IN (

SELECT DISTINCT Product_Code
FROM
(SELECT Product_Code, From_Date, To_Date, Quantity, Price, Type
FROM tblProduct_Prices
UNION ALL
SELECT Product_Code, From_Date, To_Date, Quantity, Price, Type
FROM tblProduct_Prices_Snapshot)
GROUP BY Product_Code, From_Date, To_Date, Quantity, Price, Type
HAVING COUNT(*) = 1

)

If I run the the first nested query (starting SELECT DISTINCT) on it's
own, it returns in a fraction of a second.

If I then add the outer code (SELECT * WHERE Product_Code IN... ) the
query runs so slowly that I'm not sure it ever actually returns (Access
locks up with 100% CPU). Why is this happening? The inner query is only
returning ONE single row in my test dataset so I don't get why Access is
having such a problem with it.

Does anybody have any idea on how I can fix this query so it runs as
expected (without modifying the logic of the inner query)? Surely it
shouldn't add more than a few extra milliseconds to the time taken to
run the inner query.

Thanks,

Nick....

 

[Dirk Goldgar]

Hi,

I'm experiencing a strange problem when using a nested query in
Access:

SELECT *
FROM tblProduct_Prices
WHERE Product_Code IN (

SELECT DISTINCT Product_Code
FROM
(SELECT Product_Code, From_Date, To_Date, Quantity, Price, Type
FROM tblProduct_Prices
UNION ALL
SELECT Product_Code, From_Date, To_Date, Quantity, Price, Type
FROM tblProduct_Prices_Snapshot)
GROUP BY Product_Code, From_Date, To_Date, Quantity, Price, Type
HAVING COUNT(*) = 1

)

If I run the the first nested query (starting SELECT DISTINCT) on it's
own, it returns in a fraction of a second.

If I then add the outer code (SELECT * WHERE Product_Code IN... ) the
query runs so slowly that I'm not sure it ever actually returns
(Access locks up with 100% CPU). Why is this happening? The inner
query is only returning ONE single row in my test dataset so I don't
get why Access is having such a problem with it.

Does anybody have any idea on how I can fix this query so it runs as
expected (without modifying the logic of the inner query)? Surely it
shouldn't add more than a few extra milliseconds to the time taken to
run the inner query.

I'm no SQL wizard, but I'd think that, unless the query optimizer is
really sharp, the inner query would have to be run for every product in
tblProduct_Prices. Does this variation work better?

SELECT PP.*
FROM
tblProduct_Prices As P
INNER JOIN
(
SELECT DISTINCT Product_Code
FROM
(
SELECT
Product_Code, From_Date, To_Date, Quantity, Price, Type
FROM tblProduct_Prices
UNION ALL
SELECT
Product_Code, From_Date, To_Date, Quantity, Price, Type
FROM tblProduct_Prices_Snapshot)
GROUP BY
Product_Code, From_Date, To_Date, Quantity, Price, Type
HAVING COUNT(*) = 1
)
) As T
ON P.Product_Code = T.Product_Code

 

[Nick Gilbert]

I'm no SQL wizard, but I'd think that, unless the query optimizer is

really sharp, the inner query would have to be run for every product in
tblProduct_Prices. Does this variation work better?

Works a treat - thanks!

Nick...