RE: Need advice on speeding query up
- From: Jerry Whittle <JerryWhittle@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 19 Jun 2006 11:23:02 -0700
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?
- Follow-Ups:
- Re: Need advice on speeding query up
- From: Jon
- Re: Need advice on speeding query up
- References:
- Need advice on speeding query up
- From: Jon
- Need advice on speeding query up
- Prev by Date: Removing a duplicate
- Next by Date: Re: Make Table Query
- Previous by thread: Need advice on speeding query up
- Next by thread: Re: Need advice on speeding query up
- Index(es):
Relevant Pages
|
