Questions about Querying Data from an RDBMS

I think I will do some testing on this. I'm not sure I can accept that query entity performs faster than a query rule for the same number of columns and the same number of rows - especially given that (as I understand it) the access method (hibernate) is exactly the same.

Hi Phil, as per my understanding query rule will try to get all rows and will not apply the pagination, query entity accepts the pagination as a parameter and will fetch only the required batch size from DB, it follows internally Hibernates to form the paginated query and use it to fetch only the required data subset.

@narasimhaadityac where is your understanding from? It's concerning that multiple users have now said that query rules do not obey the pagination parameter. If you provie a pagingInfo parameter the number of results will be limited exactyle like queryEntity()

I've done a bit of testing on this and the results are quite interesting.

Attached is a file containing the SQL generated for a query that gets all entries for a specific data entity, using paging info with a start index of 1, a batch size of 5 and a sort on ID ascending. The first set of SQL is that generated for a Query Rule, and the second is that generated for a Query Entity.

In this instance, the Query Rule method generates one extra SQL statement than the Query Entity - 3 statements vs 2. It seems that the Query Rule first obtains a count, then gets the primary keys that it's going to retrieve, then finally selects the data using those keys. The Query Entity does exactly the same query to retrieve the count, but then gets the data and applies the limit in a single statement.

There is, of course, arguably a performance benefit in not doing the second statement, but in this instance it would appear to be negligible; running the second statement directly in MySQL Workbench shows a query time of 0.000 seconds, so there's not even a millisecond of performance increase on the SQL side for this example.

In my opinion the performance benefits displayed don't justify the significantly longer development time required for a Query Entity method - but maybe there are limitations sitting above the MySQL level that cause further delays.

For me, the main observation from this testing is that the Query Rule applies the LIMIT 5; filter in (almost) exactly the same way as Query Entity, and so does not return all the data as suggested above.

Obviously my example is a simple one, but I think it evidences that the differences between the two methods aren't that great for this case. No doubt Appian will be able to provide some more detailed analysis; it would be good to know how complex a query needs to be occurring to realise the potential performance benefits.

QR_vs_QE.sql

I should add - my tests were done on a 16.1 machine running MySQL 5.6.19, all running on Ubuntu server.

@Tim, got it. I had a look at the documentation from and found the usage of the query rules with pagination. https://forum.appian.com/suite/help/7.8/Rules.html#Query_Rule_Paging_Parameter

unfortunately this link is not available in all the versions of the documentation.

@philb,@tim,@narasimhaadityac,@sparshs Thank you so much for responding on this but still in the confusion. As suggested by philb in the example it says there is not much difference making with limited data but my report is fetching more than 100K of data so not sure how this would work at that time, also let me explain you how am I querying the data.
1.This is basically written for Report.
2.Rule contains a choose in load function ,based on some selection each rule is called in choose function lets say rule!xyz(parameters,paginginfor(1,25)). now when this is called my page is taking so much time to load. So in this case could I go for query entity or is there any way I can make my page load. Last but not the least, from the comments from philb, "No doubt Appian will be able to provide some more detailed analysis" could someone from Appian give us more detailed Analysis it would be great ..... Once again Thank you so much guys :) !!!

I would suggest you test this with a sample table; it's not too much effort to add a table with 100k+ entries and a data store entity - you could then run both query entity and query rules against the table and see how it performs. One thing I would say is if you're actually pulling in 100,000 records, then you're very likely to hit the 1Mb query limit regardless of method.

To add briefly to the performance testing, I just ran a quick test from the Appian GUI on a basic queryEntity vs Query Rule, on 15 paged records over a DB containing 2.4 million records.

a!queryEntity() - 704 ms

720
762
665
599
774
694
716
725
758
625

Query Rule - 687 ms

838
672
697
720
619
561
790
637
672
660

Over 10 tests the QR wins out by 17 ms.

load(
local!pagingInfo: a!pagingInfo(1,15),
/* 704 ms avg
local!data: a!queryEntity(
entity: cons!ENTITY_TASK_HISTORY,
query: a!query(
pagingInfo: local!pagingInfo
)
),
*/
/* 687 avg */
local!data: rule!chrisTest_qrTaskHistory(local!pagingInfo),

a!textField(
readOnly: true,
value: count(local!data.data)
)
)

Hello, I have similar problem. We have a view which is and it is trying to fetch the data with pagination with 50 records per page. We are using Query rules and we are in version 7.4. But the problem is only first 50 records are displayed, but not all the records. As per my understanding if the total records in DB are 100, Batch Size is 50, then 2 hits are made to DB. Initial batch 50 anf next batch another 50. But in my case only initial 50 records are displayed. Can someone please explain how to retrieve data with batchSize?