Query Rule vs a!queryEntity

Take a look at docs.appian.com/.../Querying_Data_From_an_RDBMS.html

Thank you, this helps!

After some significant server issues a few months ago we were able to get someone in Appian tech support to talk to us who told us about a problem with query rules (and the unclear documentation that hints at this). Supposedly (and this I have not yet confirmed from other sources or from log files) when you use a query rule and specify a condition in it, say primary key = 25, Appian actually calls the database and does NOT filter on the where clause... the database returns the ENTIRE set of data in the table to Appian. Appian then filters the data before giving the smaller result set back to the process/rules.

IF this is true, which I have not yet confirmed for myself, then a query rule to a table of say, 25 rows of data will retrieve all 25 rows when you may have only wanted 1 row, or say, 5 rows. Not a big deal. However, if your data set grows and the table contains 300,000 rows of data, your query rule would retrieve ALL 300k from the database and then have to parse through this internally in memory before returning the 1 or 5 rows that you wanted. If your application is made up of a lot of query rules or a lot of users who are pulling data, you could potentially be returning a tremendous amount of data to the Appian system unintentionally (and thereby using large amounts of RAM, CPU and time)...the bigger your tables grow the worse the performance would be, even if your query rules should only be returning a few rows at a time.

Assuming this is true:
Query Rules - all columns in a CDT retrieved (equivalent to a SELECT *), all rows in the table retrieved (network bandwidth), large processing necessary in Appian to filter the data, no ability to nest complex AND & OR statements together or additional if/then type logic based on inputs

Query Entity - a bit more coding necessary to create and maintain, probably a bit more overhead, but the specific columns you want retrieved and a lot less data parsed by Appian since only the records you intend to be retrieved via nested and/or type 'where' clauses would be used

My team is slowly replacing the query rules we are using when possible with query entities. Starting with rules that are called frequently and/or are pulling from tables with a lot of data. We are slowly creating entities and replacing the rule!x() calls with similar calls to the entities. Sadly, because some of our apps are quite large & old this requires a lot of testing. Your normal logic may handle empty datasets from a rule differently than the data coming from an entity (null data versus empty CDTs, pagingInfo differences, etc.). Not an easy/fast transition to code & test for.

It seems really lame for Appian's query rules to NOT filter on a condition at the database level like we would expect it to do so. I am not convinced that this is truly the case, so take what I am saying with a grain of salt. If you are concerned this may be true, turn on a bunch of the Appian database debugging logs and the logs in your database engine and test it enough to confirm if this is true or not.

Assuming this is true, query rules shouldn't be a big deal on moderately small tables with little change in the data because of caching and performance. Who cares if Appian parses through 50 rows of US States & Abbreviations instead of the database....but querying tables with a million rows, thousands of times a day would be an issue.