Summary query running slow

[Bill Murphy]

I have a table containing 330,000 records representing the daily balances on
800 bank accounts. I need a query that will give me the latest date for
each account, in other words the most recent balance record. The summary
query below produces the right results but takes about 25 seconds to run.
Is there a way to construct this query to get quicker results?

SELECT tblBalances.Account, First(tblBalances.Date) AS FirstOfDate
FROM tblBalances
WHERE tblBalances.Date < #9/30/2004#
GROUP BY tblBalances.Account
ORDER BY First(tblBalances.Date) DESC;

Bill

 

[Steve Schapel]

Bill,

You could try this, and let us know whether it is any faster...
SELECT tblBalances.Account, Max(tblBalances.[Date]) AS MaxOfDate
FROM tblBalances
WHERE tblBalances.[Date] < #9/30/2004#
GROUP BY tblBalances.Account

Also, make sure you have an Index in the Date field.

By the way, as an aside, "date" is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.

 

[Bill Murphy]

Steve,

Thanks. I tried your code and the timing was about the same. I also tried
it after renaming the field Date in tblBalances to BalanceDate, and again
about the same timing. This date field is indexed. Do you have any further
thoughts on this?

Bill

Bill,

You could try this, and let us know whether it is any faster...
SELECT tblBalances.Account, Max(tblBalances.[Date]) AS MaxOfDate
FROM tblBalances
WHERE tblBalances.[Date] < #9/30/2004#
GROUP BY tblBalances.Account

Also, make sure you have an Index in the Date field.

By the way, as an aside, "date" is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.

--
Steve Schapel, Microsoft Access MVP

I have a table containing 330,000 records representing the daily balances on
800 bank accounts. I need a query that will give me the latest date for
each account, in other words the most recent balance record. The summary
query below produces the right results but takes about 25 seconds to run.
Is there a way to construct this query to get quicker results?

SELECT tblBalances.Account, First(tblBalances.Date) AS FirstOfDate
FROM tblBalances
WHERE tblBalances.Date < #9/30/2004#
GROUP BY tblBalances.Account
ORDER BY First(tblBalances.Date) DESC;

Bill

 

[John Spencer (MVP)]

One thing you might do if your data allows it is to narrow the date range so
less records are returned. But one thing you do want is to do is to use the MAX
function vice the FIRST function. First returns the first record found which is
not necessarily the first record in any order you would recognize.

If the Maximum date on all the accounts is known to be within the last year, you
could possibly speed this up by using criteria like.

WHERE [Date] Between #9/30/2003# and #9/30/2004#

If the max date is known to be within the last month, then you could use a date
range just for that month. Doing this may speed things up considerably since
the aggregation (finding the max) occurs after the records are selected. Or at
least that is my understanding.

Steve,

Thanks. I tried your code and the timing was about the same. I also tried
it after renaming the field Date in tblBalances to BalanceDate, and again
about the same timing. This date field is indexed. Do you have any further
thoughts on this?

Bill

Bill,

You could try this, and let us know whether it is any faster...
SELECT tblBalances.Account, Max(tblBalances.[Date]) AS MaxOfDate
FROM tblBalances
WHERE tblBalances.[Date] < #9/30/2004#
GROUP BY tblBalances.Account

Also, make sure you have an Index in the Date field.

By the way, as an aside, "date" is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.

--
Steve Schapel, Microsoft Access MVP

I have a table containing 330,000 records representing the daily balances on
800 bank accounts. I need a query that will give me the latest date for
each account, in other words the most recent balance record. The summary
query below produces the right results but takes about 25 seconds to run.
Is there a way to construct this query to get quicker results?

SELECT tblBalances.Account, First(tblBalances.Date) AS FirstOfDate
FROM tblBalances
WHERE tblBalances.Date < #9/30/2004#
GROUP BY tblBalances.Account
ORDER BY First(tblBalances.Date) DESC;

Bill

 

[John Spencer (MVP)]

Also, I would recommend an index of the ACCOUNT field. It is probably already
there since I would guess that the Account field is either a foreign or primary
key. Since you are aggregating by the Account field (Group by) I believe that
any index available will be used.

That is a guess on my part, but one based on the probable logic that would be used.

Steve,

Thanks. I tried your code and the timing was about the same. I also tried
it after renaming the field Date in tblBalances to BalanceDate, and again
about the same timing. This date field is indexed. Do you have any further
thoughts on this?

Bill

Bill,

You could try this, and let us know whether it is any faster...
SELECT tblBalances.Account, Max(tblBalances.[Date]) AS MaxOfDate
FROM tblBalances
WHERE tblBalances.[Date] < #9/30/2004#
GROUP BY tblBalances.Account

Also, make sure you have an Index in the Date field.

By the way, as an aside, "date" is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.

--
Steve Schapel, Microsoft Access MVP

I have a table containing 330,000 records representing the daily balances on
800 bank accounts. I need a query that will give me the latest date for
each account, in other words the most recent balance record. The summary
query below produces the right results but takes about 25 seconds to run.
Is there a way to construct this query to get quicker results?

SELECT tblBalances.Account, First(tblBalances.Date) AS FirstOfDate
FROM tblBalances
WHERE tblBalances.Date < #9/30/2004#
GROUP BY tblBalances.Account
ORDER BY First(tblBalances.Date) DESC;

Bill

 

[Bill Murphy]

John,

Thanks for your thoughts on this. Unfortunately, I'm not able to establish
a smaller date range, since some of these bank accounts could have balances
that are dated back past one year, at some undetermined point. You are
right, I do have an index on the Account field.

The only workaround I have found for this is to create a table at each month
end which stores the balance of each account as of that date, so I'm able to
use this table in queries and capture the balance as of that date. If an
account has a balance record later than that date then I only have to do a
query to capture it's later balance. This approach probably cuts the time
down by about 2/3.

Bill

Also, I would recommend an index of the ACCOUNT field. It is probably already
there since I would guess that the Account field is either a foreign or primary
key. Since you are aggregating by the Account field (Group by) I believe that
any index available will be used.

That is a guess on my part, but one based on the probable logic that would be used.

Steve,

Thanks. I tried your code and the timing was about the same. I also tried
it after renaming the field Date in tblBalances to BalanceDate, and again
about the same timing. This date field is indexed. Do you have any further
thoughts on this?

Bill

Bill,

You could try this, and let us know whether it is any faster...
SELECT tblBalances.Account, Max(tblBalances.[Date]) AS MaxOfDate
FROM tblBalances
WHERE tblBalances.[Date] < #9/30/2004#
GROUP BY tblBalances.Account

Also, make sure you have an Index in the Date field.

By the way, as an aside, "date" is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control or database object.

--
Steve Schapel, Microsoft Access MVP


Bill Murphy wrote:
I have a table containing 330,000 records representing the daily balances on
800 bank accounts. I need a query that will give me the latest date for
each account, in other words the most recent balance record. The summary
query below produces the right results but takes about 25 seconds to run.
Is there a way to construct this query to get quicker results?

SELECT tblBalances.Account, First(tblBalances.Date) AS FirstOfDate
FROM tblBalances
WHERE tblBalances.Date < #9/30/2004#
GROUP BY tblBalances.Account
ORDER BY First(tblBalances.Date) DESC;

Bill