Re: Need advice on speeding query up

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Any field your queries use in ORDER BY, GROUP BY or WHERE clauses, and any
field on which your queries sort provides a potential slow down. Do you
have indexes on all of those?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"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?



.

Relevant Pages

  • Re: Weird speed problem
    ... to design a system that queries this table to produce fast counts. ... I would assume that the first query should run faster then the second ... When I look at the query plans, they are almost identical cost ...
    (comp.databases.ms-sqlserver)
  • Re: Simply confused - Query stopped working
    ... Here is my sql for the 2 queries (and yes I know there are ... Is Not Null) part of the query. ... Another approach would be to (first, make a backup!) use Compact & Repair to ... first Query and since I can use it in a iifexpression? ...
    (microsoft.public.access.queries)
  • Weird speed problem
    ... to design a system that queries this table to produce fast counts. ... I would assume that the first query should run faster then the second ... When I look at the query plans, they are almost identical cost ...
    (comp.databases.ms-sqlserver)
  • Re: Comparing weeks with highest and lowest sales Query--HELP
    ... I think you will have to use nested queries to get the result you want. ... SELECT TOP 3 DatePart("ww", saledate) As WeekNum ... Second query saved as QueryTwo: ... Only those fields requested in the first query can be included in an ORDER BY expression. ...
    (microsoft.public.access.queries)
  • RE: Need advice on speeding query up
    ... Month functions actually make an index on that column worthless plus have to ... 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)