CRM BP SEARCH 优化

出于方便的目的，这边将BU_GROUP，SALE_ORG加到了BP搜索里面，因为CRM项目推广了很多国家，每个国家一个SALE ORG，而标准的BP HEADER ADV SEARCH里竟然没有BU_GROUP。。。。所以这边就把这两个字段加到搜索条件里了。

至于怎么加进去的就不说了，已经经历了好几代开发的手了。。。

但是效率一直是用户所追求的，而标准的BP搜索，都是很挫的那种，先做查询优先级，然后根据优先得出的PARTNER KEY再去关联其他信息做过滤。。。

可能是查询条件太多，牵涉的表太多，所以系统才用这种办法吧，但是每次500条，然后过滤，然后再500，再过滤，直到补齐查询的MAX HIT

以上是查询的逻辑，不多说，下面来说说怎么优化性的将这两个字段加入到主查询中(因为这两个字段是最常用的，也最可能单独使用的，所以不加到查询中，只过滤会很慢)

1.复制标准的查询类：CL_BUPA_IL_HEADER_SEARCH->ZL_BUPA_IL_HEADER_SEARCH

此类中的GET_DYNAMIC_QUERY_RESULT会做个优先排序：checked_search_available

然后在GET_RESULT_TABLE_CHECKED中动态拼接查询方法：CALL METHOD me->(lv_method_name)

前面基本不动，因为要把这两个查询条件放到主查询中，所以在上面的lv_method_name 等于‘SEARCH_BP_GENERAL’的时候，增加处理逻辑

增加SEARCH接构：ls_bp_gen_search01 TYPE Zcrmt_bupa_il_bp_gen_search,（额外DATA定义的）

selection处理前，添加parameters:

"add attr to gen field by ly 20170525
  IF ME->GV_CONDITION = 'X'.
    APPEND 'ZSALES_ORG' to gt_bp_gen_attributes.
    APPEND 'ZBP_GROUP' to gt_bp_gen_attributes.
  ENDIF.

处理结束后删除：

"delete add attr gen field append up by ly 20170525
  IF ME->GV_CONDITION = 'X'.
    delete gt_bp_gen_attributes WHERE table_line = 'ZSALES_ORG'.
    delete gt_bp_gen_attributes WHERE table_line = 'ZBP_GROUP'.
  ENDIF.

调用查询方法增强：

* get partner package matching the but000 criteria:
    IF ME->GV_CONDITION = ''."BP查询开关，未开启则走原始逻辑 add by ly 20170526
      CALL METHOD cl_bupa_il_search_service=>search_by_bp_general
        EXPORTING
          is_control               = is_control
          is_bp_gen_search         = ls_bp_gen_search
          iv_max_hit               = lv_max_hits
          it_selection_parameters  = lt_bp_gen_params
          iv_last_selected_partner = lv_last_selected_partner
        CHANGING
          ct_partner_keys          = lt_partner_keys
          ct_return                = ct_return.
      "CHANGE FROM cl_bupa_il_search_service=>search_by_bp_general TO Zcl_bupa_il_search_service=>search_by_bp_general
      "by ly 20170525
    ELSE."BP查询开关，未开启则走原始逻辑开启则走新逻辑 add by ly 20170526
      CALL METHOD Zcl_bupa_il_search_service=>zsearch_by_bp_general
        EXPORTING
          is_control               = is_control
          is_bp_gen_search         = ls_bp_gen_search01
          iv_max_hit               = lv_max_hits
          it_selection_parameters  = lt_bp_gen_params
          iv_last_selected_partner = lv_last_selected_partner
          GV_CONDITION             = ME->GV_CONDITION
        CHANGING
          ct_partner_keys          = lt_partner_keys
          ct_return                = ct_return.
    ENDIF.

复制cl_bupa_il_search_service的search_by_bp_general到zcl_bupa_il_search_service为：ZSEARCH_BP_GENERAL

而原本的zsearch_by_bp_general改为调用ZSEARCH_BP_GENERAL

METHOD zsearch_by_bp_general.
  "CHANGE TO BY LY 20170525
    CALL METHOD zcl_bupa_il_search_service=>ZSEARCH_BP_GENERAL"search_by_bp_general
    EXPORTING
      is_control               = is_control
      is_bp_gen_search         = is_bp_gen_search
      iv_max_hit               = iv_max_hit
      it_selection_parameters  = it_selection_parameters
      iv_last_selected_partner = iv_last_selected_partner
      GV_CONDITION             = GV_CONDITION
    CHANGING
      ct_partner_keys          = ct_partner_keys
      ct_return                = ct_return.
ENDMETHOD.

