pivot table from recordset - slow performance

[james.igoe]

I'm recoding a spreadsheet to use SQL server, instead of local SAS
table/files, and although the data coming from the server via ADO and
stored procs is retrieved rapidly, the pivot table I build from the
recordset takes a very long time, about 3 times as long as before (now
taking approx 2 minutes), and although the recordset size is 86000 rows
by 25 columns, the data is not the issue, just how long it takes to
build the table.

Since the recordset via ADO is not the problem, I'll just display the
basic text of building the pivot table from the data:


'sets pivot cache, in prep for table, and then chart
Set objpcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objpcache.Recordset = rstSQLServer
With objpcache
.CreatePivotTable TableDestination:=DestinRange,
TableName:="level_1"
End With


The lline that is taking so long is the With/End With code. I've tried
modifying the recordset values, changing various options, but none make
a difference. Any suggestions?


James Igoe || (e-mail address removed) ||
http://code.comparative-advantage.com

 

[james.igoe]

no takers?

 

[Guest]

Hi James,
I've encountered a similar problem, and been unable to
find a way around it. Did you have any joy, or understand why it's so slow ?

 

[james.igoe]

It really turned out to be the server, since it was in NY, but the
server I was runnng remote desktop on was in Boston.

 

[Guest]

Thanks v much for your reply - I'll keep digging