• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 12. Working with PivotTable and ... > Optimizing Performance of PivotTable...

Optimizing Performance of PivotTables

The first and most important rule of PivotTables is Minimize the number of rows returned by queries that you intend to use as the data source for PivotTable views. Jet returns only the first 100 rows of the query result set to the initial Datasheet view, so response is almost instantaneous for a default Dynaset-type query without GROUP BY or other operations that require operations on the entire result set. The Jet database engine retrieves additional rows as you scroll the datasheet. Unfortunately, PivotTables don’t take advantage of Jet’s incremental row retrieval feature.

The Oakmont.mdb sample database has a sufficient number of records to bring a PivotTable view to its knees with a simple query. For example, you might want to analyze tuition revenue by student graduation year and course. The average Oakmont student is enrolled in only two courses, so the query returns 59,996 rows. The objective of the query is to return total revenue and an enrollment count for all sections of each of the 590 courses offered by the college, and to summarize the data by academic department. In theory, the PivotTable’s AutoCalc feature should be able to total the revenue and count the number of enrollment records. Figure 12.23 shows the initial design of a query that’s capable of providing the required data.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint