"Group By" makes Simple Query Run FOREVER

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

saraqpost@yahoo.com - 29 Jun 2006 19:00 GMT

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

Reply to this Message

Jerry Whittle - 29 Jun 2006 19:50 GMT

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"

Signature

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

> I am stumped! I have a query that uses a function to calculate the
> total freight for each order.
[quoted text clipped - 32 lines]
> Thanks
> Sara

Reply to this Message

saraqpost@yahoo.com - 29 Jun 2006 20:03 GMT

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

> 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
[quoted text clipped - 55 lines]
> > Thanks
> > Sara

Reply to this Message

MGFoster - 29 Jun 2006 23:02 GMT

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

Signature

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

> 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
[quoted text clipped - 61 lines]
>>>
>>>Any idea why? What should I do?

Reply to this Message

saraqpost@yahoo.com - 29 Jun 2006 23:35 GMT

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

MANY thanks.
Sara

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 85 lines]
> >>>
> >>>Any idea why? What should I do?

Reply to this Message

saraqpost@yahoo.com - 30 Jun 2006 15:14 GMT

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

> You should have heard my "OH!". It makes perfect sense. I'll make the
> change and post back with any problems.
[quoted text clipped - 91 lines]
> > >>>
> > >>>Any idea why? What should I do?

Reply to this Message

Marshall Barton - 30 Jun 2006 18:16 GMT

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.

Signature

Marsh
MVP [MS Access]

>MG - (If you check this post again) -
>It's WAY faster. So here's another - related question:
[quoted text clipped - 112 lines]
>> > >>>
>> > >>>Any idea why? What should I do?

Reply to this Message

saraqpost@yahoo.com - 30 Jun 2006 19:19 GMT

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

> Mike may have a specific suggestion, but in general this can
> be difficult to predict. Queries are optimized based on the
[quoted text clipped - 135 lines]
> >> > >>>
> >> > >>>Any idea why? What should I do?

Reply to this Message

Marshall Barton - 30 Jun 2006 23:45 GMT

Responses inline below

Signature

Marsh
MVP [MS Access]

>Thanks for the little lesson.
>I will try and see what happens - THAT I can do!
[quoted text clipped - 5 lines]
>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.

>> Mike may have a specific suggestion, but in general this can
>> be difficult to predict. Queries are optimized based on the
[quoted text clipped - 117 lines]
>> >> > >>>HAVING (((tblFreightBill.FreightBillStatus)="A"))
>> >> > >>>ORDER BY tblFreightBill.POKey;

Reply to this Message