Calc Registered Datasource (MySQL) for Pivot

[alesito85]

Hi, I’m trying to use a registered datasource to create a pivot table. I created a database connection in Base through ODBC, where I can view and edit data with no problem. However when selecting the data via either the Sheet or Query option, it takes an enormous amount of time and basically doesn’t finish.

The database contains about ~100.000 rows with 27 columns. I tested with a query that limits the selection to 100 rows and that Calc does process. Also while monitoring network activity, I can see that the data from database is transfered quickly (about 20 MB) and then the CPU utilization of Calc jumps up, doing I don’t know what. Even if I leave it for tens of minutes it does not finish.

On a side note, Microsoft Excel does process the data and creates a Pivot table in a few seconds. Any recommendations?

[iplaw67]

The answer lies in the fact that Calc loads virtually all of the data into internal memory. As you might appreciate, 100K rows * 27 columns is potentially a huge data array, one that the data structure created by the pivot table code can not process (or only very slowly).