In this blogpost, we will learn to optimize & improve the data query practices in Canvas App / Power Apps. Before we begin, ensure you subscribe to CRM Crate to remain informed about the latest developments in the Power Platform field.
In Microsoft Canvas App, part of the Power Apps suite, data querying is a fundamental feature that allows users to interact with data from various sources, such as SharePoint, SQL Server, or Common Data Service, within their applications. By using built-in functions like Filter, Search, LookUp, and Sort, users can efficiently retrieve and manipulate data to suit their app’s needs. These functions enable dynamic data handling, allowing for real-time updates and interactivity. Additionally, Power Apps provides a visual interface to build and test these queries, making it accessible even for those with minimal coding experience. The integration of data querying within Canvas Apps enhances the capability to build robust, data-driven applications, facilitating better decision-making and streamlined business processes.
Improving data querying practices in the Canvas App
When building your app, it’s crucial to streamline your data retrieval process for speed and efficiency. A straightforward approach involves focusing on three key aspects:
- Simplifying Data Source: Opt for a single table or view that contains all necessary information. This minimizes the need for complex joins or lookups later on.
- Server-side Filtering: Ensure that data is prefiltered on the server to retrieve only what’s needed. This reduces unnecessary data transfer and processing overhead.
- Optimized Indexing: Properly index the columns used for filtering or sorting to expedite query execution and enhance overall performance.
For instance, let’s consider a scenario where your Canvas App displays customer and salesperson details in a gallery component. Storing this information in separate tables requires frequent lookups, slowing down the app due to multiple queries. A more efficient approach involves creating a consolidated view that merges customer and salesperson data, serving as the primary data source. This way, your app executes a single query to fetch comprehensive data, enhancing speed and responsiveness.
It’s essential to balance data normalization, which minimizes redundancy for consistency, with the need for optimized querying. Striking this balance ensures your app remains fast and responsive, avoiding unnecessary delays caused by excessive data processing and joins across multiple tables.
Efficient ways for querying data:
Use server-side views
Using views is likely the most typical way to strike a balance with these objectives. They offer a unified table setup for inquiries, sift data in advance based on what’s needed for the query, and facilitate connections and comparisons with other tables. Since the view’s filters, comparisons, and connections are processed on the server, it reduces both the data load and the computation needed on the client side.
Leverage Power FX functions like StartsWith or Filter
Power Fx offers various methods for data searching. Typically, opt for an expression that takes advantage of an index, such as StartsWith or Filter, rather than one that scans the entire table, like ‘In’ function. However, using the In operator is suitable for in-memory collections or when dealing with a very small external data source table.
Prevent multiple invocation of Lookup function
A gallery can exhibit numerous records sourced from a database. Yet, there are instances where you’d want to showcase additional details from a different database related to the original one. Take, for instance, a gallery displaying customer information—you might also wish to display the salesperson’s name assigned to each customer. However, the salesperson’s details are stored in a separate database. To incorporate this data, you’d utilize a lookup function to match the records between the two databases, essentially expanding the initial table with these lookup values.
Nevertheless, this expansion process can become quite sluggish when dealing with a large number of records and multiple lookups. For every record in the gallery, the application must execute a distinct query to the secondary database to fetch the lookup value. This translates to numerous queries for each record, leading to prolonged processing times and impacting the overall performance of the application. This phenomenon is often referred to as the “N squared” or “N+1” problem, signifying the quadratic growth in processing time as the number of records increases.
Think about replicating the data
To speed up data retrieval in queries, you might copy slow data to a local table for faster access. However, this means the local data might not always be the most current. You’d then need a process, like a daily Power Automate flow, to update the local data. This duplication strategy is common in enterprise apps for better performance.
