When you build flows in Microsoft Flow or apps in Power Apps most likely you will be using data of some sort. Where should you be storing the data?
In this post i’m considering SharePoint lists and I’m considering the Common Data Service ( CDS ). Which one of these lists is better? There is always the choice Common Data Service or SharePoint Lists
First of all we need to have a look at what we can do with lists. There will be different operations In this post I will compare:
- Creating / Updating items
- Reading items
- Large amounts of data
I’m focusing on the performance of the two data stores, however you should consider more than just performance when you implement a solution. When you store data in SharePoint you create a list that may be visible to people through other means that just your app. Is this really what you want? Yeas of course there is the construction of a hidden list, but that is something nice for developers however the citizen developer will most likely not know how to create a hidden list.
Item creation
I started by taking a simple flow. My flow runs two parallel Do Until actions. As soon as acertain number of items have been created in the SharePoint list or CDS the flow will exit both branches.
As shown below the creation of SharePoint list items is quite a lot faster. While I created 500 SharePoint items in 10 minutes the CDS only created 350 items.
Before making any major conclusions here it is important to have a look at the throttling limits for both connectors. The Common Data Service Limits are
1000 calls using a renewal period of 1 minute
SharePoint’s throttling limits are worse:
600 calls with a renewal period of 60 seconds.
Ok, that means that the difference here is nothing to do with throttling.
Both my SharePoint list and my CDS entity are the plainest possible type of items. When you investigate the CDS a bit further however you will find that even though the Entity that I created hasn’t got any custom fields or relationships, there are quite a few relations hips created by default. This could quite well means that internally the CDS is creation more than just one record in its databases.
I didn’t want to stop with just 500 items though. I did another test with 4000 items.
As my list is hitting the 5000 items limit I’m going to hammer the system a bit more. Time to run multiple instances of this flow and create an additional 12000 items with 3 flows running.
Reading items
After creating the items I decided to compare the reading of the items using a similar flow with 2 parallel branches.
At the first moment I thought that SharePoint was a bit slower but actually the CDS only returns up to 512 records. This might not be an immediate problem, you will simply have to make sure that you specify the queries better so that you only get the records back that you are interested in.
Alternatively you can enable pagination on the CDS action and then you can get more items back:
After I enabled Pagination I found a more expected result.
Once again SharePoint lists are beating the CDS entities.
Then I tried reading the items when I had more than 5000 items.
It looks like the CDS can only read up to 5000 items. Even with pagination enabled and limited to 100000 items.
My thought
Ok, it looks like the SharePoint lists are the winners when you look at the performance of two connectors considered. Also when I look at the number of available actions, SharePoint is going to win big time. However, the Common Data Services do have a place in the no code solutions. In the past I’ve developed many web parts, apps that hide the data in SharePoint lists from users. By separating the data layer (lists) and the presentation layer (apps or web parts) the CDS could be a good way forward. Also the option of proper relation ships between the entities is something that has been missing in SharePoint lists. Yes of course lookup fields can offer you this, but still SharePoint is not a relational database. As always, if we start using the CDS more often in our applications then it will make improving the technology more attractive for Microsoft.
Thank you, this article has been very useful. I use SharePoint lists as my data source and have a question. you talk about a 5000 item limit? Is that the max number of items that can be stored in a SharePoint list?
In the past 5000 items has always been a magic number where list views will struggle. With modern changes to the list indexing this has been improved a lot. There isn’t really a list limit of 5000 anymore.
I have this dilemma if I should consider switching to an sql database because when my list number grows, it will cause performance issues in my PowerApp. Also since collection max is 2000, I am thinking of using filter function instead but that causes delegation issue. Wondering how I could address these two concerns before it becomes issues. Would you have any recommendations? Happy to have a phone chat to explain my exact scenario.
Hi!
This article is certainly very helpful and it has given some impressive insights. My question here would be, what is the selling point of CDS then? I mean a lot of organizations’ data is in the form of Sharepoint lists. What benefits of CDS might make them consider moving to CDS for relational storage? There must be something which I’m missing here, as I have noticed Microsoft is working quite actively in this domain these days.
The relationships between entities. The database qualities of CDS are a lot better than SharePoint will ever be. E.g. the 5000 item limits in SharePoint can be a pain. The option to deploy solutions to a CDS as packages of a whole solution and so much more is a big benefit.
I see CDS a bit like a database without the need for a Database Administrator.