The following guide focuses on searches for 1Order documents. But there is a more general rule which is true for all cases when you want to implement fast searches.
Rule: If you implement a search think in terms of SQL. Do not think in terms of ABAP. SQL is the perfect tool for implementing searches.
Introducing new search parameter
When enhancing SAP standard searches and new search parameters are added there is usually the problem that the SQL query for the search is within the SAP standard code and cannot be changed without replacing the whole search logic. What is usually done instead is that filter logic programmed in ABAP is added after the standard search. This has a very negative effect on the performance especially when only the new custom search parameter makes the search selective. The reason is that too many records are read from the database by the standard search only to be filtered out later inside the custom code. On the other hand the approach of doing a custom search first and then passing a list of documents to the standard search becomes a problem when the hit list of the custom search has many items. Often only the combination of the standard search fields and the custom fields are selective so both approaches of preselecting and filtering do not work very well. Another problem when a search is distributed in such a way between a custom search and a standard search is that a maximal hit list parameter which is given cannot be made use of effectively. The reason is that the first part of the search can never know how many rows of the result list will be filtered out by the second part. This means to be on the save side the first part of the search has to determine the complete result set. (One way which the CRM business partner search uses which helps to mitigate the problem somewhat but which is far from ideal is an iterative approach where the select and the filtering is done in packages. The select always determines a fixed amount of say 300 results. The filter is applied to this set and the procedure is repeated until all records are processed or the max hits parameter is reached.)
For the 1Order reporting framework there is another powerful possibility which is described here for which the SAP standard code does not need to be touched. The 1Order is the framework for all business transactions in CRM such as activities, sales orders, service orders, service contracts etc. Standard searches for business transactions such as the Web UI searches make use of the so called reporting framework. The reporting framework is a tool which converts a query given as a list of search parameters with values into a dynamical SQL statement. The dynamical SQL is generated based on the content of tables CRMC_REPDY_DB and CRMC_REPDY. By adding entries to these tables new fields can be enabled for searching. If you are using the AET tool to create new fields the search will be automatically extended if you chose this option within the AET tool. But if you have already created this field before manually or with EEW this does not help. Under certain circumstances it may even be required to change entries to improve the performance of the search. But let us go step by step.
The most important table for 1Order searches is table CRMD_ORDER_INDEX. It contains (or should contain) the most frequently used search fields. It is explicitly allowed that customers enhance this table if there is a need to. Table CRMD_ORDER_INDEX contains index entries both for header and items. In particular table CRMD_ORDER_INDEX allows for searches based on partner information and on product information and based on combinations of those. The content of this table is completely redundant and can be regenerated at any time using report CRM_INDEX_REBUILD. The generation can take several hours. For this reason you should run the report in advance before transporting the changes to the search logic to production.
Since table CRMD_ORDER_INDEX does not contain all relevant search fields it needs to be joined with other tables for many use cases. Usually the join will be with the various 1Order tables such as CRMD_ORDERADM_H (Order header), CRMD_ORDERADM_I (Order item), CRMD_CUSTOMER_H (customer fields on header level) etc. Check the content of table CRMC_REPDY_DB to see which table the reporting framework can already join. In many cases the join will be with just one table (column NR_DB_TABLES) and the table name can be found in column LEADING_TABLE. But there may be also more complex joins with two or more tables and the join conditions are written in fields FROM IF NOT LEAD and REMAIN_FROM. The entries in table CRMC_REPDY_DB are a bit more complicated. But for most cases the entries are already there and it is enough to identify the right DYN_METHOD. If you really need to make a new entry in this table start with a similar entry as a copy template. If you have identified the correct DYN_METHOD for your field the next step is to create a new entry in table CRMC_REPDY. As an example assume you have created a new field in table CRMD_CUSTOMER_H manually and you want to enable it for the search.
The new entry should look like this:
SEL_FIELD | ZMYFIELD |
NAME_ON_DB | CRMD_CUSTOMER_H~ZMYFIELD |
DYN_METHOD | DYN_CUSTOMER_H |
CALL_FOR_WHERE | BLANK |
You could use the same approach for a SAP standard field which is not yet enabled for the reporting framework.
Apart from enabling new fields for the reporting framework the other big use case is that a field is already part of the reporting framework but the search is too slow. In particular a join between multiple tables is always slower than if all fields are already part of table CRMD_ORDER_INDEX. In order to conclude that this is the problem a ST05 trace of the search is required which needs to be analyzed by a database expert. For example in many cases the search itself is not the main performance problem but the populating of the result list.
If your analysis shows that an important search can be made significantly faster by adding it to table CRMD_ORDER_INDEX instead of doing a join with another table then you can consider adding the field to table CRMD_ORDER_INDEX. This has been described in detail in consulting note 1527039. The main task is to make the field available in table CRMD_ORDER_INDEX and to fill it using BADI CRM_ORDER_INDEX_BADI. The next step is to run report CRM_INDEX_REBUILD. The last step is then to modify the entry in table CRMC_REPDY.
Introducing new search result parameters
When introducing new search result parameters in CRM Web UI special things have to be considered in order to avoid big issues for the performance.
Unfortunately the approach which seems to be the most obvious one is very harmful for the performance. This approach is to add a new Getter method to the result list view and to populate the field with some code similar to the example shown on the next page. The code looks not very problematic at all. It even looks clean because we are making use of the fancy BOL layer which SAP is providing.
METHOD GET_ZFIELD_BAD_EXAMPLE.
DATA:
current TYPE REF TO if_bol_bo_property_access.
data: lr_entity type ref to cl_crm_bol_entity.
if iterator is bound.
current = iterator->get_current( ).
else.
current = collection_wrapper->get_current( ).
endif.
lr_entity ?= current.
lr_entity = lr_entity->get_related_entity( iv_relation_name = 'BTADVSSlsOrd' ).
lr_entity = lr_entity->get_related_entity( iv_relation_name = 'BTOrderHeader' ).
lr_entity->get_property_as_value( exporting
iv_attr_name = 'CREATED_BY'
importing
ev_result = value ).
ENDMETHOD.
The problem with this example is the following: For each row of the result list the system will execute the getter method. The request will be passed on to GENIL layer and a CRM_ORDER_READ call will happen to read the field. There are several unnecessary overheads involved here:
- CRM_ORDER_READ is expensive because some internal buffers are filled which are not needed at all for this scenario
- The database access is not optimal because the system will perform the necessary selects for each row separately
- There is some overhead from the UI framework because for each row a separate GENIL request has to be created.
- A lot of BOL entities have to be created again causing a high overhead from the Web UI framework
You will not notice a big performance degradation when the search is selective and only a few number of results is found (eg. below 10). But if the search will return 20 or 50 or even more results the code will become a big problem.
What went wrong here is that the BOL layer was used in the wrong way. The right way to provide the Web UI with search result fields is by using a BOL query result object. A query result object is basically a DDIC structure which contains all the result fields. It is filled inside the GENIL method IF_CRM_QUERY_RUNTIME_BTIL~GET_DYNAMIC_QUERY_RESULT which implements the search. The result structure can be enhanced using the usual APPEND technique. Once the query result structure is extended the logic to populate the fields needs to be added. In case the field is known to the reporting framework nothing more needs to be done and the field will automatically be populated. As an example the query result structure for sales orders crmst_query_r_sales_btil does not contain the field PO_NUMBER_SHIP but you can just add it with an APPEND.
In case a more complex logic is needed which requires ABAP code, BADI crm_badi_rf_q1o_read can be used to fill these additional fields. If the BADI is active the standard logic for filling the result fields contained in function module CRM_BSP_OIC_1O_READ_FROM_RF is skipped. Therefore you should add a call to CRM_BSP_OIC_1O_READ_FROM_RF to your BADI implementation.
For completeness it should be mentioned that there is also BADI crm_badi_rf_q1o_filter to filter the result list after the standard search is happened. For performance reasons you should not use this BADI for a selective search criteria because this will cause unnecessary reads from the database.
It is also possible to replace the whole GENIL standard search logic by an own custom logic. This can make sense for example if you want to replace CRMD_ORDER_INDEX table by a customer specific index table which may only contains activities or only sales orders. The effort is rather high but it is good to know that this option is available. This method to enhance the 1Order GENIL works not only for searches.
Open IMG activity “Define Custom Handler Classes for Business Transaction Model Nodes”
Here you can maintain an own GENIL handler class for any BOL query object. For example the BOL query for sales order search is BTQSlsOrd and the standard GENIL handler class is CL_CRM_QSLSORD_RUN_BTIL. If you want to replace this standard class by your own handler class ZCL_CRM_QSLSORD_RUN_BTIL then make an entry as in below screen shot. (The ending _RUN_BTIL will automatically be appended at the end.)
Now let’s look at an example how an enhancement of the search result list can be implemented in a better way using BADI crm_badi_rf_q1o_read mentioned above. Assume, you would like to add the field BUT000-BPEXT (business partner number of external system) for the sold-to party to the result list of the sales order search.
As the first step you should extend the query result structure crmst_query_r_sales_btil of the sales order search result structure with the field ZBPEXT using an APPEND in DDIC.
As the second step create a BADI implementation (here ZEXAMPLE_BPEXT) for BADI CRM_BADI_RF_Q1O_READ and assign filter value BTQSLSORD, which is the BOL query object for the sales order search.
The implementation could look like this:
METHOD if_crm_rf_q1o_read~read.
TYPES: BEGIN OF t_partner,
partner_no TYPE bu_partner.
TYPES END OF t_partner.
TYPES: BEGIN OF t_result,
partner TYPE bu_partner,
bpext TYPE bu_bpext.
TYPES END OF t_result.
DATA: lt_partner TYPE STANDARD TABLE OF t_partner,
ls_partner TYPE t_partner,
lt_result TYPE STANDARD TABLE OF t_result.
FIELD-SYMBOLS: <ls_query_result> TYPE crmst_query_r_sales_btil,
<ls_result> type t_result,
<ls_partner> type t_partner.
* fill standard result fields
CALL FUNCTION 'CRM_BSP_OIC_1O_READ_FROM_RF'
EXPORTING
it_object_key = it_object_key
iv_screen_structure_name = iv_result_structure_name
IMPORTING
et_screen_structure = et_result_structure.
LOOP AT et_result_structure ASSIGNING <ls_query_result>.
check <ls_query_result>-sold_to_party is not INITIAL.
ls_partner-partner_no = <ls_query_result>-sold_to_party.
COLLECT ls_partner INTO lt_partner.
ENDLOOP.
IF NOT lt_partner IS INITIAL.
SELECT bpext partner INTO CORRESPONDING FIELDS OF TABLE lt_result
FROM but000
FOR ALL ENTRIES IN lt_partner
WHERE partner = lt_partner-partner_no.
ENDIF.
LOOP AT et_result_structure ASSIGNING <ls_query_result>.
read TABLE lt_result
with key partner = <ls_query_result>-sold_to_party
ASSIGNING <ls_result>.
if sy-subrc = 0.
<ls_query_result>-zbpext = <ls_result>-bpext.
endif.
ENDLOOP.
ENDMETHOD.
Notice how the database access to BUT000 is optimized by doing an array select.
There is still one aspect, in which the first bad example does better job with respect to performance than the alternative options proposed so far. This is that a GETTER method is called only when a certain field is visible on the UI. In contrast to this the normal logic of a BOL query is to populate all the fields of the result structure.
It is possible to further optimize your search so that only the result fields are populated which the user sees on the screen. This is particularly useful when you have some result fields which are expensive to populate or if there are many fields in the query result structure and the user has typically only a few of them visible. Our implementation will be based on note 1819334 and note 2260411.
We first have to read out the currently visible fields of the result view and pass them to our query call. The following code can be used to retrieve this field. For iv_viewname we need to pass the view name of the query result view, which is different depending on application. 'BT115S_SLSO/SlsOrdSR' is for sales orders.
DATA: lr_view_controller TYPE REF TO cl_bsp_wd_view_controller.
DATA: lv_xml TYPE string.
DATA: ls_configuration TYPE bsp_dlc_table_descr.
DATA: lt_request_attributes TYPE crmt_attr_name_tab,
ls_request_attribute TYPE name_komp.
FIELD-SYMBOLS: <ls_column_def> TYPE bsp_dlc_column_descr.
lr_view_controller ?= me->m_parent.
lr_view_controller = lr_view_controller->get_subcontroller_by_viewname( iv_viewname = 'BT115S_SLSO/SlsOrdSR' ).
lv_xml = lr_view_controller->configuration_descr->get_config_data( ).
ls_configuration = cl_bsp_dlc_table_utility=>conf_xml_to_abap( lv_xml ).
LOOP AT ls_configuration-columndefinition ASSIGNING <ls_column_def>
WHERE hidden = space.
ls_request_attribute = <ls_column_def>-name.
INSERT ls_request_attribute INTO TABLE lt_request_attributes.
ENDLOOP.
The query call itself has to be adjusted like this to pass the requested attributes from the GENIL layer.
lr_result = lr_query_service->get_query_result( it_request_attributes = lt_request_attributes ).
Now the requested objects will be passed on to the query implementation from there to the BADI call.
To take the above example you can add the following code to your BADI implementation:
READ TABLE IS_REQUEST_OBJECT-REQUESTED_ATTR
TRANSPORTING NO FIELDS
WITH KEY
name = 'ZBPEXT'
hidden = space.
IF sy-subrc = 0.
- Do select and Populate ZBPTEXT
ENDIF.
This will ensure, that the field ZBPTEXT is only populated, when it is visible on the screen.
1Order database model
In order to replace CRM_ORDER_READ you need to be familiar with the database layer of the 1Order.
The leading objects for the 1Order model are the tables CRMD_ORDERADM_H (Order header) and CRMD_ORDERADM_I (Order items). The entries in table CRMD_ORDERADM_I are linked to the entries in CRMD_ORDERADM_H by the field HEADER.
There is also table CRMD_SCHEDLIN for all item related quantities. This table is linked by field ITEM_GUID to an entry in table CRMD_ORDERADM_I.
There are extension tables which have a 1:1 relation to the header or item table and end with the suffix _H or _I. There can also be complex extensions which contain table like information for a given header or item (example is the header/item status contained in table CRM_JEST linked by field OBJNR) .
Finally there are sets. Set data can be shared between different items (but never between different headers) and are linked by table CRMD_LINK. There is no suffix for sets. One of the most important set table is CRMD_PARTNER. View CRMV_LINKPARTNER can be used to access this table given a set of header or item guids.
For items an entry in table CRMD_LINK does not always have to exist. For a sub item this means that the set entry for the higher level item is valid. For a main item it means that the set entry for the header is valid.
- SAP Managed Tags:
| User | Count |
|---|---|
| 4 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
