You’re offline. This is a read only version of the page.
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.
Share
ReportAddColumns(
Filter('Patient Alerts','Alert Status'=lclStatus),
"EpisodeID",ThisRecord.Episode.epi_id)
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
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?
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)
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.
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:
All responses (
Answers (ThisItem.Episode.epi_id
Another thing you can look is ensure the following setting is enabled:
Hope this helps!
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
#1
WarrenBelz
791
Most Valuable Professional
#2
MS.Ragavendar
410
Super User 2025 Season 2
#3
mmbr1606
275
Super User 2025 Season 2
