Re: "Group By" makes Simple Query Run FOREVER

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


saraqpost@xxxxxxxxx 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-----


saraqpost@xxxxxxxxx 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.


"saraqpost@xxxxxxxxx" 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?

.

Relevant Pages

  • Re: "Group By" makes Simple Query Run FOREVER
    ... All you need is just a query like this: ... SELECT POKey, SumAs ActualFreight ... FROM tblFreightBill ... total freight for each order. ...
    (microsoft.public.access.queries)
  • Re: "Group By" makes Simple Query Run FOREVER
    ... All you need is just a query like this: ... SELECT POKey, SumAs ActualFreight ... FROM tblFreightBill ... total freight for each order. ...
    (microsoft.public.access.queries)
  • Re: "Group By" makes Simple Query Run FOREVER
    ... ways of writing the query result in the same query execution ... c/Put in the field ActualFreight, with the function to calculate the ... FROM tblFreightBill ... total freight for each order. ...
    (microsoft.public.access.queries)
  • Re: "Group By" makes Simple Query Run FOREVER
    ... FROM tblFreightBill ... total freight for each order. ... The query is 2 simple fields: ... POKey and ActualFreight ...
    (microsoft.public.access.queries)
  • Re: "Group By" makes Simple Query Run FOREVER
    ... Does the running of a query automatically complile it? ... c/Put in the field ActualFreight, with the function to calculate the ... FROM tblFreightBill ... total freight for each order. ...
    (microsoft.public.access.queries)