Query Runs Dead Dog Slow

[ProtocolPirate]

if I run this query it takes about 10 seconds:

SELECT s.Name
FROM (
SELECT ChargeNumber, RefPhyCode FROM Posting_Wilshire
UNION ALL
SELECT ChargeNumber, RefPhyCode FROM Posting_Brea
) AS a
INNER JOIN Servloc s ON s.Id=a.RefPhyCode

But when I added on line too it:

INNER JOIN Daysheet d ON d.ChargeNum=a.ChargeNumber

All of a sudden it takes hours. There IS an index on Daysheet.ChargeNum, and as long as I don't have queries that start from a virtual table made from the UNION of posting tables, I get great response time from any kind of query on Daysheet, no matter how stacked the JOIN statements are too other tables.

Why does adding this one line, which doesn't produce anymore rows of data, make the execution jump up by several orders of magnitude?

 

[PHV]

What about this ?
SELECT s.Name
FROM (
SELECT ChargeNumber, RefPhyCode FROM Posting_Wilshire
INNER JOIN Daysheet ON ChargeNum=ChargeNumber
UNION ALL
SELECT ChargeNumber, RefPhyCode FROM Posting_Brea
INNER JOIN Daysheet ON ChargeNum=ChargeNumber
) AS a
INNER JOIN Servloc s ON s.Id=a.RefPhyCode

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cmmrfrds]

You say, "I get great response time from any kind of query on Daysheet". How do you measure this? Are these aggregate queries? If not, then you are not making a comparison that is similiar in processing. To check your queries make sure you go to the end of the recordset before stopping your timing comparison.

The Union query you show has to make a temporary table before it can began returning any records. A non-aggregate query can start returning records as soon as some qualify. Unsually the database engine (I not sure on Access) will start returning records when it has some number such as 500 or 1000 and usually this is a property that can be set in the client.