Pivot form - 200, 000 rec's - faster?

[neemi]

I have a pivot form which the query based on it pulls back about 200,000 records. This has been speeded up to take only 20 to 30 seconds as I have created a stored procedure on the sql server to give me the results. However running the pivot form over this takes about 5 - 6 minutes? if not longer!!

Is there a way to speed up a pivot form!!

I don't normally work with them as I can't stand them but have inherited a db and given the task to speed this up.

Please help.
Cheers,

Neemi

 

[DreewToo]

We've started using SQL analysis services to manage a result set of over 1.5 million records, and it works very fast. The trick for us was putting the data in a cube, and basing the pivot table on this data.

We've been using MS Excel, and I'm not sure if an Access pivot form can have a datasource from a cube, but it's one possibility.

Another consideration is the frequency the data is updated. If it's rather static data, or not critical to have up to the minute, you might consider creating a table from the pivot's data source (if it's currently a query), and using indexes to speed up performance.