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?
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://www.microsoft.com/en-us/download/details.aspx?id=12768
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?
