slow query

A

Alex

I have a table with products and territories. The same product can be for
different territories. But, a combination of Product_ID and Territory is
unique in the table.

To don’t transfer a product for a territory where quantities for all months
are equal 0, I had a query as follows:

INSERT INTO tblTable1 ( Product_ID, Description, TerritoryID, Terr_Num,
Territory, ProdCateg_Code, ProdCateg, AdjY1F, Aug, FAug, Sep, FSep, Oct,
FOct, Nov, FNov, [Dec], FDec, Jan, FJan, Feb, FFeb, Mar, FMar, Apr, FApr,
May, FMay, Jun, FJun, Jul, FJul, AugNew, SepNew )
SELECT qryQueryFrom.Product_ID, qryQueryFrom.Description,
qryQueryFrom.TerritoryID, qryQueryFrom.Terr_Num, qryQueryFrom.Territory,
qryQueryFrom.ProdCateg_Code, qryQueryFrom.ProdCateg, qryQueryFrom.AdjY1F,
qryQueryFrom.Aug, qryQueryFrom.FAug, qryQueryFrom.Sep, qryQueryFrom.FSep,
qryQueryFrom.Oct, qryQueryFrom.FOct, qryQueryFrom.Nov, qryQueryFrom.FNov,
qryQueryFrom.Dec, qryQueryFrom.FDec, qryQueryFrom.Jan, qryQueryFrom.FJan,
qryQueryFrom.Feb, qryQueryFrom.FFeb, qryQueryFrom.Mar, qryQueryFrom.FMar,
qryQueryFrom.Apr, qryQueryFrom.FApr, qryQueryFrom.May, qryQueryFrom.FMay,
qryQueryFrom.Jun, qryQueryFrom.FJun, qryQueryFrom.Jul, qryQueryFrom.FJul,
qryQueryFrom.AugNew, qryQueryFrom.SepNew
FROM qryQueryFrom
WHERE (((qryQueryFrom.AdjY1F)>0)) OR (((qryQueryFrom.Aug)<>0)) OR
(((qryQueryFrom.Sep)<>0)) OR (((qryQueryFrom.Oct)<>0)) OR
(((qryQueryFrom.Nov)<>0)) OR (((qryQueryFrom.Dec)<>0)) OR
(((qryQueryFrom.Jan)<>0)) OR (((qryQueryFrom.Feb)<>0)) OR
(((qryQueryFrom.Mar)<>0)) OR (((qryQueryFrom.Apr)<>0)) OR
(((qryQueryFrom.May)<>0)) OR (((qryQueryFrom.Jun)<>0)) OR
(((qryQueryFrom.Jul)<>0)) OR (((qryQueryFrom.AugNew)<>0)) OR
(((qryQueryFrom.SepNew)<>0));

It was working well enough.
Then, I’ve changed it into a query to transfer the product for all
territories if at least for some territory the quantity is not equal 0 (to
allow a user to enter some qty later) as follows:

