Troubleshooting

PivotTable Performance Problems with Networked Tables

When I link large back-end tables on a network server as the data source for PivotTables, I experience serious deterioration in performance.

Opening PivotTables based on aggregate queries against remote Jet tables requires moving all affected rows of the underlying tables across the network, because the query that performs the aggregation runs on your local computer. In a lightly loaded 100BaseT network, the added opening time for a PivotTable isn't a serious issue. If your network uses 10BaseT media or you're connected to a hub (not a switch), and the network has a substantial amount of traffic, aggregate queries against large tables can slow dramatically. For historical (static) data, consider converting the aggregate query to a make-table query, and changing the data source to the table. Using a table with fewer rows also helps solve performance problems that result from overtaxed server disk drives.

If you upsize your application to SQL Server, queries run as views, functions, or stored procedures on the server, not the client PC. One of the primary advantages of client/server RDBMSs, such as SQL Server, is that only query result sets move across the network.