What is the maximum limit to retrieve data through Query Entity in Appian? If the limit exceeds what will happens?

By default appian allows 1MB (1,048,576 bytes) data to retrieve the database through queryEntity(). If try to retrieve data more than 1 MB Appian throws an memory threshold error.

Say you have a database table called Customer with 20,000 records. Each record holds a customer ID, name, address, description, and other fields.If you use a!queryEntity() to pull a large number of those records in one call, the combined result can go over Appian’s memory threshold for query results.

1. Here batchSize: -1 asks for every available row. If the data that comes back is too large, Appian can throw a memory threshold error.
2. Take 5,000 records that average 300 bytes each.The raw data alone comes to about 1.5 MB, before any of the extra memory Appian uses to represent and process it.
3. The database itself doesn’t necessarily have a 1 MB limit. The problem is how much data Appian has to receive and process.

How to avoid it:

1. Use pagination , Retrieving all records at once can use too much memory. Page the results instead:

This returns at most 100 records per query instead of the whole table.

2. Select only the columns you need

Unnecessary columns, especially large text fields, make the result bigger. Select only the ones you need:

This returns only the customer ID and name instead of every column.

3. Filter records in the query

Retrieving all customers and filtering them afterward wastes memory. Filter in the query instead:

This returns only active customers, and no more than 100 of them.

4. Avoid batchSize: -1

A batch size of -1 asks for every row:

Use a fixed batch size instead:

Each query then returns a manageable number of records. If the business needs all of them, fetch them page by page, not as one huge result.

5. Use database-side aggregation

Don’t retrieve thousands of records just to count them. Use a database query or stored procedure that returns the count directly:

Call it through an appropriate Appian database integration or data access mechanism. Appian then receives one aggregate result instead of thousands of rows.

6. Process large datasets in batches

Say a process has to work through 10,000 records. Loading them all into one process variable risks using too much memory. Work through them in batches:

The next query then uses:

After that come startIndex: 201, 301, and so on. Only a small batch is held and processed at a time. For large production workloads, consider a process model or another controlled batch-processing mechanism.

a!queryRecordType():

Appian also provides a!queryRecordType() for retrieving data through record types. a!queryEntity() queries a Data Store Entity directly. a!queryRecordType() queries an Appian record type, and you choose the fields, filters, and paging information you need.

Say the Customer table has 20,000 records. Retrieving all of them in one operation can use a lot of memory. With a!queryRecordType(), you can retrieve a smaller batch and return only the fields you need:

This query retrieves up to 100 customer records and returns only the customer ID and name.

Why does Appian limit the batch size to 5,000 as limit?

a!queryRecordType() supports a maximum batch size of 5,000 records per query in the applicable context. The limit keeps down the number of records returned in a single request and helps manage resource use. It is a row-count limit, not a 1 MB data-size limit.

Does a!queryRecordType() completely eliminate memory-threshold errors?

1. No. Pagination and field selection let you control the size of the query result, but they don’t guarantee that memory-threshold errors never happen. Large fields, complex data, and downstream processing can still use a lot of memory.
2. If you need all 20,000 customer records, you can retrieve them in four batches of 5,000, as long as the record sizes and processing needs make that batch size appropriate. With larger records, smaller batches are safer.
3. To stay under the threshold, select only the fields you need, apply filters, pick a suitable batch size, and process large datasets in manageable batches.

Can the 1 MB limit be increased?

Yes. Appian’s documentation for the “Memory threshold reached during output conversion” error names a property in custom.properties that sets the limit:

The default is 1,048,576 bytes (1 MiB). You can raise it, but Appian warns that a higher limit increases heap usage on the application server and can cause performance problems. It’s usually better to optimize the query first.

For a!queryRecordType(), the documented batch size range is 0 to 5,000, and -1 isn’t allowed. That is a separate limit from the query memory threshold.