Siebel Unleashed

Sort Search Optimization is a Field level User Property. As I had mentioned in earlier post

“An optimization that helps some databases (mostly Oracle) choose the correct index when there are multiple candidate indexes with the leading edge”

As I mentioned in my earlier post that it is up to Siebel Object Manager (OM) to decide weather to use it or not even if you specify Sort Search Optimization for a field.

That means we can use this user property in two ways

• Apply Optimization
• Disable Optimization

Apply Optimization:

To optimize a query we can specify this user property on a field and provide dummy condition that will hopefully force database to use particular index and optimize the query. It is not necessary that it will be applied all the time.

Disable Optimization:

If we feel that optimization being dynamically added by Siebel OM is cause of performance degradation then we can use this User Property and force Siebel not to use dynamic optimization for that field.

To disable the dynamic optimization added by OM we specify this user property and value as FALSE on the field for which it is being apply by OM. Follow the steps given below to apply this user property. In the below example we will apply this user property on Last Name field for contact BC

• Query for contact BC
• Query for Last Name Field
• Click on + sign for Field Object and select Field User property object as shown below
• If you are not able to see + sign against Field object read my post telling how to enable it.
• Create a new Record Field User property list applet
• Provide the following values to it
  Name: Sort Search Optimization
  Value: FALSE

In our performance issue Sort Search Optimization was being applied for CREATED column so to disable it I.

• Created a field in Action BC based on CREATED column
• Applied the user Sort Search Optimization user property with values as FALSE.
• Compiled the SRF

After the changes condition was no longer part of My Activities SQL and the performance of the query came with in acceptable limits. I will discuss second perfomance issue in next post.