cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chungchung
Level: Powered On

Connecting data directly or through an entity?

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
RobertPeledie
Level: Power Up

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

chungchung
Level: Powered On

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!

Community Support Team
Community Support Team

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,099)