"Group By" makes Simple Query Run FOREVER

I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


Any idea why? What should I do?

Thanks
Sara

Try one of the following. There's a very good chance that the Group By is
making it run through your function on record at a time instead of doing all
the records at one.

SELECT DISTINCT P.POKEY, P.ActualFreight
From (
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus)="A") as P
ORDER BY P.POKey;

or

SELECT DISTINCT tblFreightBill.POKey,
fcnGetTotalBilledFreight([POKey]) ASActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus="A"

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


" wrote:

I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


Any idea why? What should I do?

Thanks
Sara

Thanks, but neither worked. I can see where your hypothesis of running
the function one record at a time is likely valid, as when I cancel the
query while it's running, and choose "debug", I am always led to the
DSUM line in my function.

Any other ideas?

Sara



Jerry Whittle wrote:
Try one of the following. There's a very good chance that the Group By is
making it run through your function on record at a time instead of doing all
the records at one.

SELECT DISTINCT P.POKEY, P.ActualFreight
From (
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus)="A") as P
ORDER BY P.POKey;

or

SELECT DISTINCT tblFreightBill.POKey,
fcnGetTotalBilledFreight([POKey]) ASActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus="A"

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


" wrote:

I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


Any idea why? What should I do?

Thanks
Sara

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need that function, unless it is doing more than run the
DSum() function. All you need is just a query like this:

SELECT POKey, Sum(Freight) As ActualFreight
FROM tblFreightBill
WHERE FreightBillStatus="A"
GROUP BY POKey

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKROF4echKqOuFEgEQLy9ACg5AqbkkERN325AlknR/AXNoxmUpIAn3Gz
TprtUSKVgFs1PUF62OyEkc1q
=Wd9s
-----END PGP SIGNATURE-----


wrote:
Thanks, but neither worked. I can see where your hypothesis of running
the function one record at a time is likely valid, as when I cancel the
query while it's running, and choose "debug", I am always led to the
DSUM line in my function.

Any other ideas?

Sara



Jerry Whittle wrote:

Try one of the following. There's a very good chance that the Group By is
making it run through your function on record at a time instead of doing all
the records at one.

SELECT DISTINCT P.POKEY, P.ActualFreight
From (
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus)="A") as P
ORDER BY P.POKey;

or

SELECT DISTINCT tblFreightBill.POKey,
fcnGetTotalBilledFreight([POKey]) ASActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus="A"

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


" wrote:


I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


Any idea why? What should I do?

You should have heard my "OH!". It makes perfect sense. I'll make the
change and post back with any problems.

MANY thanks.
Sara


MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need that function, unless it is doing more than run the
DSum() function. All you need is just a query like this:

SELECT POKey, Sum(Freight) As ActualFreight
FROM tblFreightBill
WHERE FreightBillStatus="A"
GROUP BY POKey

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKROF4echKqOuFEgEQLy9ACg5AqbkkERN325AlknR/AXNoxmUpIAn3Gz
TprtUSKVgFs1PUF62OyEkc1q
=Wd9s
-----END PGP SIGNATURE-----


wrote:
Thanks, but neither worked. I can see where your hypothesis of running
the function one record at a time is likely valid, as when I cancel the
query while it's running, and choose "debug", I am always led to the
DSUM line in my function.

Any other ideas?

Sara



Jerry Whittle wrote:

Try one of the following. There's a very good chance that the Group By is
making it run through your function on record at a time instead of doing all
the records at one.

SELECT DISTINCT P.POKEY, P.ActualFreight
From (
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus)="A") as P
ORDER BY P.POKey;

or

SELECT DISTINCT tblFreightBill.POKey,
fcnGetTotalBilledFreight([POKey]) ASActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus="A"

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


" wrote:


I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


Any idea why? What should I do?

MG - (If you check this post again) -
It's WAY faster. So here's another - related question:

Is it better (faster) to:
a/ Bring in another table (trblFreightBill) and do the SUM(Freight)
in a query that needs total freight, but nothing else from
tblFreightBill
OR
b/ Bring in the query that runs (really fast, now, thank you) and
pull in the ActualFreight filed calculated by this query
OR
c/Put in the field ActualFreight, with the function to calculate the
ActualFreight.

