Below are the best practices, I follow.

1. Optimize Data Retrieval (Fields & Paging)

Parameterize Selection Fields: Always pass fields as a rule input (List of Text/Record Field). Explicitly selecting only required fields is significantly faster than querying all fields, especially with Data Sync and large record types.
Manage pagingInfo Dynamically: Pass pagingInfo as a parameter for flexibility. For heavy grids, keep the batch size small (e.g., 20–50).
Avoid Unbounded Queries: Never use a batch size of -1 (return all) unless the dataset is guaranteed to be extremely small and static. Unbounded queries are a primary cause of high memory consumption and interface timeouts.

2. Control Total Count Overhead

Toggle fetchTotalCount via Parameter: Make fetchTotalCount a Boolean rule input. This allows the calling interface to skip the "count" query when it isn't needed (e.g., in a dropdown or "Load More" scenario).
Disable Total Count for Data-Only Returns: Set fetchTotalCount: false when only the record data is required. This prevents Appian from running a second, often expensive, hidden query to count the entire filtered result set.

3. Minimize Database Hits

Pre-Query Null Checks: Wrap your query in an if() statement or a!localVariables() to check if required rule inputs are null. If no valid filter values are provided, return an empty datasubset immediately to avoid an unnecessary database hit.
Modernize Join Strategy: While creating a Database View was traditional, in 2026 you should first evaluate using Record Type Relationships. This utilizes Appian's internal Data Fabric to join tables more efficiently than many standard views, while still minimizing hits via a single query.
Use Views for Complexity: Only default to a database view if you require complex aggregations or heavy data transformation that the Data Fabric cannot handle natively.

4. Efficient Filtering

Filter at Source: Ensure filtering happens within the a!queryRecordType() or a!queryEntity() call rather than using index() or where() on the returned data. Database-level filtering is always more performant than Appian-side filtering.
Index Filtered Columns: Ensure every field used as a filter in your query rule is indexed in the underlying database to prevent full table scans.