Answered

Connecting data directly or through an entity?

(0) Share

Report

Posted on by WH-25070106-0 Microsoft Employee

Hi expert,
I want to build a canvas app to search a table which contains 150k rows and wish to see the update every 5 minutes.
Which data connection will you recommend? To build a SQL Server connector and query from it or create an entity on CDS and import the table from SQL then build the app based on the entity?
My concern is the performance, wishing the loading be as fast as possible.
Please advise.

Categories:

Connecting to Data

All responses (3)

Answers (1)

Verified answer

v-yutliu-msft on at

Like (0)

Report

Re: Connecting data directly or through an entity?

Hi @chungchung ,

1)delegation

Since your record number is very large, I think you should consider the delegation problem.

SQL have more delagable functions than CDS, so I suggest you use SQL.

Here are docs about delegable functions in sql and cds:

https://docs.microsoft.com/en-us/connectors/sql/

https://docs.microsoft.com/en-us/connectors/commondataservice/

2)easy

Since CDS is designed for PowerApps, so using it in PowerApps is easier.

If you want simple development process, I suggest you use CDS.

3)backup

If you want backup and restore data easier, I suggest you use SQL. Backing up of CDS is a little complex.

4)tools

CDS is only access to microsoft product, like Flow/PowerBI/PowerApps ,etc.

However, data in CDS can be used for more tools.

So if you want to use data through more tools, I suggest you use SQL.

This are 4 points that I think is impotant.

Here's a similar issue in details for your reference:

https://powerusers.microsoft.com/t5/General-Discussion/CDS-vs-Azure-SQL-Database/m-p/108940

Best regards,
WH-25070106-0 Microsoft Employee on at

Like (0)

Report

Re: Connecting data directly or through an entity?

Hi @RobertPeledie ,
Thanks for your sharing.
My SQL DB is either on Azure or Hosted. I can't get the answer as the IT dept outsourced it.
The problem is there are 100K+ rows in the DB and I need to filter them by Current User using the powerapp. Tried both way, the loading performance was not good when connecting directly to SQL DB (read about using collection/load data/save data/concurrent posts, but a 100K+ rows data doesn't work with collection). The data was not up-to-date when using entity on CDS. (Can't get the most updated rows...but the import was complete).

Any advise on this?
Thanks!
RobertPeledie 2 on at

Like (0)

Report

Re: Connecting data directly or through an entity?

Hi....
There are probably a few factors that are worth thinking about......
For example, you say that you are potentially accessing through a SQL Server. Is this on premises or Azure/Hosted?
This may be a factor.
I personally have used both options.
While it is good to access via CDS, there may be ocasions where accessing data directly from a SQL DB is better, or may be the only option (in my example the data was managed in SQL by another department, and there was no real reason to effectively replecate the data in CDS - IT would have just added another layer.

The bottom line is that it really depends on your need, but either is acceptable - Thats why there are connectors!
R