Windows 7 Key Access 2010 Performance improvement

Today’s guest writer is DJ Cole from the Access development team. DJ is the architect and primary developer of SharePoint connectivity performance improvements.
The Access Show Overview




Our goal for Access 2010 was to make connections to SharePoint lists nearly as fast as local tables. We had to ensure requests didn’t swamp the server, bottleneck over the network, or cause the client machine’s CPU or RAM to thrash. We found caching data in local tables provided the best user experience while being conservative with resources on the server, network and local machine. Access 2010 performance improvement builds upon the Access 2007 architecture. In order to fully understand the implications to your apps,Office Home And Business 2010 Product Key, it helps to understand how Access 2007 works against SharePoint.
Improvements in Access 2007
As described in a previous blog post, the team has worked to improve SharePoint performance for some time. Back in Access 2007, there were three major changes:
Asynchronous fetches of data from the server to allow some scenarios to immediately show data before the complete recordset is returned from the server. Fetch incremental data changes to a list within an Access session. Once the app fetches data subsequent requests,Windows Seven, only query what has changed in the list. Cache lists in the local database as XML. Asynchronous fetching
When data is loaded into a datasheet or form, often just the first page of data or a single record is needed to begin working or scrolling through records. To speed up these scenarios, control is returned to Access as soon as the first batch of data has been retrieved from the server. A background thread continues parsing and fetching the remaining data from the server, blocking only when the requested record is not yet available (for example, if you press Move Last immediately after open). While this works for some scenarios, queries with joins, sorts and filters require all of the data upfront.
Fetch incremental data changes
While data needs to be fetched from the server when a link is first used within a session of Access, changes to the web service infrastructure enabled fetching incremental data changes. Access 2007 caches the data rowsets in memory after they are first opened, so subsequent usage of the SharePoint links becomes significantly faster. In cases of large datasets, there is contention for RAM on the client machine. In this case, rowsets are released, freeing RAM back to the rest of the system.
Cache lists in XML
In addition to the in-memory caching, there are options to cache the XML within the database using either offline mode or cached mode. This helps in cases where the rowsets were recycled out of memory or when using the links in a new session of Access, as some of the server calls and network time were eliminated.

Generally speaking, Access 2007 performance was much improved from Access 2003; however, it quickly slowed when users connected to lists with more than 5,000 records. The primary bottleneck was the resources required to parse and load the XML data. We had an explicit goal to support much larger datasets with Access 2010.
Improvements in Access 2010
Just like Access 2007, Access 2010 connects to SharePoint lists via web services over HTTP. When a user opens a query,Office 2010 Standard Key, form or report that consumes data from a SharePoint link, web service requests are made to the SharePoint server to get the list data. The server returns the data in XML which is parsed and cached in local tables. Lastly, the data is fetched by the Access data engine and displayed to the user.

There are three areas of improvement where Access 2010 delivers a better user experience:
Cache data in local tables, Improve bulk query operations, and Reduce web service calls. Cache data in local tables
Access 2010 enhances the connected SharePoint list experience by caching data in local tables which are persisted across Access sessions. This enables Access to parse the bulk of the SharePoint XML data only once. Subsequent use of the links just fetches incremental data changes from the server. An additional benefit of using local tables internally rather than the in-memory rowsets is that usage of multi-valued lookups and value lists in a SharePoint list becomes much faster.

The first time a link is opened, Access still has to download all of the data from SharePoint and parse it into the local tables. In a situation where all of the data is needed immediately, whether from opening and moving last in the datasheet or executing a query with a join, the time to use the data is about the same as in Access 2007.



The real time savings happens the next time the link is used, either after starting a new session of Access or when many SharePoint links are used within a session. The performance for even a 50,000 row list is nearly comparable to a local table and significantly faster than the same scenario in Access 2007.



The additional benefit of the locally cached table is the memory footprint is radically reduced.


Using Access 2010 Cached Mode
The new cached mode is turned on by default for all new ACCDBs and published applications. Existing ACCDBs can use the new cached mode by checking the following box available through File -> Options -> Current Database:


This mode should not be selected if that database will be shared with Access 2007 users, though it can be used in a copy of the database that will be shared.
Bulk query operations
When exporting data to SharePoint, changes are typically sent up in batches of 50 records at a time. Access 2010 extends this batching to bulk queries against SharePoint lists,Windows 7 Key, bringing the time to update 100 rows from 30 seconds to less than 3.
Reduce web service calls
During the course of developing Access 2010, multiple customers came to Microsoft and tried the new experience with cached mode. In one case, a customer had a complicated form with many sub-forms and tabs that referenced nine different SharePoint links. Previously, opening the form took over a minute,Windows 7 Professional Key, but the time decreased to less than 20 seconds when opened in cached mode. We analyzed the web service calls being made when opening the form and made changes to ensure each use of a SharePoint link on open required only one web service call. This decreased the time to open the form from 20 to 4 seconds.
What’s next?
While Access 2010 delivers significant improvements, performance work is never done! The size of the lists used is constantly growing. Users may connect to SharePoint servers located across the country or on another continent. As a result, there are more opportunities to investigate how to use the cached data more efficiently and further reduce the web service calls.

Take the new bits for spin—you can download the beta and try it against Access Hosting today. We would love to get feedback from you about your scenarios and the improvements you see.
<div