Pivot Table Performance

[Chris Jones]

I'm using OWC10 Pivot Table control to access data in a
relational database table in SQL Server. The table
contains about 50,000 rows consisting of 5 columns; 3 of
the columns contain text, 1 column contains a date, and 1
column contains a double.

The performance of the Pivot table is really slow. When I
drag/drop a field, it can take a minute or more for the
recalculation to complete. If I do essentially the same
operation directly in excel, the pivotting occurs almost
instantly.

What am I doing wrong?

Cheers,
Chris Jones

 

[Dan Ricker]

You are probably not doing anything wrong.

Excel and OWC handle relational data differently.

Excel retrieves the data and does the calculations in
memory.

OWC uses the MSOLAP Client components to create a
temporary cub file that does all the calculations. This
adds disk IO to the process.

In general, 50000 rows of data being process to generate a
CUB file is not a short process. As more data is added to
rows and columns the process gets longer.