Blog
Settings - What's the purpose of the "Explicit Column Selection" Setting?
The "Explicit Column Selection" setting improves the performance of our apps. But how exactly does it work, and what problems can it cause? This post describes the setting in more detail, including demonstrations of behaviour with traces.
Many users often discover this setting only when
they encounter a problem where data doesn’t load or appear correctly. In such cases, disabling this setting can magically fix the problem.
What exactly is the purpose of this setting and how does it work?
In this post, we’ll explore how this setting works against a SQL Server data source.
What does "Explicit Column Selection" do?
When an app carries out a data retrieval operation, “Explicit column selection” improves performance by retrieving only the data in the rows and columns that are necessary. It is enabled by default and it is good practice to retain this setting, because it improves app performance.
This feature works only supported data sources, which includes SQL Server and Dataverse.
Demonstration - Data Retrieval with "Explicit Column Selection" Enabled
Next, we run the app and trace the data retrieval using SQL Server profiler. From this result, we see that the app issues a query that selects only the PropertyID, Address1, and City fields. This outcome is efficient, because we retrieve only the necessary data.
Demonstration - Data Retrieval with "Explicit Column Selection" Disabled
We now turn off “Explicit column selection” and repeat this exercise. As we expect, Power Apps issues a query that returns all columns. This query is far less efficient because we return more columns than is necessary.
What Practical Impact does this have on Performance?
With this example data, the data transfer size of all columns in 2,000 rows is 456KB. With just the PropertyID, Address1, and City fields, this falls to 152KB. With this specific example therefore, “Explicit column selection” can reduce the gallery load time by around 60%.
What problems can “Explicit Column Selection” cause?
Most commonly, this setting can prevent data loading or appearing correctly in an app. If we experience some of these problems, we should investigate the “Explicit column selection” setting:
- Certain fields appear blank, particularly in cases where we populate controls based on collections that are pre-populated from SQL Server
- Drop-down fields fail to show the correct data
- Controls populate correctly at design time, but not at runtime.
Example of a Typical Problem
Here’s an example of a typical problem. Here, we have a table of issues that are linked with the properties table through the PropertyID field.
Let’s say we want to populate a collection of issue records that match PropertyID 35. To do this, we write the following formula:
ClearCollect(colIssues, Filter('[dbo].[Issue]', PropertyID=35))
When we run this and inspect the colIssue collection, we see that the formula returns only data for the IssueID and PropertyID fields. The values in all other fields are blank.
In practical terms, this means that we cannot use this collection to display the issue descriptions, because these values have not been loaded into the collection.
Fixing this Problem
The fix for this problem is to explicitly define the columns that we want to return when we build the collection. We can do this by calling the ShowColumns function.
ClearCollect(colIssues,
ShowColumns(Filter('[dbo].[Issue]', PropertyID=35), "IssueID", "Description")
)
As we can see, the collection will now include just the IssueID and Description fields.
Conclusion
In summary, "Explicit Column Selection" is a setting that improves performance with SQL Server and Dataverse data sources. The best practice is to keep this setting enabled.
This setting can cause unexpected behaviour with data not appearing as expected. Often, the easiest fix is to turn off “Explicit column selection” however, the best way to solve these types of issue is to explicitly define the columns that we want to return with the ShowColumns function.
- Categories:
- data
- sql server
- Data - How to show the distinct rows from 2 data sources or collections
- Data - How to implement circular rotational date sorting
- Bug - What to do when the data section of the Power Apps Maker portal doesn't work
- Data - Combine columns from separate tables into a single table
- Formula - Transposing/converting rows to columns- an almost impossible task?
- Data - How to rename field names in a record
- Data - How to hide duplicate rows in a gallery / show distinct multiple columns in a gallery
- Data - Retrieving news/forum/blog articles with RSS
- Data - How to sort by partial numbers in a text field
- Data - How to return the last record from a table
- Data - How to create bulk test/dummy records with random values
- Data - 3 things you should know before using the MySQL or PostgreSQL connectors
- Data - A walkthrough of how to migrate the data source of an app from Excel to Sharepoint
- Data - How to enforce unique values (or prevent duplicate values) in one or more columns
- Data - How much mobile data does Power Apps consume? What ways can we minimise this?
- Data - How to save and retrieve Google calendar entries
- Data - How to save and retrieve Google contacts
- SQL - Caution! This is how users can hack shared SQL connections
- SharePoint – 2 Mistakes to avoid when importing Excel data
- SQL - Don't let this DateTime bug catch you out!
- SQL Server for Beginners Part 3 - Installing On-Premises Gateway
- SQL Server for Beginners Part 2 - Installing Management Studio
- SQL Server for Beginners Part 1 - Installing SQL Server
- Searching data–What you need to know about case sensitivity
- Images - How to create images that can change depending on data
- Excel - Reasons NOT to use Excel as a data source
- SharePoint - What you need to know about Filtering Data
- Formulas - Generating Row Numbers