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.

Figure 12.23. This sample query against four tables of the Oakmont.mdb database returns 59,996 rows.

graphics/12fig23.jpg

Note

graphics/power_tools.gif

The three qryOakmontPTTest sample queries (60,000, 2,350, and 52 Rows) discussed in this section are included in the PivotOM.mdb database in the \Seua11\Chaptr12 folder of the accompanying CD-ROM. You must install the Oakmont.mdb database from the CD-ROM to its default location, C:\Program Files\Seua11\Oakmont, for these queries to execute from the default linked tables. If you've installed Oakmont.mdb to another location, choose Tools, Database Utilities, Linked Table Manager, and change the links to the correct path before attempting to open a table or query.


To review how to use the Linked Table Manager, see "Using the Linked Table Manager Add-in to Relink Tables," p. 282.


Opening a local copy of the 60,000-row query design of Figure 12.23 in an empty PivotTable view takes 15 seconds or more on a fast computer (a 667MHz Pentium III system with 512MB RAM and an Ultra-DMA66 drive running Windows XP Professional). This delay occurs every time you move from Query Datasheet or Design view to PivotTable view, because all rows of the query must be loaded into the PivotTable to compute totals. An equal delay occurs between PivotTable and PivotChart views. If the Oakmont.mdb file is on a network server, opening the PivotTable view can take at least a minute and consume a large part (or all) of the network's available bandwidth. Clicking Show Details requires about about a minute to regenerate the PivotTable view.

graphics/troubleshooting.gif

If you're using large Jet tables shared from a network server and experience performance problems with PivotTables, see the "PivotTable Performance Problems with Networked Tables" topic of the "Troubleshooting" section near the end of this chapter.


graphics/country.gif If the opening delay doesn't sufficiently deter you, dropping fields of this query to rows or columns can consume 100% of your CPU cycles and take even longer. If you don't select Hide Details beforehand, dropping the Year field on the Column Fields zone takes a minute or two for the four year (2001 through 2004) columns to appear. Clicking Hide Details and dropping the Year field takes only a few seconds to display the columns. With details hidden, dropping Department on the Row Fields zone, and Revenue and Students on the Totals and Detail Fields zone takes only a few seconds to regenerate totals (see Figure 12.24).

Figure 12.24. This PivotTable performs grouping and aggregation operations on a 60,000-row query result set. Performing these operations in the PivotTable can take a long time.

graphics/12fig24.jpg

graphics/auto_sum.gif The solution to the preceding performance hit is to use Jet's Totals (Group By) feature to reduce the number of query rows. Grouping by year, course, and department; counting Enrollments records; and summing the Cost field of the Courses table lets the Jet query engine instead of the PivotTable handle the initial aggregation (see Figure 12.25).

Figure 12.25. Adding a GROUP BY expression to the query and summing the enrollment count and tuition revenue in the Jet query reduces the number of query rows from 59,996 to 2,348.

graphics/12fig25.jpg

The PivotTable identical to that shown earlier in Figure 12.24 better digests the result set of the aggregate query, which now contains 2,348 rows. Jet takes only about five seconds to execute this query from a local database, and the same PivotTable view opens in a bit more than five seconds. The detail data is consolidated by the query, so the Show Detail/Hide Detail toggle operation is almost instantaneous. Reducing the number of rows of the local query result set, however, doesn't reduce network traffic if you're connected to a remote database.

If you don't need detail enrollment and revenue data for each course, you can speed PivotTable operations by removing the Course: Name field from the query design of Figure 12.25 to return only 52 rows. Execution of the Jet query with 52 rows takes about the same time as for 2,348 rows, but PivotTable operations are almost instantaneous.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net