Searching Calculated Fields and performance

April 3, 2007 at 8:59 am 3 comments

The other day I was asked whether it’s possible to search calculated fields. The simple answer is yes – as can easily be tested. The complete answer’s a little more complex: query the wrong calculated field and the application will hang, CPU resources off the scale. So how can you predict what calculations are going to cause you problems?

When you execute any query, Siebel converts the user’s logical instructions to SQL. It doesn’t always do an optimal job, but you can usually be sure that most of the effort will be pushed to the database server. The problem with calculated fields is that certain functions don’t have standard cross-platform SQL equivalents.

The IIf function, for example, will never be translated to native SQL. When your field calculation contains an IIf statement, Siebel will write SQL to return a superset of data. The Siebel object manager is then left with the job of paging through the results, evaluating the calculation for each row. Needless to say, if the incomplete SQL returns a large dataset, this ain’t going to be fast.In any particular example it’s worth eyeballing the SQL to confirm where the calculation is happening, but you can definitely expect problems with the following functions:

  • IIf
  • IfNull
  • ToChar
  • Right

Unfortunately, there’s no configuration way to prevent searching on an unpleasant field. If you’re running into problems then you’ll have to hit the PreQuery event with some scripting…

Advertisements

Entry filed under: Configuration, Siebel.

Changing the Local Database Password Using MVG aggregate functions

3 Comments

  • 1. SMLHP  |  February 15, 2008 at 2:51 pm

    How to Trim the value from calculated value in BC?
    now Left([Field 1]+[Field 2],70) provide the space after the value

  • 2. stuandgravy  |  February 16, 2008 at 3:26 pm

    I might be misunderstanding, but are you saying that (for instance) Left(“Fred”,10) returns “Fred “? If so then that’s worth knowing – I’ve never seen that behaviour.

    String manipulation in Calculated Fields is a bit limited (oh, for access to a RegExp function…) – so for anything vaguely sophisticated I’ll usually write a Business Service Method invoked through the InvokeServiceMethod function.

    There’s a blog in that – I’ll add it to the list 🙂

  • 3. kishore  |  June 25, 2009 at 2:23 am

    could pls provide me the blog details on calculated fields issue


Feeds


%d bloggers like this: