Re: Need advice on speeding query up

Tech-Archive recommends: Fix windows errors by optimizing your registry

I'm looking at your comment and also John Spenser's comment about the
date fields (txtStartMonth & txtStartYear) and I'm wondering if I can't
change that to something more efficient...

Those two fields are actually unbound text fields which are pointing to
specific columns of a combo box, which in turn points to a query. I
structured it like that so I could readily see what values were being
passed along to the query called qryCustomerAggregate - as you can
guess, I'm not too experienced at this so I need some reassurance as
I'm coding that I'm not making a misstep. The combo box is so the user
can readily choose which month he wishes. Here's the query that
populates the combo box, called qryMonthID:

SELECT Year([purchase_dte]) AS YearID, MonthName(Month([purchase_dte]))
AS MonthNameID, MonthName(Month([purchase_dte]),True) & "/" &
Right(Year([purchase_dte]),2) AS FullID, Year([purchase_dte]) &
IIf(Month([purchase_dte])<10,"0","") & Month([purchase_dte]) AS SortID,
Month([purchase_dte]) AS MonthID
FROM tblPurchasesSince_200511
GROUP BY Year([purchase_dte]), MonthName(Month([purchase_dte])),
MonthName(Month([purchase_dte]),True) & "/" &
Right(Year([purchase_dte]),2), Year([purchase_dte]) &
IIf(Month([purchase_dte])<10,"0","") & Month([purchase_dte]),
Month([purchase_dte])
ORDER BY Year([purchase_dte]) & IIf(Month([purchase_dte])<10,"0","") &
Month([purchase_dte]);

Which ends up generating something like this for each line:

YearID 2005
MonthNameID November
FullID Nov/05
SortID 200511
MonthID 11

There's only one visible field on the combo box, which is the FullID
field in the query. All the other fields in qryMonthID are present in
the combo box - among other things, the report all these querys end up
in makes use of the MonthNameID field in a couple of unbound text
boxes.

Also, just so you understand, the purchase_dte field is the a date
value, right down to the millisecond, and the earliest date in it is
late 2003 (the table name is a bit of a misnomer).

Is there a more efficient way of passing along a month & year
constraint to a query?

Jerry Whittle wrote:
Index tblPurchasesSince_200511 status_id, customer_short_name, and
purchase_dte.

Speaking of tblPurchasesSince_200511.purchase_dte, your use of the Year and
Month functions actually make an index on that column worthless plus have to
search the table twice. You would be better off having one field on the form
where the month and date is entered together like June 2006. Then the
criteria would look like:

"1 " & Forms!frmReportGeneration!txtStartDate

Index tblCustomer.[Short Name]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Jon" wrote:

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?



.

Relevant Pages

  • Re: Need advice on speeding query up
    ... txtStartMonth points to the MonthID ... does I would expect to see an error in your first query since the Month ...
    (microsoft.public.access.queries)
  • Re: Need advice on speeding query up
    ... Any field your queries use in ORDER BY, GROUP BY or WHERE clauses, and any ... into the next (a Crosstab query), and then the second query is fed to a ... The first query is called qryCustomerAggregate, ...
    (microsoft.public.access.queries)
  • Re: Need advice on speeding query up
    ... Jeff Boyce wrote: ... into the next (a Crosstab query), and then the second query is fed to a ... The first query is called qryCustomerAggregate, ...
    (microsoft.public.access.queries)
  • Re: Need advice on speeding query up
    ... field on which your queries sort provides a potential slow down. ... into the next (a Crosstab query), and then the second query is fed to a ... The first query is called qryCustomerAggregate, ...
    (microsoft.public.access.queries)
  • Re: Need advice on speeding query up
    ... Are you sure that txtStartMonth points to the MonthNameId column? ... does I would expect to see an error in your first query since the Month ... The first query is called qryCustomerAggregate, ...
    (microsoft.public.access.queries)