Effectively Rewriting Siebel Predefined Queries for Performance
The following is a cross-post from my co-worker Jeroen Burgers, who shares his experiences as an Oracle Implementation Advisor on his blog with the same name. In one of his recent articles, Jeroen picked up the topic of Siebel Predefined Queries (PDQ) and the impact they can have on performance. I am pleased that he agreed to publish his findings on Siebel Essentials.
Ever had to deal with PDQs which required to fetch based on date functions such as Year-to-date or Month-to-date (or any related�)?
I came across an implementation where a customer became very creative trying to resolve this. But the end-result was a terrible performance. Why? Because the PDQ could not be completely be executed as SQL.
A generic implementation flaw in queries written by Siebel configurators or business analysts: misusing calculated fields to be used in e.g. search Expressions and PDQs. It can (or will) hammer performance. A lot.
For example:
Provide me all the Opportunities YTD. This was the original PDQ:
"[Due Date] <= Today() AND JulianYear(Today())=JulianYear([Due Date])"
It does the job. But the SQL WHERE clause would only include the [Due Date] <= Today() clause. Assume you have some 15 years of Opportunities. It would fetch all records. Only in-memory the object manager would be able to further filter based on the condition JulianYear(Today())=JulianYear([Due Date]). You can imagine how resource-extensive this would be. Not to imagine the end-user performance perceived.
Similar constructions for Month-to-date and Quarter-to-date queries.
How to circumvent this?
Goal would be to have Profile Attributes available throughout the application which would carry values such as:
To realize this you can easily configure a number of fields on the �Personalization Profile� business components. The nice feature of this business component is that all fields are loaded for every session immediately after login. And those fields - well - become Profile Attributes. Typically the �Personalization Profile� business component consist out fields which can be joined toward the Party record for the user logging in (can be an Employee, but can be also a Portal user). But you can also create Calculated Fields. And that will be of great help. Consider the Calculated Fields below (you can grab the complete.xls here).
The �green� ones are the interesting profile attributes. The white ones are just supporting field to make the calculated fields somewhat readable.
Now let�s rewrite the PDQ from the example.
"[Due Date] <= Today() AND JulianYear(Today())=JulianYear([Due Date])"
The optimized version would become:
[Due Date] > GetProfileAttr("Year Start") AND [Due Date]) < Today()
The optimized PDQ would translate completely into a more enjoyable SQL WHERE clause. It will no longer have to fetch unnecessary data. Let the database take care of this. And of course ensure an appropriate index exist for an efficient execution plan :-)
This article was originally published on the Oracle Implementation Advisor blog by Jeroen Burgers.
have a nice day
@lex
***
Ever had to deal with PDQs which required to fetch based on date functions such as Year-to-date or Month-to-date (or any related�)?
I came across an implementation where a customer became very creative trying to resolve this. But the end-result was a terrible performance. Why? Because the PDQ could not be completely be executed as SQL.
A generic implementation flaw in queries written by Siebel configurators or business analysts: misusing calculated fields to be used in e.g. search Expressions and PDQs. It can (or will) hammer performance. A lot.
For example:
Provide me all the Opportunities YTD. This was the original PDQ:
"[Due Date] <= Today() AND JulianYear(Today())=JulianYear([Due Date])"
It does the job. But the SQL WHERE clause would only include the [Due Date] <= Today() clause. Assume you have some 15 years of Opportunities. It would fetch all records. Only in-memory the object manager would be able to further filter based on the condition JulianYear(Today())=JulianYear([Due Date]). You can imagine how resource-extensive this would be. Not to imagine the end-user performance perceived.
Similar constructions for Month-to-date and Quarter-to-date queries.
How to circumvent this?
Goal would be to have Profile Attributes available throughout the application which would carry values such as:
- 1st day of the year - "01/01/2014"
- last day of the year - "31/12/2014"
- 1st day of the month: - "01/08/2014"
- last day of the month: - "31/08/2014"
- Well, you get the point.
To realize this you can easily configure a number of fields on the �Personalization Profile� business components. The nice feature of this business component is that all fields are loaded for every session immediately after login. And those fields - well - become Profile Attributes. Typically the �Personalization Profile� business component consist out fields which can be joined toward the Party record for the user logging in (can be an Employee, but can be also a Portal user). But you can also create Calculated Fields. And that will be of great help. Consider the Calculated Fields below (you can grab the complete.xls here).
 |
| Click to enlarge. |
The �green� ones are the interesting profile attributes. The white ones are just supporting field to make the calculated fields somewhat readable.
Now let�s rewrite the PDQ from the example.
"[Due Date] <= Today() AND JulianYear(Today())=JulianYear([Due Date])"
The optimized version would become:
[Due Date] > GetProfileAttr("Year Start") AND [Due Date]) < Today()
The optimized PDQ would translate completely into a more enjoyable SQL WHERE clause. It will no longer have to fetch unnecessary data. Let the database take care of this. And of course ensure an appropriate index exist for an efficient execution plan :-)
This article was originally published on the Oracle Implementation Advisor blog by Jeroen Burgers.
***
have a nice day
@lex
תגובות
הוסף רשומת תגובה