When you build flows in Microsoft Flow or apps in PowerApps 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?
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.
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.
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.
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.