I can see that making the wrong choice can be painful for the user, but
I don't know what's more efficient/faster.

Any guidelines would be appreciated.

Sara


wrote:
You should have heard my "OH!". It makes perfect sense. I'll make the
change and post back with any problems.

MANY thanks.
Sara


MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need that function, unless it is doing more than run the
DSum() function. All you need is just a query like this:

SELECT POKey, Sum(Freight) As ActualFreight
FROM tblFreightBill
WHERE FreightBillStatus="A"
GROUP BY POKey

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKROF4echKqOuFEgEQLy9ACg5AqbkkERN325AlknR/AXNoxmUpIAn3Gz
TprtUSKVgFs1PUF62OyEkc1q
=Wd9s
-----END PGP SIGNATURE-----


wrote:
Thanks, but neither worked. I can see where your hypothesis of running
the function one record at a time is likely valid, as when I cancel the
query while it's running, and choose "debug", I am always led to the
DSUM line in my function.

Any other ideas?

Sara



Jerry Whittle wrote:

Try one of the following. There's a very good chance that the Group By is
making it run through your function on record at a time instead of doing all
the records at one.

SELECT DISTINCT P.POKEY, P.ActualFreight
From (
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus)="A") as P
ORDER BY P.POKey;

or

SELECT DISTINCT tblFreightBill.POKey,
fcnGetTotalBilledFreight([POKey]) ASActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus="A"

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


" wrote:


I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


Any idea why? What should I do?

Mike may have a specific suggestion, but in general this can
be difficult to predict. Queries are optimized based on the
general patten of data, so there are times where different
ways of writing the query result in the same query execution
plan. OTOH, the same query may use a different query plan
when there are only a few data records than after a lot of
records have been added to the tables.

Note that all saved queries are marked uncompiled whenever
you Compact the database so the performance may change at
that time. Also note that compiling the queries the first
time they are used after a Compact will distort the timing
picture. Another consideration is that data caching will
generally improve performance if the query is executed
multiple times in the same Access session.

Your best bet is to try the alternatives under different
circumstances and see if there is a noticable difference in
speed.
--
Marsh
MVP [MS Access]


wrote:

MG - (If you check this post again) -
It's WAY faster. So here's another - related question:

Is it better (faster) to:
a/ Bring in another table (trblFreightBill) and do the SUM(Freight)
in a query that needs total freight, but nothing else from
tblFreightBill
OR
b/ Bring in the query that runs (really fast, now, thank you) and
pull in the ActualFreight filed calculated by this query
OR
c/Put in the field ActualFreight, with the function to calculate the
ActualFreight.

I can see that making the wrong choice can be painful for the user, but
I don't know what's more efficient/faster.

Any guidelines would be appreciated.

Sara


wrote:
You should have heard my "OH!". It makes perfect sense. I'll make the
change and post back with any problems.

MANY thanks.
Sara


MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need that function, unless it is doing more than run the
DSum() function. All you need is just a query like this:

SELECT POKey, Sum(Freight) As ActualFreight
FROM tblFreightBill
WHERE FreightBillStatus="A"
GROUP BY POKey

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKROF4echKqOuFEgEQLy9ACg5AqbkkERN325AlknR/AXNoxmUpIAn3Gz
TprtUSKVgFs1PUF62OyEkc1q
=Wd9s
-----END PGP SIGNATURE-----


wrote:
Thanks, but neither worked. I can see where your hypothesis of running
the function one record at a time is likely valid, as when I cancel the
query while it's running, and choose "debug", I am always led to the
DSUM line in my function.

Any other ideas?

Sara



Jerry Whittle wrote:

Try one of the following. There's a very good chance that the Group By is
making it run through your function on record at a time instead of doing all
the records at one.

SELECT DISTINCT P.POKEY, P.ActualFreight
From (
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus)="A") as P
ORDER BY P.POKey;

or

SELECT DISTINCT tblFreightBill.POKey,
fcnGetTotalBilledFreight([POKey]) ASActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus="A"

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


" wrote:


I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


Any idea why? What should I do?

Thanks for the little lesson.
I will try and see what happens - THAT I can do!

I am wondering about your comment on "marked uncompiled whenever you
Compact the database". Whenever I make a new .mde there is an
auto-compact.

