You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest writer is DJ Cole from the Access development team. DJ is the architect and primary developer of SharePoint connectivity performance improvements.
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, it helps to understand how Access 2007 works against SharePoint.
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:
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.
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.
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.
Just like Access 2007, Access 2010 connects to SharePoint lists via web services over HTTP. When a user opens a query, 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:
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.
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.
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, bringing the time to update 100 rows from 30 seconds to less than 3.
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, 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.
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.
Comments: (8) Collapse
Clint,
This looks great. I'm expecting to use Access in this mode, i.e. a rich client with Sharepoint as the back end, rather than web forms and web reports. I have one, possibly two customers lined up so roll on Release To Manufacture! A couple of questions for you: 1) Will Access 2010 run in the same way as the full version of Access 2010? Access 2007 runtime did not (it couldn't toggle online/offline) and caused me a major headache.
The 2010 public runtime beta seemed to act the same as the full version beta even though it came up with a rather ugly error message when the data was being updated. Despite the error message, the data was updated OK.
2) Partly related to the above, how do you set the "Use the cache format that is compatible with Microsoft Access 2010 and later" setting (and associated "Clear Cache on Close" and "Never Cache") programmatically. Is it via SetOption? Alan
That is really incredible performance. This really gives access a near perfect disconnected solution. Running reports means the cpu load is local and fast. This plays to access strength by using the fantastic CPU power of the desktop but allows sharing of data. Talk about a scalable solution. I did some application testing a few years ago and I had to forgo using SharePoint for one of my solutions. My tables were really not really very large, not much over 10,000 records. Looking at this new 2010 solution, this is right on the money. My world would be totally different if I had this technology just a few years ago. I can't wait to test out some of my production applications. Being able to send sales force people out of the office and letting them work without no internet connection and then syncing up the data when they get back to the office is an fantastic side benefit of this local data system. Disconnected solutions was one major area of solutions that .net people had huge leg up on us, but now no more. Access lets us build local desktop applications, lets us build shared data applications, lets us build web based applications. This Data caching feature simply again widens the already amazing battery of solution choices that access gives us developers. This is a major feature with usability that expands the reach of solutions that access developers can offer with access. Albert D. Kallal
Edmonton, Alberta Canada
Very nice, I' happy to hear that Access 2010 has become a better off-line client
Question? How woould you compare then the new Access 2010 Sharepoint List improvements vs Linked Tables (w/SQL server)?
To explain my request for info in my first post a bit more....
The Access 2007 runtime does not allow for taking a Sharepoint-based database offline (or putting it back online again), though when tested using a full version of Access 2007 with the /runtime command line switch or giving the file a .accdr suffix, you can take it offline. This meant that the Access 2007 runtime was severely restricted.
I am concerned that the Access 2010 runtime may be going the way of the Access 2007 runtime and may not be suitable for use with Sharepoint 2010. If it is not possible to manage "Use the cache format that is compatible with Microsoft Access 2010 and later" setting (and associated "Clear Cache on Close" and "Never Cache") programmatically, what assurance have we that we can use Access 2010 with Sharepoint 2010? I appreciate that I may have missed something obvious, but currently I am very concerned. Alan
Llin Wong,
I've not seen any comparison of speeds between a Sharepoint 2010 back end and a SQL Server back end, but having a Sharepoint 2010 back end has the following advantages that I can see: 1) You can take a Sharepoint database offline without having to actually do anything. To me, in certain situations, this is going to be brilliant (assuming it works correctly of course).
2) There is the possibility of easily handled security. I know SQL Server security is good, but if Office Live eventually uses Sharepoint 2010 (and surely it will), then you can use Windows Live ID as the basis of your security.
3) Upsizing an Access 2010 database to Sharepoint 2010 seems more tightly integrated than Access going to SQL Server (though the MUST tool for upsizing to SQL Server is excellent). Alan
With regard to my posts about the Access 2010 runtime, I look like I may well "have missed something obvious". I see that the control of the cache comes under the Current Datatabase option and so can be set by a user with the full version of Access 2010 and then make the database available to any Access 2010 runtime users. Would it please be possible for someone to just confirm this. Many thanks.
> Question? How woould you compare then the new Access 2010 Sharepoint List improvements vs Linked Tables (w/SQL server)? That is a great question. However your question is a bit like what is the gas mileage of one of the new plug in hybrid cars. If you include the electricity charge into the gas used for a miles per gallon rating after a full charge, then you get these weird numbers in the 100’s of miles per gallon. Realistically a fair rating would be what is the MPG without any electricity involved. You then would ask what is the mileage you can get for 1 kW of electricity. Any other kind of mixed up rating is completely useless information to any intelligent consumer. The same goes for this question when you ask how does this setup compare to SQL server? Well because we using a VERY HIGH performance local data cache, if you have 100,000 records, you can likely sort and group this report data in under one second with a query. The reason for this the course is that the 100,000 records don’t have to travel across your network or Internet connection anymore. If you modify one of the 100,000 records, then only the one record is transferred back up to the server. So in this type of scenario or comparison, this setup will run circles around SQL server. Remember in these types of scenarios, it is not the speed of the database server that’s the problem, it’s the speed of your network transfers. When working with sql server you have no local data cache. This means to view + edit records in a form the record must transfer down the wire (connection) for editing in a form, and then be transfereed back up after editing occurs. If we assume we already have the record local then we saving half the bandwidth for an edit. Same goes for reporting on that data. So, for many scenarios, this will given an better experience then trying to use sql server over the internet. However there are scenarios were the reverse would be also true. For example, if you have to modify a lot of records, then each record modified has to be sent up a network connection. With SQL server those large record updates can occur server side without network traffic. So there is some give and take here. This setup gives us an decent and easy to use disconnected record set system. We don’t have this type of setup available with SQL server right now. Albert K
Comments: (loading) Collapse