pivot table from recordset - very slow performance
 

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:=xlExter nal)

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 || ||
http://code.comparative-advantage.com

pivot table from recordset - very slow performance
 

I eventually solved my own problem or at least found out what the issue
was. It was not an issue with code, but an issue of locations and data
transmission. The application was in Boston, but the SQL server was in
NY.