SELECT STATEMENT OR DOMAIN AGGREGATE

G

Glint

Hi All,
I have a query like this:

SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode;

There are only 1356 records in the vCashTransactions0 recordset, which has
TDate as a date field, Amount and TAmount fields as currency, others as long
integer. When I run this query, it is executed in 2 seconds or less, judging
by the time it takes to display "Record: 1 of 1356" in the Navigation button.

When I replace the domain aggregate with a select statement,

TAmount: (SELECT Sum(Amount) FROM vCashTransactions0 WHERE ACCode=AccountID)

This time it takes at least 15 seconds for the Navigation button to finish
displaying.

I had the impression that a SELECT statement was always preferable to a
domain aggregate function, but how do I explain the time difference in
execution this occasion? Can you guess what I am doing wrong? Execution time
is of importance because the table will grow to hundreds of thousand records.
 
M

Michel Walsh

The query execution can be asynchronous. To know how long it really takes,
you can try a MoveLast, but even then, in the first query, the DSum will be
executed only for the visible records (which will be 2, the first one and
the last one you saw, after the MoveLast)., as would be any VBA function
appearing ONLY in the SELECT clause.

In the second query, the sub query is executed for all the records, even
those you don't see on your form / in query data view.

You can force the execution of the DSum, in the first query, to occur for
all rows! Add an ORDER BY involving it:



SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode
ORDER BY DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID])




and then, DSum will be executed n+1 times (minimum) : n times to establish
the sorting, one another for the display of the visible record,... and once
more (at least) each time you display another record.



Hoping it may help,
Vanderghast, Access MVP
 
G

Glint

Thanks, Michel.
What I want is a way to to make things faster when I load the form or set
its parameters. I want a summary of the records based on the AccountID field.
When I select Unique Records on the query properties, it takes eternity to
load.
--
Glint


Michel Walsh said:
The query execution can be asynchronous. To know how long it really takes,
you can try a MoveLast, but even then, in the first query, the DSum will be
executed only for the visible records (which will be 2, the first one and
the last one you saw, after the MoveLast)., as would be any VBA function
appearing ONLY in the SELECT clause.

In the second query, the sub query is executed for all the records, even
those you don't see on your form / in query data view.

You can force the execution of the DSum, in the first query, to occur for
all rows! Add an ORDER BY involving it:



SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode
ORDER BY DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID])




and then, DSum will be executed n+1 times (minimum) : n times to establish
the sorting, one another for the display of the visible record,... and once
more (at least) each time you display another record.



Hoping it may help,
Vanderghast, Access MVP



Glint said:
Hi All,
I have a query like this:

SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode;

There are only 1356 records in the vCashTransactions0 recordset, which has
TDate as a date field, Amount and TAmount fields as currency, others as
long
integer. When I run this query, it is executed in 2 seconds or less,
judging
by the time it takes to display "Record: 1 of 1356" in the Navigation
button.

When I replace the domain aggregate with a select statement,

TAmount: (SELECT Sum(Amount) FROM vCashTransactions0 WHERE
ACCode=AccountID)

This time it takes at least 15 seconds for the Navigation button to finish
displaying.

I had the impression that a SELECT statement was always preferable to a
domain aggregate function, but how do I explain the time difference in
execution this occasion? Can you guess what I am doing wrong? Execution
time
is of importance because the table will grow to hundreds of thousand
records.
Click to expand...
Click to expand...
 
M

Michel Walsh

I would try to use a third query:


SELECT ACCode, SUM(amount) As SubByACCode
FROM vCashTransactions0
GROUP BY ACCode


Then, in your original query, add the query you just created, join it to
Accounts through the AccountID and the new query Accode. Remove the DSum,
and instead of it, bring the SubByACCode value, of the new query.


If you don't have an index on ACCode, or on AccountID, add one.



Hoping it may help,
Vanderghast, Access MVP



Glint said:
Thanks, Michel.
What I want is a way to to make things faster when I load the form or set
its parameters. I want a summary of the records based on the AccountID
field.
When I select Unique Records on the query properties, it takes eternity to
load.
--
Glint


Michel Walsh said:
The query execution can be asynchronous. To know how long it really
takes,
you can try a MoveLast, but even then, in the first query, the DSum will
be
executed only for the visible records (which will be 2, the first one and
the last one you saw, after the MoveLast)., as would be any VBA function
appearing ONLY in the SELECT clause.

In the second query, the sub query is executed for all the records, even
those you don't see on your form / in query data view.

You can force the execution of the DSum, in the first query, to occur for
all rows! Add an ORDER BY involving it:



SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode
ORDER BY DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID])




and then, DSum will be executed n+1 times (minimum) : n times to
establish
the sorting, one another for the display of the visible record,... and
once
more (at least) each time you display another record.



Hoping it may help,
Vanderghast, Access MVP



Glint said:
Hi All,
I have a query like this:

SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode;

