0

I would like to use MS Access to update one field on a SharePoint list. I run into serious performance issues with MSAccess locking up for +15 minutes. Are there steps I can take to optimize the query or connection?

Details are below:

I have a local table in MS Access where the ID column joins on a corresponding column in SharePoint.

ID     STATUS

1      STARTED
2      STARTED
3      NOT STARTED

The update query is simple:

UPDATE SHAREPOINT
INNER JOIN ACCESS ON ACCESS.ID = SHAREPOINT.ID
SET SHAREPOINT.STATUS = ACCESS.STATUS

There are about 800 records to be updated out of a total of 3500 on the SP List. This seems superficially like it should run fairly quickly - as a SELECT it runs instantly. Is the INNER JOIN causing a performance issue? Something SharePoint side (with locking records on update)?

Any ideas on how to optimize or workaround are greatly appreciated.

0

We determined that there was a load-balancing issue affecting the SharePoint environment overall which contributed to the (otherwise inexplicable) performance issues in the update query.

N.B. - the performance issue stemmed from SharePoint Designer 2010 enabled sites. The Microsoft guidance on partitioning suggested that our environment could handle more simultaneous sites/users than it could in practice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.