So your SharePoint list or perhaps lists are very slow to response or a custom solution that accesses your lists takes a long to complete its processes. What could be the problem? Old, dilapidated hardware? Poor database configuration? Slow network? How about the fact that many SharePoint solutions are designed as if they are a relational database.
After a long hiatus, I would like to start things off with something I brought forward to one of my clients the other day. Even though SharePoint looks like a fantastic place to build easy database solutions (like a replacement for MS Access) it simply is not built to do that. To the user SharePoint lists look completely separate from each other. So why can’t we simply create DB table like lists and have them reference each other, like having columns that are basically foreign keys to other lists just like a DB table?
In this case it’s not what SharePoint shows you, but how it stores it in the content DB. Unlike what many may believe, there isn’t a new DB table for every list and library within a content DB. SharePoint stores all of its list data and a library’s list item data (metadata) for every site in the content DB in one single table. That’s right. I think you are getting it, but let’s break it down a bit:
Site A: 3 unrelated lists with about 2500 items between them. It also has a library with 3000 documents organized with metadata.
Site B: One “active” list of about 3500 items and an archive list of about 45000 items. It also has 2 libraries. Library 1 has 500 documents and library 2 has 1700 documents.
Site C: A solution of related lists built as a Relational DB style solution. It contains 5 lists and totals about 125000 rows. It also has 3 libraries that total about 12000 documents.
So what this means is that for only 3 sites in a single site collection you have a single table of 189,700 rows that users are actively accessing constantly. It may not seem to bad, but think of this, what if you have 50+ sites in your site collection each with their own set of lists. That number can really grow. Microsoft has planned for this and set things up so the lists will still respond properly if they are built as expected. But once you start gathering data based on contents of another list you are stepping outside the design parameters and things aren’t going to respond as you wish.
But what about throwing on some indexes? Generally this can make things better, but again, the indexes are all stored in a single table and all the values are stored there too. So the more indexes you create, the more rows you will also create. Again, you could very easily create yourself the same problem you have in the main lists. What’s the sense in having an index nearly as large as the list(s) it is indexing?
So what’s the solution? Sometimes, SharePoint simply isn’t the way to go. However, there is another way. I would suggest placing your solution outside of SharePoint, but create BCS connections to surface the data from that solution instead. This way you can create the database tables the way they were meant to be used, but you won’t tax your SharePoint system in the process.
For some information on the AllUserData table (the table where the list data is contained) please take a look at this technet article: https://msdn.microsoft.com/en-us/library/hh625524%28v=office.12%29.aspx
I plan on speaking further on BCS later, but until then…
Great post David,
Do you know if it possible to connect BCS MySQL as well? And is it possible to update external tables using PowerApps or Flow?
Sorry for the late response. You can build a connector for just about anything. For database-based connections, you might even be able to do it without a custom connection. I’ll look into doing a blog on that in the future. As for PowerApps and Flow I am afraid you can’t. As of right now, they don’t have any concept of those fields that I have been able to configure.