Questions about Querying Data from an RDBMS

...t !!! :)

adding to it, since we are in 7.4 we are using Query rules and the question is related just to understand the difference.

1. Query rule fetches all records in one go and it only shows 25 per page.
2. Query rule is different than query entity , query entity has good performance than query rule and in query entity you can do things which you cannot do in query rule like query rule does not support OR operator in its filter criteria.
3. Same for both.
4.Performance is effected because in query entity you can limit your data selection very well but in query rule you cannot limit your selection e.g if you just want to fetch a single column from your database for particular filter , you can do that in query entity but not in query rule. Plus as i mentioned earlier also appian recommends use of query entity over query rule.

hope this answers your concerns, if you want more detailed explanation , please let me know.

@sharshs - A few comments:

re 1. Do you have metrics to back this up? What you're implying is that a query rule totally ignores the pagingInfo that you give it; I don't believe this is the case.
re 2. When you say "query entity has good performance than query rule" are you saying that query entity is ALWAYS faster? I don't believe this is correct. A comparison of a query rule returning all 100 fields of an entity and a query entity returning only 5 of those fields will, of course, show a performance increase, but I'd suggest that the difference is negligible for similar numbers of fields.
re 4. This simply isn't true; adding an input to a query rule and filtering by that input will limit the results. The performance benefit of query entity is shown when selecting subsets of columns for a given data entity.

I'd be interested in some input from Appian on this; the recommendation of "appian recommends use of query entity over query rule" seems far too broad and doesn't take into account the extra effort an maintenance of query entity over a query rule.

philb: i am not implying that query rule ignores the paging given to it, but what i state here is that if we are fetching all records suppose 100 from our database and we want to show it as 10 per page , in this case query rule will fetch all 100 and but on our grid we are showing 10 per page. Secondly, when i say that performance wise query entity is better i say it because in query entity you can limit the selection and also you can have OR operator in query entity which we do not get there in query rule. I hope i am making sense here.

Please correct me , if you think i am wrong here. Thanks in advance.

Just to add - some of this is documented here - forum.appian.com/.../Database_Performance_Best_Practices.html - but "[Use] a query rule against a data store entity when only a subset of the data is needed will still return all the data" is still far too broad; in most use cases this will remove a small percentage of the columns involved and produce a negligible performance increase for a higher development cost.

@sparshs Have you tested that by turning on SQL trace and viewing the queries that are generated by hibernate? I don't see why it would work that way - fair enough if it does, but I'd be very surprised.

Agreed you can limit the selection in query entity but what are the actual performance benefits? 21ms vs 20ms "performs better" but is ultimately a very poor return on the inevitably increased development time. The "OR" option is, of course, a useful function but is not a performance increase.

i agree with you on this but i my previous project we were facing issue like form taking long to load , so we replaced all query rules with query entity's and it kind of decreased overall laging time for a form to load and overall process execution time.Secondly if database table has columns around 10 or above then i think query entity will be more beneficial specially when we are fetching lot of records.