long-running query Access 2k3

[galorin]

I have the following query which takes an absolute age to run in Access, but when run on MySQL is done in a flash. Access appears to lock up completely when I try to run the report generated from this query. Because the resultset is going to be used as a part of a nested subreport, I can't just run it s a passthrough query.

All of the referenced tables are standard tables, except for RepsForComps, which is a view on the MySQL Server. RepsForComps looks at a counties table, an employee table, and an override column in the Companies table to determine which employee a company is assigned to, given the county a company is in, and any override status.

Anyhow, my very slow query.

SELECT 
r.name
, p.[First Name] & " " & p.[last name] AS perName
, c.Company
, c.Address_1
, c.Address_2
, c.Town
, c.County
, c.Postcode
FROM users AS u 
RIGHT JOIN ((companies AS c 
INNER JOIN RepsForComps AS r ON c.Comp_ID = r.Comp_ID) 
INNER JOIN people AS p ON c.Comp_ID = p.Comp_ID) ON u.email = p.Email
WHERE (((u.time)>=[Forms]![FollowRep]![Range].[Form]![Start] 
AND (u.time)<=[Forms]![FollowRep]![Range].[Form]![End]))
GROUP BY r.name, p.[First Name] & " " & p.[last name], c.Company, c.Address_1, c.Address_2, c.Town, c.County, c.Postcode
ORDER BY Count(u.email);

 

[Remou]

You do not need Order By in a query that is to be used in a report, use the report to order the records. Similarly, you do not need Group By. You can group in the report and hide the detail records. Alternatively, you can restructure the query to return only a single line, SELECT DISTINCT, for example, or nested queries.

 

[PHV]

And due the WHERE clause you don't need a RIGHT join but an INNER join.

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

 

[galorin]

Got it running fast. Instead of relying on a view, I re-wrote my query def to do the lookup on its own. I still need grouping and sums, but I did it in SQL rather than in the report. It just made more sense to me to do it that way, now that speed isn't an issue