Why is this query so slow

[MarkS]

Hi,

SELECT
Count(RISK_TBL_OFFERSLOT.FLOORAMOUNT)/2 AS Hours,
Avg([CAPAMOUNT]*1000) AS [Price ($/MWh)],
Avg(Abs([KW])/1000) AS [Capacity (MW)]
FROM tbl_ElecContractsTemp LEFT JOIN RISK_TBL_OFFERSLOT
ON tbl_ElecContractsTemp.OfferCD = RISK_TBL_OFFERSLOT.OFFERCD

WHERE (((RISK_TBL_OFFERSLOT.VALIDTO)=0)
AND ((tbl_ElecContractsTemp.OfferCD) Is Not Null))

GROUP BY RISK_TBL_OFFERSLOT.OFFERCD;

The tbl_ElecContractsTemp is a local access table and RISK_TBL_OFFERSLOT is
remote linked table. With only one OfferCD in tbl_ElecContractsTemp I have
let the query run for 10 minutes before killing it. If I put the OfferCD into
the query it runs in about 7 seconds.

Thanks MarkS

 

[John Spencer]

Why bother with a LEFT JOIN since you are negating the effects of the left
join by applying criteria to the table on the right?

SELECT
Count(RISK_TBL_OFFERSLOT.FLOORAMOUNT)/2 AS Hours,
Avg([CAPAMOUNT]*1000) AS [Price ($/MWh)],
Avg(Abs([KW])/1000) AS [Capacity (MW)]

FROM tbl_ElecContractsTemp INNER JOIN RISK_TBL_OFFERSLOT
ON tbl_ElecContractsTemp.OfferCD = RISK_TBL_OFFERSLOT.OFFERCD

WHERE RISK_TBL_OFFERSLOT.VALIDTO=0
AND tbl_ElecContractsTemp.OfferCD Is Not Null

GROUP BY RISK_TBL_OFFERSLOT.OFFERCD;

Beyond that I can see no reason for the query to take a long time assuming
that there is not a large number of records and that you have indexes on
these three fields
tbl_ElecContractsTemp.OfferCD
RISK_TBL_OFFERSLOT.OFFERCD
RISK_TBL_OFFERSLOT.VALIDTO=0


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..