WebServices Performance Tuning

Recently I have been working on high traffic and highly available web service which is hosted by Siebel. I was asked to improve performance of it. I checked all the places for any performance degradation clues, there was hardly any, all queries were on indexed columns and thin BC's were used, there was no fat whatsoever.

With all the logs and spool generation my focus went to these two problems. 

1. Before every web service call Siebel was requesting file system to write read user preference file which was taking fair bit of time due to contention at file system. You might see following in logs, if user preference file is corrupted.

ObjMgrLog Debug 5 0 2016-06-29 06:50:56 SharedFileReader: \SiebelFS\userpref\EAIUSER&Siebel Sales Enterprise.spf: Open iteration 0 failed. errcode = 2.

This file read was absolutely unnecessary, luckily there was a way to turn off by setting "OM - Save Preferences" to FALSE for EAI object manager. This will make all logins to avoid looking up for user preference file.

2. Another useless transaction I noticed was with database. It was when Siebel tries to update the last login date on S_USER record. 

In logs you can see queries like these:

UPDATE SIEBEL.S_USER SET
LAST_LOGIN_TS = :7,
MODIFICATION_NUM = :8,
LAST_UPD_BY = :9,
LAST_UPD = :10,
DB_LAST_UPD = current_date,
DB_LAST_UPD_SRC = :11
WHERE
ROW_ID = :12 AND MODIFICATION_NUM = :14;

This can also be turned off in recent Siebel versions by setting DisableLastLoginTS = TRUE 

Its a new parameter introduced by Oracle, you can read more about this parameter on oracle support 1665762.1

Hope it helps. If you have come across any such parameter which can improve performance then please share it in comments below.

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…