Effectively rewriting Siebel Predefined Queries for performance

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’s 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 ProfileAttributes 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).

PersonalizationProfile

The “green” onces are the interesting profile attributes. The white onces 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 unneccesary data. Let the database take care of this. And of course ensure an appropriate index exist for an efficient execution plan 🙂

 

Advertisements

5 thoughts on “Effectively rewriting Siebel Predefined Queries for performance

  1. Hi,

    Thank you for the interesting post! Could you kindly update the link to the xls? It seems like the link is dead and I’d like to take a peek at other examples:)

  2. Hi,
    very nice article, thank you for doing it and sharing it.
    I have some comments:

    1. In field “Month End Day”, you test whether the month has 31 days or 28 or 29. What about months with 30 days?
    2. I also notice that you choose to format the date in american format. Is this by choice or is it mandatory? (I’m thinking of using some of your formulas to improve Oracle TBO functionality).

    thank you.

    Matthieu

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s