0

I'm doing a lot of operations on very large custom lists, comparing them to SQL tables and updating items in the lists based on those tables. Currently this is a pretty slow process because it takes forever to retrieve a large ListItemCollection from SP and perform operations on every item in it.

What I was wondering is, would it be more efficient to perform such queries and updates directly to the table in SQL containing these list items instead of going through the SP API?

2

Directly accessing the SharePoint Database not recommended in production in any who. But you can do it in Staging or dev/test farm. For read operations:

Reading from the SharePoint databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance. Any read operations against the SharePoint databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft SharePoint Development Team or by Microsoft SharePoint Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.

Method you are using to query the List, put big performance hit. So best options are using the Web Services, Rest Api. here are couple of examples to query large list.

http://social.technet.microsoft.com/wiki/contents/articles/18202.sharepoint-2010-search-using-the-keywordquery-class.aspx

Using the Content Iterator

http://www.microsoft.com/en-us/download/details.aspx?id=12768

1

Of course it will be, however, it's also not recommended to touch the database directly. Perhaps you could do this for the reading part only, but use the SP Api to write?

  • Why exactly is it not recommended, just out of curiosity? – thanby Jun 24 '14 at 18:57
  • Lots of dialog online about this, boils down to the same reason you don't touch any managed database, if you mess up.. bad things happen.mycentraladmin.wordpress.com/2012/07/16/no-touchy – Aboba Jun 24 '14 at 19:00
  • 1
    Ah thank you. Actually that article points to a KB that says not to even read from it either! – thanby Jun 24 '14 at 19:08

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.