Microsoft Flow / PowerApps – Common Data Services or SharePoint Lists

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:

  1. Creating / Updating items
  2. Reading items
  3. 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.

Flow to Compare Entity and SharePoint list

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.

CDS Relationships

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.

Pagination enabled

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.

 

Advertisements

3 thoughts on “Microsoft Flow / PowerApps – Common Data Services or SharePoint Lists

  1. 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?

    Like

    1. 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.

      Like

      1. 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.

        Like

Please leave a comment or feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.