Unanswered

Lookup to Virtual Tables Performance

(0) Share

Report

Posted on by Tommy-Upton

119

We have a lot of data stored in local SQL servers that we connect to. When I connect directly through the SQL gateway, performance is good. I've recently started to store some data in Dataverse so I've pulled in some data from SQL as virtual tables. That seemed OK too.

However, when I create lookups from the Dataverse tables to the virtual tables (parent/child relationships), performance in my apps is awful. I have a Dataverse table with less than 1000 rows and it takes about 30 secs to load them in a detailslist if I reference the lookups. Connecting to SQL directly is fine...connecting to Dataverse only is fine. Anyone else seeing this? Here is some sample code - nothing special.

AddColumns(
 Filter('Patient Alerts','Alert Status'=lclStatus),
 "EpisodeID",ThisRecord.Episode.epi_id)

Categories:

Microsoft Dataverse with Power Apps

All responses (6)

Answers (0)

Sort by

Michael E. Gernaey 53,970 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Tommy-Upton

It really depends. Are the lookup columns in SQL (the mapped) indexed from the SQL Side? Possibly either Creating Filtered Views in Dataverse and/or Creating Views in SQL (which you use as a virtual table), can help.

I'd have to see some back end tracing to tell for sure what the issue is, and this "can" be an issue with virtual tables, but 30 seconds is a very long time.

How many rows is it finding in that 30 seconds?

Also you mentioned it being on the LookUp column, but is Alert status really the lookup column? That seems more like an int or a Choice column plus you are literally adding a Column to the entire table or collection (I cannot tell to what), which also takes time.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey
EricRegnier 8,720 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Tommy-Upton,

Looking at your expression, it's not very performant (complexity On^2). It filters "Patient Alert" and for every row, does another retrieve on Episode.

I would try to redesign of my app slightly, instead of adding EpisodeID column and using EpisodeID in the app. I would directly reference epi_id where and when you need it. For example, in a gallery:

ThisItem.Episode.epi_id

Another thing you can look is ensure the following setting is enabled:

Hope this helps!
Tommy-Upton 119 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks, Eric, for the reply. I have toggled on/off explicit columns and it doesn't seem to make a difference in performance. For the columns, there is no way to reference a column that is not in the items() property of the DetailsList. epi_id doesn't exist in the parent table so you must reference the parent and walk it down to the child table to get the field you want to show. Or, maybe I'm not understanding what you are trying to tell me. Here is the table structure:
Patient Alerts (Dataverse Table)
Episode (virtual table connected to SQL table)
In Patient Alerts, there is a look up to the Episode table called Episode (1:M) relationship. The documentation says I should be able to add both Patient Alerts and Episode as data sources in the App, set the Item() property to Patient Alerts for the DetailsList and still reference Episode fields (child fields). However, I never could get this to work. Child fields would never populate in the Fields list for me to add to the DetailsList. So, I used AddColumns to do a lookup for each record to add the child fields I needed. I get that is poor design, but how else would you get to a child table?

And this is not a Dataverse or SQL performance issue. Both environments perform great on their own. But when I join them like this performance is awful.
Tommy-Upton 119 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks, Michael. I couldn't get filtered views to work any better as the datasource to a DetailsList. I created views on the parent table (Patient Alerts) with the fields I wanted from the child table (Episode) and put that view as the datasource to the DetailsList. It wasn't any better.

Alert status is not the issue or a lookup. It's Episode (see more detail in my response to Eric)
EricRegnier 8,720 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for supplementing @Tommy-Upton and understand your data model more now. I believe my initial response still stands, you should be able to reference directly Episode fields form Patient Alerts since it's lookup on Patient Alerts, and you added both tables to the app. In Patient Alerts, are the lookup value of the rows set to desired Episode?

I've tested it in my environment, and it works both ways meaning with virtual table with lookup to Dataverse table and Dataverse table with lookup to virtual table.

What is DetailsList, a gallery control?
Tommy-Upton 119 on at

Like
a
(1)

Report
Copy link

Link copied!

DetailsList is a control in the Creator Kit. It's a gallery at heart but a very nice control that I user everywhere. I rarely use a gallery any longer. Let me try again to see if I can reference child fields with a regular old gallery.

https://learn.microsoft.com/en-us/power-platform/guidance/creator-kit/detailslist