CODEWITHSUNDEEP
sqlms-access-2010
Brandon Wilson
Brandon Wilson

Reputation: 4600

Access Query MAX() Slows Query

I have the below Access Query and it works fine. However, it takes about 8-10 seconds to finish on a table that is about 700 records right now. The FROM is another query that has very little query time. I have narrowed it down to the MAX() function, because when I remove that function it runs with very little query time. What can I do to speed this up? I am going to assume as more data comes into the database the longer it will take to query. 

SELECT FirstName, LastName, TeamID, MAX(total) AS totalMax 
        FROM attendanceViewAll 
            WHERE TeamID IN(5,9,13) 
            GROUP BY FirstName, LastName, TeamID

Here is the Sub Query, basically it selects a bunch of data from a table. This happens in less than a second. The result of this query is everything ordered by date and agentID. I then use the above query to find the MAX(total) so I can group the agents for a summary. I use the below query for other reports as well.

SELECT 
    a1.TeamID,
    a1.FirstName,
    a1.LastName,
    a1.incurredDate, 
    a1.points, 
    a1.OneFallOff, 
    a1.TwoFallOff, 

    (select sum(a2.actualPoints) 
        from attendanceView as a2 where a2.agentID = a1.agentID and a2.incurredDate <= a1.incurredDate) as total, 

a1.comment, a1.linked, a1.FallOffDate
FROM attendanceView as a1;

Upvotes: 0

Views: 91

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123849

Your [attendanceViewAll] query is using a correlated subquery to produce a running total (ref: your previous question here). Now you are asking for the MAX() of that running total, which is the same thing as the SUM() of the [TwoFallOff] values. That is, for

incurredDate  TwoFallOff  total
------------  ----------  -----
  2014-01-10           2      2
  2014-01-11           3      5
  2014-01-12           1      6

MAX(total) is the same value as SUM(TwoFallOff). The big difference is that to get each value for [total] you need to run the correlated subquery, whereas to get each value for [TwoFallOff] you don't.

In other words, I suspect that your current query is slow because the MAX() is forcing the correlated subquery in [attendanceViewAll] to be executed many times. You may get faster response if you have your current query refer directly back to [attendanceView] and SUM() the [TwoFallOff] values from there.

Upvotes: 2

DRapp
DRapp

Reputation: 48179

What you need is a multiple-column index and it should be almost instantaneous.

Use the interface as this link describes if you need help on that. However, your index should be first on the criteria, secondary on the fields used in group by, so I would have an index on

TeamID, FirstName, LastName

Upvotes: 0

Related Questions