ACCESS FEED: Re: [MS_AccessPros] Query speed

Selasa, 02 Juni 2015

Re: [MS_AccessPros] Query speed

Ken-

That's always going to run slowly in Access because of the correlated subquery. If you don't need an updatable query, I would normally recommend moving the subquery to do a JOIN, but your criteria (< and IS Null) won't allow you to do that. Make sure there are indexes on the meter IDs, the Meter Name, and the ReadingDate.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jun 2, 2015, at 6:00 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ken,
What fields have indexes?

Duane

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 2 Jun 2015 08:51:19 -0700
Subject: [MS_AccessPros] Query speed

I have several queries that are causing the main query to run slow.

SELECT M.[Meter Name], R1.ReadingDate, R1.MeterReading, R2.ReadingDate AS PrevReadingDate, R2.MeterReading AS PrevMeterReading, [R1].[MeterReading]-([PrevMeterReading]+[R2].[Refill]) AS Use, Abs([Use]) AS TTLUsed, R2.Refill, R2.[Floride_Added_#]
FROM [Meter Information] AS M INNER JOIN ([Water Meter Readings] AS R1 LEFT JOIN [Water Meter Readings] AS R2 ON R1.frg_MeterID = R2.frg_MeterID) ON M.MeterID = R1.frg_MeterID
WHERE (((M.[Meter Name])="Fluoride") AND ((R2.ReadingDate)=(SELECT Max(R3.ReadingDate)
      FROM [Water Meter Readings] AS R3
      WHERE (R3.frg_MeterID = R1.frg_MeterID)
        AND (R3.ReadingDate < R1.ReadingDate)
    ))) OR (((R2.frg_MeterID) Is Null))
ORDER BY M.[Meter Name], R1.ReadingDate;

Any help is appreciated.
Ken

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group