拼接动态查询时添加：（append_where_cond_to_select原样从cl_bupa_il_search_service中复制过来，包括对应用到的结构）

  "add where  cluse by ly 20170525  BP查询开关开启 添加查询条件    BEGIN
    IF is_bp_gen_search-ZBP_GROUP IS NOT INITIAL AND GV_CONDITION = 'X'.
    CALL METHOD cl_bupa_il_search_service=>map_attribute_to_range_tab
      EXPORTING
        iv_attribute_name       = 'ZBP_GROUP'
        it_selection_parameters = it_selection_parameters
      IMPORTING
        et_range_tab            = lt_group.
    IF lt_group IS NOT INITIAL.
      append_where_cond_to_select( EXPORTING iv_where_line     = 'BU_GROUP IN lt_group '
                                   CHANGING ct_where_pattern = lt_where_pattern ). "#EC NOTEXT
    ENDIF.
  ENDIF.

  IF is_bp_gen_search-ZSALES_ORG IS NOT INITIAL AND GV_CONDITION = 'X'.
    CALL METHOD cl_bupa_il_search_service=>map_attribute_to_range_tab
      EXPORTING
        iv_attribute_name       = 'ZSALES_ORG'
        it_selection_parameters = it_selection_parameters
      IMPORTING
        et_range_tab            = lt_SALES_ORG.
    IF lt_SALES_ORG IS NOT INITIAL.
      append_where_cond_to_select( EXPORTING iv_where_line     = 'SALES_ORG IN lt_SALES_ORG '
                                   CHANGING ct_where_pattern = lt_where_pattern ). "#EC NOTEXT
    ENDIF.
  ENDIF.
  "add where  cluse by ly 20170525  BP查询开关开启 添加查询条件    end

修改查询的对象，从BUT000到新的视图‘ZCRMV_BP_SEARCH’（此视图后面说明）

* DATA:  lv_from_clause TYPE string VALUE 'BUT000 '.
 DATA:  lv_from_clause TYPE string VALUE 'ZCRMV_BP_SEARCH '."CHANGE BY LY 20170525
 IF ( is_bp_gen_search-ZSALES_ORG IS INITIAL AND is_bp_gen_search-ZBP_GROUP IS INITIAL ) or is_control-employees = 'X'."add by ly 20170525
   lv_from_clause = 'BUT000'.
 ENDIF.

 IF GV_CONDITION = ''."BP查询开关，如果未开启，则走原始逻辑
   lv_from_clause = 'BUT000'.
 ENDIF.

另外，在查询后的数据过滤，也做个指向更改：FILTER_BY_SEARCH_CRITERIA

DATA ls_search_criteria01  TYPE ZHSCRMT_BUPA_IL_HEADER_SEARCH."change by ly 20170525
DATA ls_search_criteria    TYPE crmt_bupa_il_header_search.

 

* search by general but000 data
  IF ls_search_criteria01-bpgeneral IS NOT INITIAL.
    IF ME->GV_CONDITION = ''.
      CALL METHOD cl_bupa_il_search_service=>search_by_bp_general
        EXPORTING
          is_control              = ls_control
          is_bp_gen_search        = ls_search_criteria-bpgeneral
          it_selection_parameters = lt_selection_parameters
        CHANGING
          ct_partner_keys         = ct_partner_keys
          ct_return               = lt_return.
      APPEND LINES OF lt_return TO ct_return .
      IF ct_partner_keys IS INITIAL.
*     criteria given, but no result found
        RETURN.
      ENDIF.
    ELSE.
      "change by ly 20170525
      CALL METHOD zcl_bupa_il_search_service=>ZSEARCH_BP_GENERAL
        EXPORTING
          is_control              = ls_control
          is_bp_gen_search        = ls_search_criteria01-bpgeneral
          it_selection_parameters = lt_selection_parameters
          GV_CONDITION             = ME->GV_CONDITION
        CHANGING
          ct_partner_keys         = ct_partner_keys
          ct_return               = lt_return.
      APPEND LINES OF lt_return TO ct_return .
      IF ct_partner_keys IS INITIAL.
*     criteria given, but no result found
        RETURN.
      ENDIF.
    ENDIF.

  ENDIF.

 

 

 

将GENERAL中的查询字段都添加进这个视图，后面查询时则自动匹配，省去了过滤的时间和效率。

 

优化效率说明：将查询能卡死或者超过30分钟的，优化到2-3秒，一是因为数据存储和查询条件的无序，二是省略了多次查询BUT000大数据量的表

BUT000数据量：60W，里面的数据是按照推广的顺序依次创建的，那么后面推广的国家的数据会存的最'深'。

只用BU_GROUP和SALE_ORG查询时，会从BUT000中一次500条排序取，然后根据GROUP和SALE过滤，这样直到取到有效的数据需要60W/500 = 1200次，每次查询后还要查询一到两次BUT000，每次大概耗时1-2秒。

优化后从新建的视图中取数，在只用GROUP和SALE时，只取一次，全部满足，不在额外查询BUT000.。。。

 

影响分析：原始的增强查询是有其他查询条件时先查其他，在用GENERAL过滤，然后再过滤GROUP和SALE

              处理后，用配置可配置是否走这个增强，原始的查询走向不变，在特定情况会从新视图中查询和过滤，无二次过滤。

在普通情况依然走原始逻辑，特殊情况走新视图。

 

 

^_^没怎么好好整理，有点乱