Should I run all reports before releasing to the user whenever I make a
new .mde to "speed it up"?

Is there a way to "compile all" in an .mde?

Or am I mis-understanding what you've said here?

Does the running of a query automatically complile it?

I'm still very new at Access, especially functions and coding, but my
view is that this is the time I form my habits. If I know what is the
best to do, I just start doing it now, while learning, and then it's a
habit rather than a fix I have to apply later. (Of couse I know this
won't be perfect).

That said, I really appreciate this site and the time all you MVPs
devote to our problems. I would say that I find answers via a search
in about 75% of the cases - without my own post!

Sara


Marshall Barton wrote:
Mike may have a specific suggestion, but in general this can
be difficult to predict. Queries are optimized based on the
general patten of data, so there are times where different
ways of writing the query result in the same query execution
plan. OTOH, the same query may use a different query plan
when there are only a few data records than after a lot of
records have been added to the tables.

Note that all saved queries are marked uncompiled whenever
you Compact the database so the performance may change at
that time. Also note that compiling the queries the first
time they are used after a Compact will distort the timing
picture. Another consideration is that data caching will
generally improve performance if the query is executed
multiple times in the same Access session.

Your best bet is to try the alternatives under different
circumstances and see if there is a noticable difference in
speed.
--
Marsh
MVP [MS Access]


wrote:

MG - (If you check this post again) -
It's WAY faster. So here's another - related question:

Is it better (faster) to:
a/ Bring in another table (trblFreightBill) and do the SUM(Freight)
in a query that needs total freight, but nothing else from
tblFreightBill
OR
b/ Bring in the query that runs (really fast, now, thank you) and
pull in the ActualFreight filed calculated by this query
OR
c/Put in the field ActualFreight, with the function to calculate the
ActualFreight.

I can see that making the wrong choice can be painful for the user, but
I don't know what's more efficient/faster.

Any guidelines would be appreciated.

Sara


wrote:
You should have heard my "OH!". It makes perfect sense. I'll make the
change and post back with any problems.

MANY thanks.
Sara


MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need that function, unless it is doing more than run the
DSum() function. All you need is just a query like this:

SELECT POKey, Sum(Freight) As ActualFreight
FROM tblFreightBill
WHERE FreightBillStatus="A"
GROUP BY POKey

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKROF4echKqOuFEgEQLy9ACg5AqbkkERN325AlknR/AXNoxmUpIAn3Gz
TprtUSKVgFs1PUF62OyEkc1q
=Wd9s
-----END PGP SIGNATURE-----


wrote:
Thanks, but neither worked. I can see where your hypothesis of running
the function one record at a time is likely valid, as when I cancel the
query while it's running, and choose "debug", I am always led to the
DSUM line in my function.

Any other ideas?

Sara



Jerry Whittle wrote:

Try one of the following. There's a very good chance that the Group By is
making it run through your function on record at a time instead of doing all
the records at one.

SELECT DISTINCT P.POKEY, P.ActualFreight
From (
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus)="A") as P
ORDER BY P.POKey;

or

SELECT DISTINCT tblFreightBill.POKey,
fcnGetTotalBilledFreight([POKey]) ASActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus="A"

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


" wrote:


I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


Any idea why? What should I do?

Responses inline below
--
Marsh
MVP [MS Access]


wrote:

Thanks for the little lesson.
I will try and see what happens - THAT I can do!

I am wondering about your comment on "marked uncompiled whenever you
Compact the database". Whenever I make a new .mde there is an
auto-compact.

Should I run all reports before releasing to the user whenever I make a
new .mde to "speed it up"?

I suppose you could if it were a serious issue, but mostly
it's not worth the trouble. Don't forget the saved queries,
which are usually more complex than an SQL statement in a
report's record source/

My comment was aimed more at interpreting the results of a
performance analysis and the circumstances that can distort
the performance picture you would want to develop.


Is there a way to "compile all" in an .mde?

Just open the query in datasheet view or anything that runs
that uses the query.


Or am I mis-understanding what you've said here?

Yes you did misunderstand what I intended to convey, but
that's my fault for not being more explicit. However, what
you are questioning is also an issue, even if it is normally
not noticeable.


