Optimizing Queries within Appian
System performance is one of the key indicators of a successful platform. Even if your software solution incorporates all the right functionality and has a great interface design, being slow and unresponsive degrades efficiency and ruins the user experience. These performance issues for business users can be costly in lost productivity and negatively impact the morale of employees who have to deal with slow technology every day.
Following best practices for software development and understanding how to optimize your software applications is critical to the productivity and success of an organization.
A Low-Code platform helps simplify the development process for everyday business users, making it easier to avoid making many common performance mistakes. However, even with a low-code platform that simplifies developing applications, poor architecture and design can lead to performance issues.
The Appian Low-Code Platform is one of the best on the market. It’s incredibly versatile and powerful, allowing you to automate processes, decision making and data flow. Regardless of coding experience, Appian is a valuable resource to businesses and employees to improve workflows and processes across an organization for significant increases in efficiency. Vision Point System (VPS) specializes in Appian development and focuses on enterprise-level performance when creating an application to ensure organizations get the most from their key software systems.
With any Appian solution there are many ways to maximize performance and optimize the platform based on your architecture and functionality. A simple and common mistake that we have seen in Appian projects – that goes against our best practices – is querying more data than you need in a given interface or process. The more data, the more time it takes to transfer, process and display.
We’ll explore this mistake through an example provided below:
In this example, pretend you are an operations company that uses Appian for processing orders. As a part of the process, your company ships orders to 6 separate warehouses. Warehouses have an existing data type within your Appian instance. For those unfamiliar with Appian, data types function very similarly to a database table or a Salesforce object. The framework of the warehouse data type is as follows below:
Now, let’s pretend that you’re an average user, a salesperson. You just made a sale and need to log which warehouse that order should be sent to in the database and on a sales table. In order to complete the order, you need to select the warehouse in some way like below:
Let’s say that your employees are familiar with the warehouses by name. Therefore, they don’t need additional insights like what address, city, state, country, and zip code they are in. As a result, your user low-code interface only displays the names of the warehouse to your employees when they select one, as shown above.
In order to display this information to your employees, your system will need to query where this data exists. However, it’s important to note that it should ONLY be pulling the data that it needs. Here’s what I mean:
WHAT NOT TO DO
This is an Appian expression rule. It functions very similarly to a query within a database. In simple terms, this is the request the system sends to the database to pull back the list of records for warehouses. A record represents each individual warehouse that exists in the database, so our system would have six records in the database.
However, this expression rule pulls back ALL of the data that exists on warehouses. Developers do this because it’s simple and versatile, allowing for agile capabilities. If you query all of the data that exists within a warehouse, you don’t have to worry about which data you need or don’t need later. Sometimes, data structures are simple enough that pulling all of the data isn’t a big performance hit.
We previously discussed that the only data that our average business user needs, in this case, is the name of the warehouse, allowing them to input data in a faster time. Pulling data back on the first line address, second line address, city, state, zip code, and country will be completely unused in this process. This applies to more than half of the data you’re pulling back. So, how do we fix it?
WHAT TO DO
In the fixed version, we’ve made the fields that this expression rule is pulling back dynamic. Appian has a concept called rule inputs, which allows you to pass values into a variable. The notation “ri!” signifies a rule input. In this case, the rule input “columns” is an array of strings (Text) where you can pass in which fields you would like returned on your query.
rule!getAddressByCriteria(
columns: {“id”, “name”}
)
OLD QUERY
NEW QUERY
Passing this information above would only then return the fields needed. For our example, we would need to return the database ID and name for our warehouses. The other fields we do not need for this user interface wouldn’t be returned. As a result, we saved 2 milliseconds on this query. This example is also quite simple. Appian data types could have 20 or more fields, instead of 8. The incorrect query would be pulling back even more unused data. Six records is also a very small amount to query in real time. Depending on what information you need, a real system could have hundreds, thousands, or even more records to pull. These types of mistakes could cripple the performance of your system beyond a 2-millisecond increase.
An added benefit of this is it removes the need for multiple expression rules. A common mistake I see developer teams make is to have a separate expression rule for querying all data, querying just the id and name, querying just the id and city, etc. This removes that need for additional actions and allows your expression rule to be reusable. This makes your code more maintainable.
VPS uses a series of best practices similar to this one when designing and developing Appian systems for database programming and other complex processes. If you’re interested in learning more about our Appian services for the average user, read our accessible selection of additional resources here.
Author
