Some Thoughts about Queries
Hello,
I am not sure if everybody understands the concept of the different queries which are supported in PDI and when to use which.
Let me shed some light on this.
Three Kinds of Queries
PDI is supporting three different kinds of queries.
- QueryByElement
- I am quite sure everybody knows these queries which are generated for every node by the framework automagically.
- These queries support only the elements of the own node, for the selection parameter as well as for the result.
- As they access direct the database table there is no optimization for this query access.
This means a shot execution can only happen if the key fields are part of the selection conditions. These are- Node ID (which you will probably never use in a search)
- Alternative Key annotated elements.
- Furthermore this is only true if the operator for the selection is the equal comparison. 😯
- This makes theses queries not the preferred ones if your selection criteria do not match these conditions.
Especially if there are many entries in the database table. - If you get more than 1000 hits you will see a message in the UI which informs you that you are using the wrong type of query for such a big result list.
- QueryBuilder
- You define a (technical name) Query Response Transformation Node (QRTN) which can make use of any element which can be reached from the node to this query is attached. 😎
- The implementation is based on the Fast Search Infrastructure (FSI) so you can expect
- A fast execution
- Support of any comparison operator
- This query will give you a quick access to the search data in many places.
- SADL based Queries
- These queries are defined direct in the UI (typically OWLs) and grasp direct to the data avoiding any framework overload.
- This access is not only dedicated for key access but also for query, so you can also expect
- A fast execution
- Support of any comparison operator
- As there is no further propagation of the query definition it can only be used in the the UI in which it is defined. 😕
No reuse.
Trigger a query
Imagine where you want to trigger a query:
- Studio (SDK)
- Just right-click on the BO and choose “Execute Query” and you can choose any query from any node, either the generated QueryByElement or the ones from the QueryBuilder.
- You can provide all the selection criteria, follow in the result list the available associations.
- It is even possible to generate the resp. ABSL statement which can be copied to your code snippet
- Due to security reasons this is not availabe in a production tenant! ❗
- Coding (ABSL)
- If the SDK generates code for you it is clear that you can use it anywhere in any ABSL code snippet.
- But also the QueryBuilder queries are available.
- But please follow the “SAP Cloud Application Studio Performance Best Practices“:
- First the query and later the modify part.
- Do not mixed that. ❗
- If you do not povide any selection criteria you will get a warning message in the ABSL editor. ℹ
- User Interface
- All kind of queries can be used here
- For filling the selection parameters you can even use Transformation Definitions.
- This can be standard ones as well as Customer Reuse Library functions which are marked to be used by the UI Designer.
I hope your decision about “Which query shall I use for my case?” can now be answered more easily.
That’s all, folks,
Horst
Dear Horst,
can you please tell me how to create "Query Response Transformation Node" on a standard BO like ServiceRequest?
Best Regards,
Florian
Hello Florian,
I am sorry, but you can add queries only to Custom Business Objects, but not to SAP Business Objects. 🙁
Sorry,
. Horst
Dear Horst,
we are currently try to use a SADL Query to show a list of more than 10k datapoints, which, however, should still be editable. A normal query doesn't show more thant 10k elements and is very slow in filtering and sorting. Thus, we tried a SADL Query which is amazingly fast, but it doesn't not allow to edit the data in the UI. How can we make elements of a list, which is bound to a result of a SADL query, editable?
Best regards,
Thorsten
Hello Thorsten,
The SADL Query is fast, but (or because) it returns only data. It has no knowledge about the resp. Business Object nodes.
Therefore it is not possible to direct maintain the data.
I would suggest, that you return also the node ID and re-reads the data (aka node instance) via a BO operation. This data set should be editable and can be saved afterwards.
HTH,
. Horst
Hello Horst,
thanks for the fast answer! That already helps, however, I did not understand exactly how we should implement this "read".
So the use case is, we have a list of >10k sub-node objects and when I press edit in the TI (where the list is included), they all should be editable. Or is there only a way to set just specific elements into edit mode e.g. via a button, which executes a BO Operation in the table row?
Best regards,
Thorsten
Hello Thorsten,
I would enable a column of the list to open on click a modification popup.
The Modif should read the data via the BO operation and provide the Save option.
HTH,
. Horst
Hello Horst,
thanks. We will check that option and will talk to the users, whether that is ok.
Too bad, that on the one hand, normal queries cannot load >10k data and are very slow and on the other hand, a normal simple "binding" of the BO sub-node to a list in the BO TI is not pre-filterable (or is it?), e.g. pre-filter the sub-nodes on a status field, while with this normal binding, all data can be shown, filtered, sorted and edited incredibly fast. Thus, a SADL Query would be the option we need to pre-filter it, however, this type of query doesn't allow to edit data. I know, special usecase, but still.
Hello Thorsten,
As this is inside a BO don't you have any filtered association to this sub node?
Bye,
. Horst
Hello Horst,
I'm not sure how we can filter an assoziation.
Here our use-case in detail:
We have a BO and a Subnode inside this BO, e.g.,
businessobject TestBO {
node ZCustomer [0,n] {
element ZERPStatus : Z_CustomerStatusCode;
}
}
When we bind the TestBO to the Root of the UI and create a datalist and bind this list to ZCustomer, we can use this datalist and bind it to a advancedListPane, but then we get ALL entries in ZCustomer. So we are looking for an option, to pre-filter this list on the ZERPStatus == 'A'.
Picture showing the Datamodel in the UI-Designer
The question is: How can we realize such a "pre-filtering" without a query, when we just bind the BO and Sub-Node-Assoziation to the datamodel in the UI-Designer. Of cause, we can tell our users to always filter by hand in the UI via column filter before they start to work, however, this would be annoying for them why we are searching for a better solution.
I hope you now have a better understanding, what we try to achieve. And many thanks for your time and help!
Best regards,
Thorsten Schodde
Dear Horst,
we just saw the "valuation" option and will test it. Thanks for the hint!
Best regards,
Thorsten Schodde
Hello Thorsten,
Exact. 🙂
Define at the Root node an association to ZCustomer with a valuation like
valuation ( ZERPStatus == Z_CustomerStatusCode=>CO_A )
I am not sure about the exact syntax as I have left PDI some years ago.
Bye,
. Horst
Hello Horst,
many thanks it worked! However, somehow on the DEV Tenent it works very fast to filter the resulting list via column filters and search for a specific name, on the Test-Tenant aber the deployment last night, it's super slow. That doesn't make much sense for me. But probably you also have no idea how this can happen?
Thanks in advance!
Best regards,
Thorsten
Hello Thorsten,
Glad that my "old" knowledge is not an outdated one. 🙂
Reg. deployment: maybe some nightly runs slowed down the system.
But that's only a guess.
Bye,
. Horst