Questions about Querying Data from an RDBMS

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'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