Re: Need advice on speeding query up
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Mon, 19 Jun 2006 15:17:44 -0400
Change your first query so it can use the index on the date field
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 Purchase_Dte
=DateSerial(Forms!frmReportGeneration!txtStartYear,Forms!frmReportGeneration!txtStartMonth,1)and
Purchase_Dte
=DateSerial(Forms!frmReportGeneration!txtStartYear,Forms!frmReportGeneration!txtStartMonth+1,0)and tblPurchasesSince_200511.status_id<>3
GROUP BY tblCustomer.[Full Name];
Query 2. Not In is slow. You might be able to improve your performance
with the following.
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 as Q1 INNER JOIN
(SELECT TOP 25 Q2.*
FROM qryCustomerAggregate as Q2
ORDER BY Q2.Total_Cost DESC) as Q3
ON Q1.Name = Q3.Name
WHERE Q3.Name is Null
"Jon" <jcrowhurst@xxxxxxxxx> wrote in message
news:1150739581.687263.322450@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?
.
- References:
- Need advice on speeding query up
- From: Jon
- Need advice on speeding query up
- Prev by Date: Re: 4+0 = 2 in Union query. Data lost.
- Next by Date: Re: Changing hyperlinks for 1000 records
- Previous by thread: Re: Need advice on speeding query up
- Next by thread: Removing a duplicate
- Index(es):
Relevant Pages
|