INSERT INTO tblTable1 ( Product_ID, Description, TerritoryID, Terr_Num,
Territory, ProdCateg_Code, ProdCateg, AdjY1F, Aug, FAug, Sep, FSep, Oct,
FOct, Nov, FNov, [Dec], FDec, Jan, FJan, Feb, FFeb, Mar, FMar, Apr, FApr,
May, FMay, Jun, FJun, Jul, FJul, AugNew, SepNew )
SELECT qryQueryFrom.Product_ID, qryQueryFrom.Description,
qryQueryFrom.TerritoryID, qryQueryFrom.Terr_Num, qryQueryFrom.Territory,
qryQueryFrom.ProdCateg_Code, qryQueryFrom.ProdCateg, qryQueryFrom.AdjY1F,
qryQueryFrom.Aug, qryQueryFrom.FAug, qryQueryFrom.Sep, qryQueryFrom.FSep,
qryQueryFrom.Oct, qryQueryFrom.FOct, qryQueryFrom.Nov, qryQueryFrom.FNov,
qryQueryFrom.Dec, qryQueryFrom.FDec, qryQueryFrom.Jan, qryQueryFrom.FJan,
qryQueryFrom.Feb, qryQueryFrom.FFeb, qryQueryFrom.Mar, qryQueryFrom.FMar,
qryQueryFrom.Apr, qryQueryFrom.FApr, qryQueryFrom.May, qryQueryFrom.FMay,
qryQueryFrom.Jun, qryQueryFrom.FJun, qryQueryFrom.Jul, qryQueryFrom.FJul,
qryQueryFrom.AugNew, qryQueryFrom.SepNew
FROM qryQueryFrom
WHERE (((qryQueryFrom.Product_ID) In (SELECT qryQueryFrom.Product_ID FROM
qryQueryFrom GROUP BY qryQueryFrom.Product_ID HAVING
Sum(qryQueryFrom.AdjY1F)>0 OR Sum(qryQueryFrom.Aug)<>0 OR
Sum(qryQueryFrom.Sep)<>0 OR Sum(qryQueryFrom.Oct)<>0 OR
Sum(qryQueryFrom.Nov)<>0 OR Sum(qryQueryFrom.Dec)<>0 OR
Sum(qryQueryFrom.Jan)<>0 OR Sum(qryQueryFrom.Feb)<>0 OR
Sum(qryQueryFrom.Mar)<>0 OR Sum(qryQueryFrom.Apr)<>0 OR
Sum(qryQueryFrom.May)<>0 OR Sum(qryQueryFrom.Jun)<>0 OR
Sum(qryQueryFrom.Jul)<>0 OR Sum(qryQueryFrom.AugNew)<>0 OR
Sum(qryQueryFrom.SepNew)<>0)));

But, the query is extremely slow. Could anybody advise how I could make it
better.

Thanks
 
C

Chris2

Alex said:
I have a table with products and territories. The same product can be for
different territories. But, a combination of Product_ID and Territory is
unique in the table.

To don’t transfer a product for a territory where quantities for all months
are equal 0, I had a query as follows:

INSERT INTO tblTable1 ( Product_ID, Description, TerritoryID, Terr_Num,
Territory, ProdCateg_Code, ProdCateg, AdjY1F, Aug, FAug, Sep, FSep, Oct,
FOct, Nov, FNov, [Dec], FDec, Jan, FJan, Feb, FFeb, Mar, FMar, Apr, FApr,
May, FMay, Jun, FJun, Jul, FJul, AugNew, SepNew )
SELECT qryQueryFrom.Product_ID, qryQueryFrom.Description,
qryQueryFrom.TerritoryID, qryQueryFrom.Terr_Num, qryQueryFrom.Territory,
qryQueryFrom.ProdCateg_Code, qryQueryFrom.ProdCateg, qryQueryFrom.AdjY1F,
qryQueryFrom.Aug, qryQueryFrom.FAug, qryQueryFrom.Sep, qryQueryFrom.FSep,
qryQueryFrom.Oct, qryQueryFrom.FOct, qryQueryFrom.Nov, qryQueryFrom.FNov,
qryQueryFrom.Dec, qryQueryFrom.FDec, qryQueryFrom.Jan, qryQueryFrom.FJan,
qryQueryFrom.Feb, qryQueryFrom.FFeb, qryQueryFrom.Mar, qryQueryFrom.FMar,
qryQueryFrom.Apr, qryQueryFrom.FApr, qryQueryFrom.May, qryQueryFrom.FMay,
qryQueryFrom.Jun, qryQueryFrom.FJun, qryQueryFrom.Jul, qryQueryFrom.FJul,
qryQueryFrom.AugNew, qryQueryFrom.SepNew
FROM qryQueryFrom
WHERE (((qryQueryFrom.AdjY1F)>0)) OR (((qryQueryFrom.Aug)<>0)) OR
(((qryQueryFrom.Sep)<>0)) OR (((qryQueryFrom.Oct)<>0)) OR
(((qryQueryFrom.Nov)<>0)) OR (((qryQueryFrom.Dec)<>0)) OR
(((qryQueryFrom.Jan)<>0)) OR (((qryQueryFrom.Feb)<>0)) OR
(((qryQueryFrom.Mar)<>0)) OR (((qryQueryFrom.Apr)<>0)) OR
(((qryQueryFrom.May)<>0)) OR (((qryQueryFrom.Jun)<>0)) OR
(((qryQueryFrom.Jul)<>0)) OR (((qryQueryFrom.AugNew)<>0)) OR
(((qryQueryFrom.SepNew)<>0));

