October 09, 2015

Searching Calculated Fields and performance



 Originally posted on blog.notesonsiebel.com on April 3rd, 2007

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…

No comments :

Post a Comment