how to speed up query (1 Viewer)

[arage]

how to speed up query
I created a query that takes a LONG time to load 10-15mins & I was looking for some helpful tips on how to possibly make it load faster? Please advise.

 

[David R]

Like slows down queries immensely.
Indexes speed things up (unless you're using Like), but slow down data entry.
There are other things. You can post your query structure (SQL) to see what can be tweaked.

HTH,
David R

 

[Rich]

Remove calculated fields.

 

[arage]

well, here it is, hope there’s something tweakable in it.

SELECT qryProj7.promotionType, qryProj7.Month, qryProj7.RetailerNumber, Sum(NwQry.Results) AS SumOfResults
FROM NwQry, qryProj7
WHERE (((qryProj7.promotionType)=[NwQry]![PromotionType]) AND ((qryProj7.Month)=[NwQry]![tempMonth]) AND ((NwQry.tempYear)=2001))
GROUP BY qryProj7.promotionType, qryProj7.Month, qryProj7.RetailerNumber
HAVING (((qryProj7.RetailerNumber)="33333"));

This bugger takes an astounding 25min, I checked!


[This message has been edited by arage (edited 02-12-2002).]

 

[Pat Hartman]

You need to make the join between the tables explicit. You are forcing Access to make a cartesian product.

SELECT qryProj7.promotionType, qryProj7.Month, qryProj7.RetailerNumber, Sum(NwQry.Results) AS SumOfResults
FROM NwQry Inner Join qryProj7
On (((qryProj7.promotionType)=[NwQry].[PromotionType]) AND ((qryProj7.Month)=[NwQry].[tempMonth])
WHERE ((NwQry.tempYear)=2001)) AND (((qryProj7.RetailerNumber)="33333"))
GROUP BY qryProj7.promotionType, qryProj7.Month, qryProj7.RetailerNumber;

I think I fixed it up. But if the query won't work as I changed it, open the QBE grid and draw the join lines between the two tables. Also, if Access insists in turning the WHERE clause into a HAVING (it does that sometimes when you use group by), you may have to create separate queries to apply the selection criteria and then use those queries as the source for this one rather than the tables.

 

[arage]

well yes, a cartesian product is what I was going for, let me explain in my limited way.

The query I want to make is something that will list all our dealer & tell me $values they’ve spent in 2001 based on a certain payment type, by month.

Now, my query that contains the detail records doesn’t contain all dealers in it & I must list all dealers even if they didn’t have any payment activity.

So I started like this, I designed a query that contained 3 tables, paymentType, dealer, and months. I did a cartestian product between these 3 & got all the records I wanted, with groupings like I wanted.

Then I created 3 more querys, qryProj5 , qryProj6 , & qryProj7 & they are built upon one another (for example, qryProj6 links to the detail query by way of results obtained from qryProj5) & each query uses a right join to the query containing the detail records:

QryProj5 right joins to the detail query by paymentType.
QryProj6 right joins to the detail query by month.
QryProj7 right joins to the detail query by dealer.

And the final query, the one whose code you posted for, was a cartesian product of the detail records query combined with the results obtained from qryProj7.

I’m trying to use your code but I get an error message that there’s a missing operator somewhere in here:
(((qryProj7.promotionType)=[NwQry].[PromotionType]) AND ((qryProj7.Month)=[NwQry].[tempMonth])
WHERE ((NwQry.tempYear)=2001))

The problem w/your suggestion (which I did try before my original posting) is that my totals are coming up wrong, really wrong & that’s why I went the route I went. Oh that and it doesn’t affect the time limit it takes to load

Btw, here are the results of my initial code, which is completely correct for type OA but is mssing months in type BE:

Type, Month, RetailerNum, Results
BE, 6, 33333, 30
BE, 10, 33333, 4760
BE, 11, 33333, 0
BE, 12, 33333, 0
OA, 1, 33333, 0
OA, 2, 33333, 0
OA, 3, 33333, 0
OA, 4, 33333, 0
OA, 5, 33333, 3373
OA, 6, 33333, 0
OA, 7, 33333, 1228
OA, 8, 33333, 3228
OA, 9, 33333, 23
OA, 10, 33333, 4760
OA, 12, 33333, 0

[This message has been edited by arage (edited 02-13-2002).]

[This message has been edited by arage (edited 02-13-2002).]

[This message has been edited by arage (edited 02-13-2002).]

 

[Alexandre]

Now, my query that contains the detail records doesn’t contain all dealers in it & I must list all dealers even if they didn’t have any payment activity.

Normally a simple outer join should do for that

Alex

[This message has been edited by Alexandre (edited 02-13-2002).]

 

[Pat Hartman]

Try removing all the parentheses from the part of the query causing the problem. I may have messed them up with my cut and paste or just rebuild the where clause in QBE view by drawing the proper join lines.

As to the cartesian product. You don't want one! It is just a waste of resources and is contributing to the time the query is taking. Cartesian products take ALL the rows from tableA and match them against EACH row of tableB and a third table sends the numbers into the stratosphere. If tableA has 1,000 rows and tableB has 1,000 rows, the cartesian product has 1,000,000 rows! That's a pretty big recordset to be building. A proper join with a where clause would probably only result in a few hundred rows.