How to speed up a MS SQL Pivot view
Posted on 2012-04-03
I have an Access / SQL server application which uses a MS Sql view populate an Access front-end form at runtime. Although the view executes quickly in SSMS, it takes a long time to populate the form. I'm trying to think of alternative ways to populate that form using the data displayed by the view. I'm thinking of using a table to hold the pivot view data and making that table as the data source for the unbound form. This is a multiuser environment and I need to figure out a way to load the form faster. I'm looking for advice on the best approach to take. The record set is about 20,000 records. I'd appreciate any ideas.