There are only 1356 records in the vCashTransactions0 recordset, which
has
TDate as a date field, Amount and TAmount fields as currency, others as
long
integer. When I run this query, it is executed in 2 seconds or less,
judging
by the time it takes to display "Record: 1 of 1356" in the Navigation
button.

When I replace the domain aggregate with a select statement,

TAmount: (SELECT Sum(Amount) FROM vCashTransactions0 WHERE
ACCode=AccountID)

This time it takes at least 15 seconds for the Navigation button to
finish
displaying.

I had the impression that a SELECT statement was always preferable to a
domain aggregate function, but how do I explain the time difference in
execution this occasion? Can you guess what I am doing wrong? Execution
time
is of importance because the table will grow to hundreds of thousand
records.
Click to expand...
Click to expand...
Click to expand...
 
G

Glint

Michel,
That was wonderful. Please one more thing: I need some columns on the query
to display selected item totals, like for (ACCode=30 or ACCode=34), or
ACCode<>34, things like that.
I appreciate your effort so far.
--
Glint


Michel Walsh said:
I would try to use a third query:


SELECT ACCode, SUM(amount) As SubByACCode
FROM vCashTransactions0
GROUP BY ACCode


Then, in your original query, add the query you just created, join it to
Accounts through the AccountID and the new query Accode. Remove the DSum,
and instead of it, bring the SubByACCode value, of the new query.


If you don't have an index on ACCode, or on AccountID, add one.



Hoping it may help,
Vanderghast, Access MVP



Glint said:
Thanks, Michel.
What I want is a way to to make things faster when I load the form or set
its parameters. I want a summary of the records based on the AccountID
field.
When I select Unique Records on the query properties, it takes eternity to
load.
--
Glint


Michel Walsh said:
The query execution can be asynchronous. To know how long it really
takes,
you can try a MoveLast, but even then, in the first query, the DSum will
be
executed only for the visible records (which will be 2, the first one and
the last one you saw, after the MoveLast)., as would be any VBA function
appearing ONLY in the SELECT clause.

In the second query, the sub query is executed for all the records, even
those you don't see on your form / in query data view.

You can force the execution of the DSum, in the first query, to occur for
all rows! Add an ORDER BY involving it:



SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode
ORDER BY DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID])




and then, DSum will be executed n+1 times (minimum) : n times to
establish
the sorting, one another for the display of the visible record,... and
once
more (at least) each time you display another record.



Hoping it may help,
Vanderghast, Access MVP



Hi All,
I have a query like this:

SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode;

There are only 1356 records in the vCashTransactions0 recordset, which
has
TDate as a date field, Amount and TAmount fields as currency, others as
long
integer. When I run this query, it is executed in 2 seconds or less,
judging
by the time it takes to display "Record: 1 of 1356" in the Navigation
button.

When I replace the domain aggregate with a select statement,

TAmount: (SELECT Sum(Amount) FROM vCashTransactions0 WHERE
ACCode=AccountID)

This time it takes at least 15 seconds for the Navigation button to
finish
displaying.

I had the impression that a SELECT statement was always preferable to a
domain aggregate function, but how do I explain the time difference in
execution this occasion? Can you guess what I am doing wrong? Execution
time
is of importance because the table will grow to hundreds of thousand
records.
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

If the computation does not depends of any column from the record itself,
you can use a DSUM, and it should be fast, since being NOT dependant of ANY
column, that expression becomes a CONSTANT and is computed just once, by the
query optimizer, for all the rows of the result.

If the result depends on some column of the record, proceed as you just did
for the SUM which was dependant of the value ACCode.



Hoping it may help,
Vanderghast, Access MVP


Glint said:
Michel,
That was wonderful. Please one more thing: I need some columns on the
query
to display selected item totals, like for (ACCode=30 or ACCode=34), or
ACCode<>34, things like that.
I appreciate your effort so far.
--
Glint


Michel Walsh said:
I would try to use a third query:


SELECT ACCode, SUM(amount) As SubByACCode
FROM vCashTransactions0
GROUP BY ACCode


Then, in your original query, add the query you just created, join it to
Accounts through the AccountID and the new query Accode. Remove the
DSum,
and instead of it, bring the SubByACCode value, of the new query.


If you don't have an index on ACCode, or on AccountID, add one.



Hoping it may help,
Vanderghast, Access MVP



Glint said:
Thanks, Michel.
What I want is a way to to make things faster when I load the form or
set
its parameters. I want a summary of the records based on the AccountID
field.
When I select Unique Records on the query properties, it takes eternity
to
load.
--
Glint


:

The query execution can be asynchronous. To know how long it really
takes,
you can try a MoveLast, but even then, in the first query, the DSum
will
be
executed only for the visible records (which will be 2, the first one
and
the last one you saw, after the MoveLast)., as would be any VBA
function
appearing ONLY in the SELECT clause.

In the second query, the sub query is executed for all the records,
even
those you don't see on your form / in query data view.

You can force the execution of the DSum, in the first query, to occur
for
all rows! Add an ORDER BY involving it:



SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode
ORDER BY DSum("[Amount]","vCashTransactions0","[ACCode]=" &
[AccountID])




and then, DSum will be executed n+1 times (minimum) : n times to
establish
the sorting, one another for the display of the visible record,... and
once
more (at least) each time you display another record.



Hoping it may help,
Vanderghast, Access MVP



Hi All,
I have a query like this:

SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode;

There are only 1356 records in the vCashTransactions0 recordset,
which
has
TDate as a date field, Amount and TAmount fields as currency, others
as
long
integer. When I run this query, it is executed in 2 seconds or less,
judging
by the time it takes to display "Record: 1 of 1356" in the
Navigation
button.

When I replace the domain aggregate with a select statement,

TAmount: (SELECT Sum(Amount) FROM vCashTransactions0 WHERE
ACCode=AccountID)

This time it takes at least 15 seconds for the Navigation button to
finish
displaying.

I had the impression that a SELECT statement was always preferable
to a
domain aggregate function, but how do I explain the time difference
in
execution this occasion? Can you guess what I am doing wrong?
Execution
time
is of importance because the table will grow to hundreds of thousand
records.
Click to expand...
Click to expand...
Click to expand...
 
G

Glint

Again, Michel, you have been wonderful. Thanks.
--
Glint


Michel Walsh said:
If the computation does not depends of any column from the record itself,
you can use a DSUM, and it should be fast, since being NOT dependant of ANY
column, that expression becomes a CONSTANT and is computed just once, by the
query optimizer, for all the rows of the result.

If the result depends on some column of the record, proceed as you just did
for the SUM which was dependant of the value ACCode.



Hoping it may help,
Vanderghast, Access MVP


Glint said:
Michel,
That was wonderful. Please one more thing: I need some columns on the
query
to display selected item totals, like for (ACCode=30 or ACCode=34), or
ACCode<>34, things like that.
I appreciate your effort so far.
--
Glint


Michel Walsh said:
I would try to use a third query:


SELECT ACCode, SUM(amount) As SubByACCode
FROM vCashTransactions0
GROUP BY ACCode


Then, in your original query, add the query you just created, join it to
Accounts through the AccountID and the new query Accode. Remove the
DSum,
and instead of it, bring the SubByACCode value, of the new query.


If you don't have an index on ACCode, or on AccountID, add one.



Hoping it may help,
Vanderghast, Access MVP



Thanks, Michel.
What I want is a way to to make things faster when I load the form or
set
its parameters. I want a summary of the records based on the AccountID
field.
When I select Unique Records on the query properties, it takes eternity
to
load.
--
Glint


:

The query execution can be asynchronous. To know how long it really
takes,
you can try a MoveLast, but even then, in the first query, the DSum
will
be
executed only for the visible records (which will be 2, the first one
and
the last one you saw, after the MoveLast)., as would be any VBA
function
appearing ONLY in the SELECT clause.

In the second query, the sub query is executed for all the records,
even
those you don't see on your form / in query data view.

You can force the execution of the DSum, in the first query, to occur
for
all rows! Add an ORDER BY involving it:



SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode
ORDER BY DSum("[Amount]","vCashTransactions0","[ACCode]=" &
[AccountID])




and then, DSum will be executed n+1 times (minimum) : n times to
establish
the sorting, one another for the display of the visible record,... and
once
more (at least) each time you display another record.



Hoping it may help,
Vanderghast, Access MVP



Hi All,
I have a query like this:

SELECT vCashTransactions0.Zone, vCashTransactions0.TDate,
vCashTransactions0.ACCode, vCashTransactions0.Amount,
vCashTransactions0.ItemAmount, Accounts.AccountID,
DSum("[Amount]","vCashTransactions0","[ACCode]=" & [AccountID]) AS
TAmount1
FROM Accounts INNER JOIN vCashTransactions0 ON Accounts.AccountID =
vCashTransactions0.ACCode;

There are only 1356 records in the vCashTransactions0 recordset,
which
has
TDate as a date field, Amount and TAmount fields as currency, others
as
long
integer. When I run this query, it is executed in 2 seconds or less,
judging
by the time it takes to display "Record: 1 of 1356" in the
Navigation
button.

When I replace the domain aggregate with a select statement,

TAmount: (SELECT Sum(Amount) FROM vCashTransactions0 WHERE
ACCode=AccountID)

This time it takes at least 15 seconds for the Navigation button to
finish
displaying.

I had the impression that a SELECT statement was always preferable
to a
domain aggregate function, but how do I explain the time difference
in
execution this occasion? Can you guess what I am doing wrong?
Execution
time
is of importance because the table will grow to hundreds of thousand
records.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FORM DOES NOT POPULATE WITH A2007 1
SUMMARIES REPORTING 3
Select Top with aggregate function 1
ERROR: aggregate function? 4
Domain Aggregate Functions 4
domain aggregate functions on linked spreadsheets? 5
ACCOUNT SUMMARIES 1
Select statement in VBA 11

Top