This play provides an introduction to database query optimization.
As a general recommendation, design your applications using Appian Records with Data Sync enabled. The Data Sync feature caches your source data in Appian and provides the Appian Data Fabric framework to efficiently query data without the need of manual optimizations.
As an enterprise application used for a wide variety of tasks, Appian is often integral to an organization’s productivity. The performance of Appian applications could directly impact employee task completion times and in turn their organization’s KPIs. The perception of Appian as a platform being performant or non-performant can also greatly influence end user satisfaction and thus further organizational adoption of a system.
One of the most common actions a typical Appian app performs is reading or writing to a database. Due to the sheer frequency of database operations, even small performance improvements of database interactions can greatly increase the perception of overall database performance by reducing the immediate response times to the user and the overall load on the server. If your app uses a database, optimizing database interactions should be a part of your release plan.
Database and query optimization is an involved topic, and can often feel like an art, rather than a science. Optimizing for all three of performance, memory usage and maintainability/code clarity together may simply not be possible in most cases. There may be no single configuration that is optimal for all scenarios, and as an Appian App Developer you should evaluate the options available and weigh the benefits/trade-offs to develop the design. This document is intended to provide an introduction to a number of different optimization techniques and rules of thumb.
For the purposes of this document, the following definitions apply:
Some overall details to keep in mind:
When designing your Appian records or CDTs and the underlying tables, always keep in mind how the application, and the end users, will be interacting with them. Some sample criteria to determine:
Understanding how the application will use the data can help guide you, as an application developer, in the structure of your entities, such as deciding how Normalized vs. Denormalized your tables should be. As an example, consider a scenario at a Courier company with a Package Shipping Request CDT representing data associated with a Package delivery, and a Customer CDT representing data about the person who requested the delivery, such as an account number, invoice address and phone number.
Most databases, including Oracle and MS SQL server, use internally computed statistics to decide on the appropriate strategy for creating the query execution plan, e.g., which order of operations to perform, which indexes to use on which table, etc. Up to date statistics are often critical for the database to choose the most efficient query execution plan.
When attempting to improve query performance for query entities, the general sequence of steps is as follows:
Keep in mind that returning the total row counts using the flag “fetchTotalCount” executes the query twice in Appian. Setting this flag to false will help improve performances.
As an Appian App developer, most of the time you will not be writing SQL directly for queries, instead you will be using a!queryEntity(), a!query() and a!queryFilter()s, which will construct the actual SQL for you. You should be aware of some best practices in interacting with the database to improve performance of a!queryFilters().
SELECT * FROM orders WHERE deliveryDate > DATE_ADD(NOW(), INTERVAL 2 DAY);
it may be more performant for Appian to compute the date of Today()+2 itself, and send the single hardcoded date to the database. In more complex sql statements, the impact may be even more evident.
The query execution plan, or explain plan, is a set of steps that a database will perform in order to obtain the result set. Reviewing the explain plan is one of the best ways to understand how your database performs the query, broken down into ordered steps. By examining this plan, you can determine if the database is picking the optimal indexes and joining the tables in the most efficient manner. (Note that indexes are referred to in more detail in the next section.) In general, a query execution plan will contain:
Oracle, SQL Server and MySQL all include one or more tools which will provide details on the query execution plan. Some tools are text based, some XML and some even graphical. For the exact details on these tools, please refer to the documentation provided by your database vendor.
Sample commands to retrieve a plan
An example query execution plan, from the sample Oracle HR database, is as follows:
Another example, from the sample MySQL sakila database:
A full understanding of query execution plans is beyond the scope of this document and is both database vendor and version specific. Please refer to the documentation provided by your database vendor for more specific details.
To use the query execution plan:
Database Indexes are additional structures added to a database schema, usually to facilitate data retrieval. They are used to quickly find the results of conditional expressions, i.e., rows that match certain criteria, without having to search every row when the query is executed. Sample SQL syntax for creating an index would be as follows:
CREATE INDEX index_nameON table_name (column1, column2, ...);
Here is a very simple example. Let’s consider the table Inventory with the following structure
This table has roughly 4500 rows, consisting of the inventory to multiple stores in a central database. In order to retrieve the inventory from a single store, we could run a SQL query like:
SELECT * FROM inventory WHERE store_id=2
In this case, the store_id column is not indexed, so the database engine will scan through the table, finding all the rows with store_id=2. Running the explain plan on this simple SELECT when the WHERE column is not indexed results in the following (on MySQL).
Since we know that this store_id column will be queried regularly, we can add an index to this column to facilitate future searches.
CREATE INDEX idx_storeID ON inventory (store_id)
If we take a look at the explain plan after the table is indexed, there are some important changes.
In this simple example, we can see immediately that there is a key (or index) that is being used, and in the rows column that fewer rows are being considered by the database engine. There is a corresponding increase in performance as well, with the query execution time on the indexed table actually being 5X faster than those of the non-indexed table.
Some additional guidance:
DROP INDEX index_name ON tbl_name;