do I need more indexes - followup

[jmoore]

Thanks to Jeff boyce for his response to my question (do I need more
indexes?) in the database deisgn group. Individual reports created throughout
the year have been working fine. But, an aggregate annual report is very slow
to open, and edits in design view take a very long time to accomplish. The
report is based on qCrosstabs/combined. Jeff, here is the SQL you requested
for all of the queries involved. Thanks to all for your help.

qUnion
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A1a" as
Question, [A1a] as Answer
FROM [qReview Sample]
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A1b" as
Question, [A1b] as Answer
FROM [qReview Sample]
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A3" as
Question, [A3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A4" as
Question, [A4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5a" as
Question, [A5a] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5b" as
Question, [A5b] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5c" as
Question, [A5c] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A6" as
Question, [A6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A7" as
Question, [A7] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B1" as
Question, [B1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B2" as
Question, [B2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B3" as
Question, [B3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B4" as
Question, [B4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B5" as
Question, [B5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B6" as
Question, [B6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C1" as
Question, [C1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C2" as
Question, [C2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C3" as
Question, [C3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C4" as
Question, [C4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C5" as
Question, [C5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C6" as
Question, [C6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C7" as
Question, [C7] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1a" as
Question, [D1a] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1b" as
Question, [D1b] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1c" as
Question, [D1c] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D2" as
Question, [D2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D3" as
Question, [D3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D4" as
Question, [D4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D5" as
Question, [D5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E1" as
Question, [E1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E2" as
Question, [E2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E3" as
Question, [E3] as Answer
FROM [qReview Sample];
UNION ALL SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E4"
as Question, [E4] as Answer
FROM [qReview Sample];

qUnionCrosstab
TRANSFORM Count(qUnion.ReviewKey) AS CountOfReviewKey
SELECT qUnion.Question, qUnion.Answer, Count(qUnion.ReviewKey) AS
CountOfReviewKey1
FROM qUnion
GROUP BY qUnion.Question, qUnion.Answer
PIVOT qUnion.CNTYNAME In
("ALGER","ALPENA","BARAGA","BARRY","BENZIE","CASS","CLARE","CLINTON","GLADWIN","GRAND
TRAVERSE","HILLSDALE","HOUGHTON","INGHAM","IOSCO","JACKSON","KEWEENAW","LEELANAU","LENAWEE","LUCE","MACOMB","MASON","MONTCALM","MONTMORENCY","OGEMAW","OTSEGO","SAGINAW","SANILAC","SCHOOLCRAFT","SHIAWASSEE","WASHTENAW");

qReviewSampleCount
SELECT DISTINCT [qReview Sample].CNTYNAME, Count([qReview Sample].ID) AS
TotalReviews
FROM [qReview Sample]
GROUP BY [qReview Sample].CNTYNAME;

qReviewSampleCountCrosstab
TRANSFORM Sum(qReviewSampleCount.TotalReviews) AS SumOfTotalReviews
SELECT "RowHead" AS RowHead
FROM qReviewSampleCount
GROUP BY "RowHead"
PIVOT qReviewSampleCount.CNTYNAME;

qCrosstabsCombined
SELECT qUnionCrosstab.Question, qUnionCrosstab.Answer,
qUnionCrosstab.CountOfReviewKey1, qUnionCrosstab.ALGER,
qUnionCrosstab.ALPENA, qUnionCrosstab.BARAGA, qUnionCrosstab.BARRY,
qUnionCrosstab.BENZIE, qUnionCrosstab.CASS, qUnionCrosstab.CLARE,
qUnionCrosstab.CLINTON, qUnionCrosstab.GLADWIN, qUnionCrosstab.[GRAND
TRAVERSE], qUnionCrosstab.HILLSDALE, qUnionCrosstab.HOUGHTON,
qUnionCrosstab.INGHAM, qUnionCrosstab.IOSCO, qUnionCrosstab.JACKSON,
qUnionCrosstab.KEWEENAW, qUnionCrosstab.LEELANAU, qUnionCrosstab.LENAWEE,
qUnionCrosstab.LUCE, qUnionCrosstab.MACOMB, qUnionCrosstab.MASON,
qUnionCrosstab.MONTCALM, qUnionCrosstab.MONTMORENCY, qUnionCrosstab.OGEMAW,
qUnionCrosstab.OTSEGO, qUnionCrosstab.SAGINAW, qUnionCrosstab.SANILAC,
qUnionCrosstab.SCHOOLCRAFT, qUnionCrosstab.SHIAWASSEE,
qUnionCrosstab.WASHTENAW, qReviewSampleCountCrosstab.ALGER,
qReviewSampleCountCrosstab.ALPENA, qReviewSampleCountCrosstab.BARAGA,
qReviewSampleCountCrosstab.BARRY, qReviewSampleCountCrosstab.BENZIE,
qReviewSampleCountCrosstab.CASS, qReviewSampleCountCrosstab.CLARE,
qReviewSampleCountCrosstab.CLINTON, qReviewSampleCountCrosstab.GLADWIN,
qReviewSampleCountCrosstab.[GRAND TRAVERSE],
qReviewSampleCountCrosstab.HILLSDALE, qReviewSampleCountCrosstab.HOUGHTON,
qReviewSampleCountCrosstab.INGHAM, qReviewSampleCountCrosstab.IOSCO,
qReviewSampleCountCrosstab.JACKSON, qReviewSampleCountCrosstab.KEWEENAW,
qReviewSampleCountCrosstab.LEELANAU, qReviewSampleCountCrosstab.LENAWEE,
qReviewSampleCountCrosstab.LUCE, qReviewSampleCountCrosstab.MACOMB,
qReviewSampleCountCrosstab.MASON, qReviewSampleCountCrosstab.MONTCALM,
qReviewSampleCountCrosstab.MONTMORENCY, qReviewSampleCountCrosstab.OGEMAW,
qReviewSampleCountCrosstab.OTSEGO, qReviewSampleCountCrosstab.SAGINAW,
qReviewSampleCountCrosstab.SANILAC, qReviewSampleCountCrosstab.SCHOOLCRAFT,
qReviewSampleCountCrosstab.SHIAWASSEE, qReviewSampleCountCrosstab.WASHTENAW
FROM qReviewSampleCountCrosstab, qUnionCrosstab;

 

[John Spencer MVP]

More indexes won't help because once you use create the union query the
subsequent queries will work with unindexed data from the union query.

And you aren't using any indexes in the qUnion since you are not filtering by
or ordering by any fields in the individual SELECT statements.

There may be ways to speed up the queries, but I think the best method would
be to restructure your data - something that is implied by your use of the
union query to normalize the data you have.

Another option if the data is static (or at least fairly static) is to use
qUnion as the source to populate a table (possibly a temporary work table)
with the fields: CntyName, ReviewKey, Question, Answer. You could apply
indexes to all the fields in this table and see what the performance would be
like. You might find a marked increase in performance.

I use a temporary table for a several very complex reports that use the same
set of data. The queries for the reports used to time out frequently. Now I
build the table, populate it (takes about 60 seconds), and then the users run
multiple reports against the data. The reports all run in just a few seconds.
The next time they want to run this group of reports, I check with them
whether or not they need to update the data in the work table.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County