ACC2002: How to Optimize PivotTable List Performance in Microsoft Access

To optimize PivotTable List performance in Microsoft Access, use the following tips, which are explained in detail in the "More Information" section:

• Hide details in the PivotTable list.
• Use a query to restrict unnecessary fields and records.
• Calculate detail fields in a query.
• Limit use of granular date groupings.

Hide Details in the PivotTable List

Even though PivotTable lists can display detail data, typically you should use PivotTable lists to display summary data and totals. The PivotTable component opens a separate ActiveX Data Objects (ADO) recordset for each cell it contains. When you use a PivotTable List to display details, there are typically many more cells displayed than if the details are hidden. Therefore, the PivotTable List must open and manage additional ADO recordsets, which contributes to slower performance.

If you need to view detail data, design the PivotTable list so that details are hidden. Then you can drill down into the details for a specific row or column where the summary data is interesting. If you need to view details for all rows and columns, you should consider using another object to display the data, such as a query or a form.

Unfortunately, the user interface of a PivotTable list usually requires that you view detail data before you add totals. The usual order of steps you would have to go through is:

1. Open the table or query in PivotTable view.
2. Drag a field from the PivotTable field list to the Drop Totals or Detail Fields Here area.
3. Select the column that you just added in the PivotTable list.
4. On the PivotTable menu, point to AutoCalc, and then select the aggregate function that you want.
5. On the PivotTable menu, click Hide Details.

However, depending on the field's data type, it is possible to add Sum and Count totals to the data axis without first having to display detail data. If you want to use the Sum aggregate function to total a numeric or currency field, you can create the total by following these steps.

NOTE: These steps assume that the PivotTable view is currently blank and has not been previously customized.

1. Open a table, a query, or a form in PivotTable view.
2. In the PivotTable field list, select a numeric or currency field that you want to total by using the Sum aggregate function.
3. Select Data Area in the drop-down box at the bottom of the PivotTable field list.
4. Click Add.

Note that the sum of the field that you selected was added to the data axis of the PivotTable list.

You can use these same steps to create a total that uses the Count aggregate function by selecting a non-numeric field in step 2. For example, if you select a text field, and then add it to the data axis by clicking Add, the PivotTable list will automatically create a total using the Count aggregate function.

Use a Query to Restrict Unnecessary Fields and Records

Try to reduce the number of fields and records used by the PivotTable component. You can do this by creating a query, a view, a stored procedure, or an inline function. Then, only select the fields necessary for the PivotTable list in the query's output, and use criteria, where possible, to reduce the number of records in the PivotTable list.

This is important because Microsoft Access must retrieve the recordset from the underlying table or query, and then pass the recordset to the PivotTable component. The more data you have in this recordset the slower this operation is. This operation is faster if you eliminate unnecessary fields and records by using a query, and then opening the query in PivotTable view.

This is especially important in a Microsoft Access project (.adp) file. When opening a SQL Server table in PivotTable view, Access must first retrieve all records for the table from the server and then download them to the client computer. Then, Microsoft Access passes the recordset to the PivotTable component. If you use criteria in a view, a stored procedure, or an inline function, the server only returns the rows requested and eliminates unnecessary network traffic.

Calculate Detail Fields in a Query

Even though you can create calculated detail fields in a PivotTable list, it is usually faster to calculate them in a query, a view, a stored procedure, or an inline function. By using this approach, the database engine performs the calculation and includes the calculated field as part of the recordset it passes to the PivotTable component. Because the database engine has already calculated the value, you do not have to create a calculated field in the PivotTable component.

Limit Use of Granular Date Groupings

Try to avoid the use of very granular date groupings in the PivotTable list. For example, try to avoid using hours, minutes, and seconds. If you need to group on a particular portion of a date, create an expression in a query that extracts the portion that you need. For example, you can use the DatePart function in a query or a view to extract the portion of the date that you need. After you have extracted the date portions that you need, you can add these as fields to a particular axis. This allows you to group by the date portion without having the PivotTable component calculate the date portions.

For additional information about optimizing performance when you print PivotTables, click the article number below to view the article in the Microsoft Knowledge Base: