Access Pivot Tables: Are indexes of any use?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've recently gotten into Access pivot tables, and am finding them very useful.

I use a MakeTable query to create a new table containing the info I want to
use in the pivot table, and work from it.

I have no idea what MS does internally in doing the pivot table analysis,
but from what I've seen it doesn't sem to matter in terms of performance
whether a field is indexed or not. Can anyone shed light on this? Is there
any benefit in indexing any of the fields in the table on which the pivot
table is based? Or does the whole thing just get sucked into memory somehow,
making indexing superfluous?

Thanks,

Kevin (Hoping to get some benefit from the guaranteed 48-hour response time
my MSDN subscription is supposed to give me, but not having seen it yet.)
 
Kevin Witty said:
I've recently gotten into Access pivot tables, and am finding them very useful.

I use a MakeTable query to create a new table containing the info I want to
use in the pivot table, and work from it.

I have no idea what MS does internally in doing the pivot table analysis,
but from what I've seen it doesn't sem to matter in terms of performance
whether a field is indexed or not. Can anyone shed light on this? Is there
any benefit in indexing any of the fields in the table on which the pivot
table is based? Or does the whole thing just get sucked into memory somehow,
making indexing superfluous?
Click to expand...

Kevin Witty,

Queries are handled in many stages, and indexing might, or might not,
help in those various stages. It is strictly on a per-query basis,
and is impossible to say for certain without seeing the paticular
query.

Major benefits of indexing occur on the FROM clause (during the
JOINing of the Tables) and WHERE clause (when the operators allow for
the use of indexes).

GROUP BY and TRANSFORM...PIVOT, hmmm, I've thought about it for a bit,
but I find I'm not sure.


The use of indexes is determined by a process called Query
Optimization (most DB Engines have component called the "Query
Optimizer", or something similar). This is the component that figures
out the "best way" to retrived the data requested by the SQL
statement. It's a pretty complicated process of trade offs and
estimates.

One thing that usually stops a Query Optimizer in its tracks:
Functions. Many types of function, RIGHT, MID, IIF, etc., will stop
the use of Query Optimization (including indexing) on any column
contained in the function. Note that aggregate functions, SUM, COUNT,
AVG, etc., can still usually be optimized to some degree, but they're
specifically a part of SQL, and they work to "summarize" data, rather
than "transform" it into something else (which is what RIGHT, MID,
IIF, all do).


See:

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


Thanks,

Kevin (Hoping to get some benefit from the guaranteed 48-hour response time
my MSDN subscription is supposed to give me, but not having seen it
Click to expand...
yet.)

I hope you're not thinking that support is provided *here*?


Sincerely,

Chris O.
 
Back
Top