When a query rule is slow, I approach it systematically by checking rule dependencies, data volume, query design and indexing.

Check Rule Dependencies First

Before optimizing the query itself, I analyze where and how the rule is used.

Actions:

1. Identify dependent interfaces, expressions, and process models
2. Confirm what data is actually required by the consumer
3. Avoid over-fetching data just because the query rule was reused

If the UI or process needs only a single value or record, I adjust the query accordingly instead of returning a full dataset.

Control Data Volume with Pagination

Fetching unnecessary rows is one of the biggest performance killers.

Actions:

1. Use pagination in all queries
2. Never use batchSize: -1 unless absolutely required
3. Limit batch size based on actual usage

Example (Fetch only one record):

This ensures the database returns only one row, not the entire table.

Fetch Only Required Columns

Many slow queries fetch all columns even when only a few are needed.

Actions:

1. Use rule inputs to decide required fields
2. Pass selection columns as rule inputsUse selectionFields to fetch minimal data

Example:

This makes the rule reusable and optimized across multiple use cases.

Adding Indexing

If I observed that the performance issue was mainly caused by frequent filtering on a specific column. For example username

Adding an index to the frequently used column can significantly improve query performance.

Sample code for adding Index

If Data source is a VIEW

I will follow same the above steps for view. In additional, I will follow this below steps as well

Optimize Query Design

1. Remove unnecessary filters and joins
2. Avoid complex calculations inside sub queries
3. Push heavy logic to the database layer when possible
   

Optimize Joins

1. Remove joins that are not required by the UI
2. Convert LEFT JOIN to INNER JOIN when business rules allow
3. Add indexes on join columns

In summary, I first check rule dependencies to understand actual data usage. I then optimize the queryEntity—whether it queries a table or a database view—by limiting rows using pagination, fetching only required columns, simplifying joins, and ensuring proper indexing on underlying tables.