cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chungchung
Microsoft
Microsoft

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

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
New Member

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

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!

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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,536)