Record Type Query Performance Best Practices

Record types are the foundation of your data fabric, allowing you to access enterprise data wherever you need it. You can query record data to display information in interfaces, drive logic in expression rules, or route workflows in process models.

How you retrieve the record data directly impacts the speed and responsiveness of your application. To ensure your apps remain performant and scalable, review the following best practices when configuring queries to your record types.

Specify individual fields to query instead of using a!selectionFields()

Guideline

When you query record data, specify the exact fields you want to return in the fields parameter of your query. Avoid using the a!selectionFields() function in your queries unless you must access all the fields in a record type or related record type.

Importance

The more data you query, the longer it takes to load and display that data on the interface.

While a!selectionFields() can be useful during initial development, it often returns more data than is necessary. Requesting every field, including those from related record types, can significantly increase the time it takes to load and display data in your interface.

As a best practice, only ever query the specific fields you need. You should also consider using filters and batching to ensure you only return the necessary amount of data you need.

Example

Let's say you want to create an interface that highlights your top five customers. For each customer, you want to create a card that shows the customer's name, their industry, and how long they've been a customer.

Do query the specific fields you need and the exact amount of data you need.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
a!queryRecordType(
  recordType: recordType!Customer,
  /* Only query the 3 fields you need to display in the card */
  fields: {
    recordType!Customer.fields.displayName,
    recordType!Customer.fields.createdOn,
    recordType!Customer.relationship.industry.fields.name
  },
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    /* Only return 5 results, and sort those results by customers with the largest year-to-date sales */
    batchSize: 5,
    sort: a!sortInfo(
      field: recordType!Customer.fields.YTDSales,
    )
  )
).data

Don't query all the fields in your record type and related record type if you don't need access to all of them.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
a!queryRecordType(
  recordType: recordType!Customer,
  /* This fetches every field from Customer and Industry, increasing load time  */
  fields: a!selectionFields(
    allFieldsFromRecordType: {
      recordType!Customer, 
      recordType!Customer.relationships.industry
    }
  ),
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    /* Returning 5000 rows when only 5 are displayed wastes resources */
    batchSize: 5000,
  )
).data

Add sync filters to high-volume record types

Guideline

To maintain the best performance in your largest synced record types, add sync filters to limit the data synced in Appian to only what is necessary for your application.

Importance

Large datasets increase the resource requirements for every query operation. Without sync filters, Appian syncs all available source data, which can increase memory usage and query latency. By syncing only the data your business processes require, you keep your queries fast and your application responsive.

Only query real-time custom record fields and Extra Long Text fields when necessary

Guideline

Only include real-time custom record fields and Extra Long Text fields in your queries when the data is essential.

Importance

Real-time custom record fields and Extra Long Text fields require more time and resources to retrieve than your standard record fields. For this reason, they're often referred to as expensive fields. Including these fields unnecessarily, especially in grids that return many rows, may impact query performance.

Example

