Siebel Configuration Best Practices : Part 6

Siebel best practices for Search Expressions, Specifications and Sort Specifications.

1. When you are configuring the Search Specification property of a BC you have to consider the following:

• Try creating search specification which is based on columns on the main table of the Business Component. If the Business Component is Contact (based on table S_CONTACT) if you need to modify the search specification, then try to use the fields that are based on table S_CONTACT. The problem when you include joined fields in the search specification is that indexes cannot be created across multiple tables so introducing joined fields to the search specification will result in poor performance due to lack of index on the joined field. 
• Try avoiding the use of Calculated Fields in the search specification of a Business Component. The Calculated Field usually results in an additional query being executed which can have a big performance impact. 
• The Search Specification property of a Business Component is very crucial as its filter will always be used (and hence the resultant SQL will always use this filter) whenever the Business Component is instantiated. If a poor search specification is configured on a Business Component, then a poor SQL will be run any time the BC is instantiated. The nature of RDBMS databases is that it is possible for an entire database to have performance degraded by a single bad performing Query. Hence, it is very important to be configuring search specifications as optimal as possible. 

2. You have to perform regular analysis on usage of the Siebel system in production. Try to analyze the Siebel log files or SARM to get this information. Get to know common queries that users are executing and the queries that are leading to performance issues. If a field exists in the UI and users can query on that field then that field will probably require indexing.

Therefore, by setting the Siebel log levels on the Application Object Manager component as cited below will give you much needed information about where the users are navigating to and what queries they are performing:

Event Context = 4
SQL Object Manager Log = 4

Poor performance usually comes down to bad scripting or poor performing SQL queries due to use of poor indexes.

3. Sort Specifications: If we allow Sort in Applet List Columns in the UI, it can be a performance nightmare in Siebel applications. Again, if you alter the Sort Specification on a Business Component, there is a good chance you are going to get performance issues.

In Siebel CRM, the FIRST_ROWS_10 database session parameter will allow the database to efficiently return the first 10 records in the dataset, if a sort specification is included the entire SQL result set will need to be sorted before the result set is returned. This will be avoided if the SQL query avoids the sort operation in its SQL query plan through the use of an index on that sort. However, we have seen in Siebel UI the sort will return fast in one view while on the other hand it will be very slow in other views due to the nature of the SQL being different in different views.

Hence, if we allow Applet List Columns to be sortable, it can be a huge problem for the same reasons as above, a full sort will occur unless an index is used that can cover the sort.

Related Posts