Pivot Table Trauma

[douglascfast]

Happy new year all:

Can someone help me with a why and maybe how to fix this little issue.

I have a pivot table connected from SQL.
It has about 9 columns and 4 rows (yes only 4 rows)

When i refresh the pivot table, it takes over 4 hours to calculate the
fields.
I have similar Pivot tables with 10,000 rows that calc's in like 8
min.

Any ideas on what I could look at to see why this one is such a pain?

Should I just rebuild?

Doug

 

[Ron Coderre]

Even though the Pivot Table may only have a few rows,
the data source may have millions of rows
....of which, you only need a few.

Typically, when I run into this problem (on Oracle) the issue is related
to the base query joining Views, instead of Tables.
Each row in the join triggers the View to run.
10,000 rows would run the View 10,000 times.
Larger tables could create a query that could
push the duration into hours, days, or weeks.

After consulting with the DBA's, I replace the Views with an
appropriate table joined query, and the data pull duration
decreases dramatically.


Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)