Let's say you're configuring a grid of employee information. The Customer record type has a real-time custom record field called numberOfOrders (which counts the number of orders for each customer), and an Extra Long Field called bio (which contains each employee's biography).

Do include the real-time custom record field in the grid since users want to sort the grid by customers with the most orders.

Don't include the Extra Long Text field in the grid since it's not necessary to display each customer's bio in the grid. Instead, use that field in individual record views so it's only evaluated for a single record at a time.

For complex grids, conditionally query the fields

Guideline

When configuring a records-powered grid with conditional logic, use the fields parameter in a!recordData() to explicitly control when each field is queried.

Importance

By default, a records-powered grid queries every field referenced in the following parameters:

• columns (Regardless of whether the column is currently visible)
• pagingSaveInto
• selectionSaveInto

If your grid contains a large volume or data or many columns that are hidden by default, this behavior can cause the grid to over fetch data.

To optimize performance, use a!recordData() to define the source of the grid. This allows you to write expressions in the fields parameter that strictly limit queries to only the data required for the current view.

Example

Let's say you configured a grid so that certain columns are hidden from managers.

Do use an if() statement in the fields parameter to specify when the fields in each column should be queried, so they aren't all queried when the interface initially loads.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
a!localVariables(
local!managers: a!isUserMemberOfGroup(loggedInUser, cons!MANAGER_GROUP),
a!gridField(
  data: a!recordData(
    recordType: recordType!Case,
    /* The grid will only retrieve one of these two sets of fields */
    fields: if(local!managers,
      { /* Fields for Managers */
        recordType!Case.fields.id,
        recordType!Case.fields.name,
        recordType!Case.fields.description,
        recordType!Case.fields.createdOn,
      },
      { /* Fields for Engineers */
        recordType!Case.fields.id,
        recordType!Case.fields.description,
        recordType!Case.fields.status,
        recordType!Case.fields.assignedTo,
        recordType!Case.fields.timeInQueue,
        recordType!Case.relationships.comment.fields.description,
        recordType!Case.fields.createdOn,
      }
  ),
  /* Only fetch the last 3 comments added to a case */
  a!relatedRecordData(
    relationship: recordType!Case.relationships.comment,
    sort: a!sortInfo(
      field: recordType!Case.relationships.comment.fields.createdOn,
      ascending: false
    ), 
    limit: 3
  ),
  columns: {
    a!gridColumn(
     label: "Id",
     value: fv!row[recordType!Case.fields.id],
    ),
    a!gridColumn(
     label: "Name",
     value: fv!row[recordType!Case.fields.name],
    ),
    a!gridColumn(
     label: "Description",
     value: fv!row[recordType!Case.fields.description],
    ),
    a!gridColumn(
     label: "createdOn",
     align: "END",
     value: fv!row[recordType!Case.fields.createdOn]
    ),
    /* The fields used in the following columns 
       may or may not be displayed. But by using the 
       'fields' parameter we ensure they are not queried if
       they are not needed.
    */
    a!gridColumn(
     label: "Status",
     value: fv!row[recordType!Case.fields.status],
     showWhen: not(local!managers)
    ),
    a!gridColumn(
     label: "Assigned To",
     value: fv!row[recordType!Case.fields.assignedTo],
     showWhen: not(local!managers)
    ),
    a!gridColumn(
     label: "Time in Queue",
     value: fv!row[recordType!Case.fields.timeInQueue],
     align: "END",
     showWhen: not(local!managers)
    ),
    a!gridColumn(
     label: "Comments",
     value: fv!row[recordType!Case.relationships.comment.fields.description],
     showWhen: not(local!managers)
    )
   }
  )
 )
)

Use rv!identifier instead of rv!record for record views with complex interfaces

Guideline

For complex record views that display a lot of data or conditionally hide components, use rv!identifier instead of rv!record in your record view configuration.

Importance

The rv!record and rv!identifier domains allow you to reference record values within a record type, like when you're configuring a record view or title.

When you use rv!record, Appian queries every field potentially required by the interface before the page loads. To optimize performance, use rv!identifier with a!queryRecordByIdentifier(). This approach lets you specify the exact fields to query so that Appian returns only the data necessary for the current state and conditional logic of your interface.

Use the following table to compare the domains:

Example

Let's say the summary view for your Order record type uses a complex interface. This interface displays large volumes of data from multiple record types and conditionally shows information depending on whether the customer used a discount code or purchased a certain item.

Do: Pass record data to this interface using rv!identifier and the a!queryRecordByIdentifier() function.

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Filter and sort unsynced record types

Guideline

When querying unsynced record types and their related data, prioritize filtering and sorting on the unsynced record type's fields instead of the related record type's fields.

Importance

Relationships are a powerful way to connect and display your data. However, when record types are unsynced, filtering or sorting on a related field requires Appian to retrieve and join all of the data for the selected fields from the source system before it can apply the specified filter or sort.

To keep your queries fast, use the identifier (foreign key) on the base record type to filter your data. This allows the source database to filter the records before they are sent to Appian, which significantly improves performance while still allowing you to display related information in your application.

Example

In this example, the Order record type (unsynced) has a relationship to the Region record type (unsynced). You want to return order details along with the name of the "Northeast" region.

Do use the foreign key on the base record type to filter the results.

1
2
3
4
5
6
7
8
9
10
a!queryRecordType(
  recordType: recordType!Order,
  filters: a!queryFilter(
    /* Filter on the base record's foreign key instead of the related record's name */
    field: recordType!Order.fields.regionId,
    operator: "=",
    value: 3 /* ID corresponding to "Northeast" */
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 20)
)

Don't filter on a related record field when you can filter by the corresponding identifier on the base record.

1
2
3
4
5
6
7
8
9
10
a!queryRecordType(
  recordType: recordType!Order,
  filters: a!queryFilter(
    /* This requires retrieving all orders and their related regions before filtering */
    field: recordType!Order.relationships.region.fields.name,
    operator: "=",
    value: "Northeast"
  ),
  pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 20)
)

Best practice checklist

Review your queries against this checklist to ensure optimal performance:

• Check Field Selection:
  • Are you only querying the specific fields you need?
  • Have you removed a!selectionFields() from production queries (unless strictly necessary)?
  • Are real-time custom record fields and Extra Long Text fields excluded from high-volume queries?
• Manage High-Volume Data:
  • Are sync filters configured to limit the data synced to only what your application needs?
• Verify Grid Logic:
  • For grids with conditional columns, are you using the fields parameter in a!recordData() to specify which fields to query and when?
• Review View and Action Configurations:
  • Are complex record views and related actions with dynamic start forms using rv!identifier to prevent over-fetching data?
• Check Unsynced Record Type Queries:
  • Are filters and sorts applied to the base record type rather than related record types?
  • For more complex filters and sorts, have you considered using a database view instead?

Feedback