It was working well enough.
Then, I’ve changed it into a query to transfer the product for all
territories if at least for some territory the quantity is not equal 0 (to
allow a user to enter some qty later) as follows:

INSERT INTO tblTable1 ( Product_ID, Description, TerritoryID, Terr_Num,
Territory, ProdCateg_Code, ProdCateg, AdjY1F, Aug, FAug, Sep, FSep, Oct,
FOct, Nov, FNov, [Dec], FDec, Jan, FJan, Feb, FFeb, Mar, FMar, Apr, FApr,
May, FMay, Jun, FJun, Jul, FJul, AugNew, SepNew )
SELECT qryQueryFrom.Product_ID, qryQueryFrom.Description,
qryQueryFrom.TerritoryID, qryQueryFrom.Terr_Num, qryQueryFrom.Territory,
qryQueryFrom.ProdCateg_Code, qryQueryFrom.ProdCateg, qryQueryFrom.AdjY1F,
qryQueryFrom.Aug, qryQueryFrom.FAug, qryQueryFrom.Sep, qryQueryFrom.FSep,
qryQueryFrom.Oct, qryQueryFrom.FOct, qryQueryFrom.Nov, qryQueryFrom.FNov,
qryQueryFrom.Dec, qryQueryFrom.FDec, qryQueryFrom.Jan, qryQueryFrom.FJan,
qryQueryFrom.Feb, qryQueryFrom.FFeb, qryQueryFrom.Mar, qryQueryFrom.FMar,
qryQueryFrom.Apr, qryQueryFrom.FApr, qryQueryFrom.May, qryQueryFrom.FMay,
qryQueryFrom.Jun, qryQueryFrom.FJun, qryQueryFrom.Jul, qryQueryFrom.FJul,
qryQueryFrom.AugNew, qryQueryFrom.SepNew
FROM qryQueryFrom
WHERE (((qryQueryFrom.Product_ID) In (SELECT qryQueryFrom.Product_ID FROM
qryQueryFrom GROUP BY qryQueryFrom.Product_ID HAVING
Sum(qryQueryFrom.AdjY1F)>0 OR Sum(qryQueryFrom.Aug)<>0 OR
Sum(qryQueryFrom.Sep)<>0 OR Sum(qryQueryFrom.Oct)<>0 OR
Sum(qryQueryFrom.Nov)<>0 OR Sum(qryQueryFrom.Dec)<>0 OR
Sum(qryQueryFrom.Jan)<>0 OR Sum(qryQueryFrom.Feb)<>0 OR
Sum(qryQueryFrom.Mar)<>0 OR Sum(qryQueryFrom.Apr)<>0 OR
Sum(qryQueryFrom.May)<>0 OR Sum(qryQueryFrom.Jun)<>0 OR
Sum(qryQueryFrom.Jul)<>0 OR Sum(qryQueryFrom.AugNew)<>0 OR
Sum(qryQueryFrom.SepNew)<>0)));

But, the query is extremely slow. Could anybody advise how I could make it
better.

Thanks
Click to expand...


Alex,

Two things:

1:

There are a *titantic* number of aggregate operations underway here.
This is because of #2, below.

I believe, but may be wrong, that JET cannot optimaize the <>
operator, causing the whole thing to become non-optimized (I can't
find the right reference to back that up at the moment).

2:

MS Access, being a Relational-based Database, works best upon tables
that have been designed along Relational standards, like First,
Second, and Third Normal Forms, at a minimum. The structure of the
Table revealed from the above Query does not even meet First Normal
Form, in that it has repeating column types (Jan, Feb, etc.; which are
not allowed).

My recommendation is: Normalize the Database.

Database Normalization Basics:
http://databases.about.com/od/specificproducts/a/normalization.htm

Normalization (Advanced Terminology):
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html


Also:

Information about query performance in an Access database (2000, 2002,
2003): http://support.microsoft.com/kb/209126


ACC: How to Optimize Queries in Microsoft Access 2.0, Microsoft Access
95, and Microsoft Access 97: (http://support.microsoft.com/kb/112112)
Note: There is much here that still applies to today (even though JET
has now gone from 3.5 to 4.0).
 
Top