Douglas Cummings
asked on
Slow MS Access Query
How can I optimize this query. As written, it takes 10's of minutes to execute.
SELECT C.CustomerId AS [Customer Id], IIf(Len([Surname])>1,IIf(L
FROM ((dbo_tblReturns AS R INNER JOIN dbo_tblCustomer AS C ON R.CustomerId = C.CustomerId) INNER JOIN dbo_tblSalespeople ON R.SalesId = dbo_tblSalespeople.SalesId
WHERE (((R.MailingsId)=[Forms]![
ORDER BY R.MailingsId, R.DateCompleted;
It is the following that slows this query:
(Select Max(DateCompleted) From dbo_tblReturns Where dbo_tblReturns.CustomerId = C.CustomerId and dbo_tblReturns.DateComplet
Without it, it executes in a fraction of a second.
SELECT C.CustomerId AS [Customer Id], IIf(Len([Surname])>1,IIf(L
FROM ((dbo_tblReturns AS R INNER JOIN dbo_tblCustomer AS C ON R.CustomerId = C.CustomerId) INNER JOIN dbo_tblSalespeople ON R.SalesId = dbo_tblSalespeople.SalesId
WHERE (((R.MailingsId)=[Forms]
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Other tips:
1. Limit the fields to only the fields absolutely needed.
2. You might also get a little bump in speed by checking for:
Not IsNull(YourField)
...instead of LEN(YourField)>1
1. Limit the fields to only the fields absolutely needed.
2. You might also get a little bump in speed by checking for:
Not IsNull(YourField)
...instead of LEN(YourField)>1
ASKER
At Pat's suggestion, I created a separate query for the sub select and using Jeff's suggestion, I limited the number of customers to only those that responded to the mailing that was the basis for the underlying query. DateCompleted is indexed. The problem, as Jeff suggested, was 649,000 customer records and 1.6 million order records.
Doug