Does the running of a query automatically complile it?

Yes


I'm still very new at Access, especially functions and coding, but my
view is that this is the time I form my habits. If I know what is the
best to do, I just start doing it now, while learning, and then it's a
habit rather than a fix I have to apply later. (Of couse I know this
won't be perfect).

Absolutely!


That said, I really appreciate this site and the time all you MVPs
devote to our problems. I would say that I find answers via a search
in about 75% of the cases - without my own post!

The newsgroups are an incredible resource. It's where I
learned a lot of the tricks in Access.

I hope Mike comes back with some ideas about your query so I
can learn even more.


Marshall Barton wrote:
Mike may have a specific suggestion, but in general this can
be difficult to predict. Queries are optimized based on the
general patten of data, so there are times where different
ways of writing the query result in the same query execution
plan. OTOH, the same query may use a different query plan
when there are only a few data records than after a lot of
records have been added to the tables.

Note that all saved queries are marked uncompiled whenever
you Compact the database so the performance may change at
that time. Also note that compiling the queries the first
time they are used after a Compact will distort the timing
picture. Another consideration is that data caching will
generally improve performance if the query is executed
multiple times in the same Access session.

Your best bet is to try the alternatives under different
circumstances and see if there is a noticable difference in
speed.


wrote:

MG - (If you check this post again) -
It's WAY faster. So here's another - related question:

Is it better (faster) to:
a/ Bring in another table (trblFreightBill) and do the SUM(Freight)
in a query that needs total freight, but nothing else from
tblFreightBill
OR
b/ Bring in the query that runs (really fast, now, thank you) and
pull in the ActualFreight filed calculated by this query
OR
c/Put in the field ActualFreight, with the function to calculate the
ActualFreight.

I can see that making the wrong choice can be painful for the user, but
I don't know what's more efficient/faster.

Any guidelines would be appreciated.


wrote:
You should have heard my "OH!". It makes perfect sense. I'll make the
change and post back with any problems.


MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't need that function, unless it is doing more than run the
DSum() function. All you need is just a query like this:

SELECT POKey, Sum(Freight) As ActualFreight
FROM tblFreightBill
WHERE FreightBillStatus="A"
GROUP BY POKey

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKROF4echKqOuFEgEQLy9ACg5AqbkkERN325AlknR/AXNoxmUpIAn3Gz
TprtUSKVgFs1PUF62OyEkc1q
=Wd9s
-----END PGP SIGNATURE-----


wrote:
Thanks, but neither worked. I can see where your hypothesis of running
the function one record at a time is likely valid, as when I cancel the
query while it's running, and choose "debug", I am always led to the
DSUM line in my function.


Jerry Whittle wrote:

Try one of the following. There's a very good chance that the Group By is
making it run through your function on record at a time instead of doing all
the records at one.

SELECT DISTINCT P.POKEY, P.ActualFreight
From (
SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus)="A") as P
ORDER BY P.POKey;

or

SELECT DISTINCT tblFreightBill.POKey,
fcnGetTotalBilledFreight([POKey]) ASActualFreight
FROM tblFreightBill
WHERE tblFreightBill.FreightBillStatus="A"


" wrote:
I am stumped! I have a query that uses a function to calculate the
total freight for each order.

curTotFreight = Nz(DSum("[Freight]", "tblFreightBill", _
"[POKey]= " & lngPOKey & " AND [FreightBillStatus] = 'A'"), 0)
(lngPOKey is passed in)

The query is 2 simple fields:
POKey and ActualFreight

It runs in about a second as: (No "Group By")

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
WHERE (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;


But I noticed that the results duplicate the order number: If an order
has 3 separate freight charges, there are 3 entries on the
tblFreightBill, and (obviously) the actual freight is the same for each
time the PO Freight is calculated. SO, I said "Group by" to eliminate
the dups. The query ran for 20 minutes before I killed it!

SELECT tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]) AS
ActualFreight
FROM tblFreightBill
GROUP BY tblFreightBill.POKey, fcnGetTotalBilledFreight([POKey]),
tblFreightBill.FreightBillStatus
HAVING (((tblFreightBill.FreightBillStatus)="A"))
ORDER BY tblFreightBill.POKey;