Hello all,
I have two tables:
tblSales with the fields
ID,
GroupID,
SalesTypeID,
CompanyID,
Amount
And tblCompany
with the fields
ID, TypeID
The underlined fields are PKs, the bold ones have indexes.
Based on these two tables, I created the query qrySalesOfCompanyType2, which returns the sales totals for all companies with company type = 2.
The query works as expected.
The problem or question is about the query plan or performance:
If I run the query and see the plan of JETShowPlan, it looks like this:
Questions:
1. Why does Access sort in step 02?
2. And why does it use a temporary index for the JOIN instead of the existing index or Rushmore? I suspect the second part of the question relates to the sorting.
3. Does anyone have any idea why this is happening and how we could optimize the query?
Thanks for any help
I have two tables:
tblSales with the fields
ID,
GroupID,
SalesTypeID,
CompanyID,
Amount
And tblCompany
with the fields
ID, TypeID
The underlined fields are PKs, the bold ones have indexes.
Based on these two tables, I created the query qrySalesOfCompanyType2, which returns the sales totals for all companies with company type = 2.
SQL:
SELECT
S.CompanyID,
SUM(S.Amount) AS Amount
FROM
tblSales AS S
INNER JOIN tblCompany AS C ON S.CompanyID = C.ID
WHERE
C.TypeID = 2
GROUP BY
S.CompanyID;
The query works as expected.
The problem or question is about the query plan or performance:
If I run the query and see the plan of JETShowPlan, it looks like this:
Questions:
1. Why does Access sort in step 02?
2. And why does it use a temporary index for the JOIN instead of the existing index or Rushmore? I suspect the second part of the question relates to the sorting.
3. Does anyone have any idea why this is happening and how we could optimize the query?
Thanks for any help

Last edited: