how to speed up query

[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).]

 

[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).]