What goes beyond the code push down? It is the filter push down!
For a better understanding of code push down please refer to my below blog post on code pushdown.
As I have already discussed in my previous blog post, greater the number of records, more the time taken by the application.
We already know enough on minimizing the number of records returned by the database layer to the ABAP layer. Going beyond ,how do we reduce the number of records processed by the SAP HANA database layer?
Just by pushing down the filters from consumption level selection to the SAP HANA database table level selection.
Very often in the normal application design, the UI( User Interface) layer filters are applied at the consumption views level and not on the database table level. In high volume analytical applications this design would make the CDS views to process and return a larger dataset which in turn hits on the memory and the performance of the application.
The better approach for a high data volume application is to push down the filters as much as possible to the data base table level so that we will have to work with the required set of data in order to achieve an optimized use of time and memory.
In this blog post I have discussed on various technics those can be used to push down the consumption level filters to the database table level
- Pushing down the single selection filters from the fiori application using parameterized ABAP CDS views
- Pushing down the multiple selection filters from the fiori application using the dynamic where clause – In combination with the ABAP Managed Database Procedure (AMDP ) table functions and ABAP CDS views.
Pushing down Single selection filters
In the below example, there is a mandatory filter criterion “Ad Effective Date” in the fiori application layer. Let us see how to taken the filter till the database level selection instead of using it as a consumption filter.
Single Select Filter example
Place this filter as a parameter in the final Consumption CDS view and pass it down all through your layers till it reaches the actual table selection.
“P_adeffdate” is the parameter in my example. ZI_PROD_FILTER_BYDATE = > is the actual CDS view which hits the database.
My Consumption view – ZC_GEN_REPORT
@AbapCatalog.sqlViewName: 'ZCGENREPORT'
@AbapCatalog.compiler.compareFilter: true
@VDM.viewType: #CONSUMPTION
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Filter the products by date'
@ObjectModel: {
usageType.serviceQuality: #D,
usageType.sizeCategory: #XXL,
usageType.dataClass: #TRANSACTIONAL
}
@Metadata.allowExtensions: true
define view ZC_GEN_REPORT
with parameters
p_adeffdate :char8
as select from ZI_PROD_FILTER_BYDATE(p_adeffdate:$parameters.p_adeffdate) as _Product
{
key _Product.mandt as Clnt,
key _Product.node_id as NodeId,
key _Product.prod_id as ProdId,
key _Product.prod_hr_id as ProdHrId,
_Product.prod_name_uc as ArticleName
}@AbapCatalog.sqlViewName: 'ZIPRODSEARCH'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Filter the products by date'
define view ZI_PROD_FILTER_BYDATE
with parameters
p_adeffdate : dats
as select from /dmf/prod_nd_xr as _Product
inner join /dmf/prod as _ArticleChar on _Product.prod_id = _ArticleChar.prod_id
association [0..1] to /dmf/prodt as _ArticleTxt on _Product.prod_id = _ArticleTxt.prod_id
{
key _Product.mandt as Clnt,
key _Product.node_id as NodeId,
key _Product.prod_id as ProdId,
key _Product.prod_hr_id as ProdHrId,
_ArticleTxt.prod_name_uc as ArticleName
}
where
_Product.valid_fr <= $parameters.p_adeffdate
and _Product.valid_to >= $parameters.p_adeffdate
Below are the snippets on how to call the parameterized ABAP CDS views from different layers of the application
Calling from the UI layer for an analytical application
/sap/opu/odata/sap/ZC_GEN_ANALYTICAL_REPORT_SRV/ZC_GEN_ANALYTICAL_REPORT(p_adeffdate=datetime'2020-07-26T00:00:00')/Results Calling from the UI layer for a normal application
/sap/opu/odata/sap/ZC_GEN_REPORT_SRV/ZC_GEN_REPORT(p_adeffdate=datetime'2020-07-26T00:00:00')/SetCalling a parameterized CDS view from ABAP layer
select * from ZIPRODSEARCH(p_adeffdate = @lv_adeffdate) into table @data(lt_prod).Calling the parameterized CDS views in AMDP method for a table function – SQL layer
Table function
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Table function for Prod att'
@ClientHandling.type: #CLIENT_INDEPENDENT
define table function ZTABL_PROD
with parameters
p_adeffdate : dats
returns
{
key clnt : abap.clnt;
key ProdHrId : /dmf/hierarchy_id;
ProdId : /dmf/product_id;
}
implemented by method
ZCL_COM_FINAL=>GET_PROD;
AMDP method
method get_prod
by database function
for hdb
language sqlscript
options read-only
using ZIPRODSEARCH.
Return select clnt, ProdHrId , ProdId from ZIPRODSEARCH(:p_adeffdate) as _Prod
Where _Prod.clnt = session_context('CLIENT');
Endmethod.
Calling the parameterized CDS inside another CDS View
Association example
@AbapCatalog.sqlViewName: 'ZCRETAIL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Retail Report'
}
define view ZI_RETAIL
with parameters
p_addeffdate : dats
as select from ZI_RETAIL as _Main
association [0..1] to ZI_PROD_FILTER_BYDATE as _Prod on _Main.ProdId = _Prod.ProdId
{
Key _Main.ProdHrId as ProdHrId,
_Prod(p_adeffdate:$parameters.p_adeffdate).prodId as ProdId
}
Using inner join
Inner join ZI_PROD_FILTER_BYDATE(p_adeffdate:$parameters.p_adeffdate) as _Prod on _Main.ProdId =
_Prod.ProdId
{
Key _Main.ProdHrId as ProdHrId,
_Prod.prodId as ProdId
}
Pushing down the multiple selection(select options) type filters
Sometimes it takes a lot to pass the multiselect filters from the UI layer to the database layer. But when performance of the application is a key factor, we can make use of the dynamic where clause in combination with the AMDP table functions and achieve the desired filter push down
Here in my current example , I have considered a CDS entity Set , redefined the Get_entityset method of the DPC_EXT class in my ODATA project and converted the multiselect filters into a dynamic where clause. After which I have passed the where clause down to the database table in my AMDP method through the Table function via the Consumption CDS view.
Along with Ad effective date which is a single select filter , I have two multiselect filters – Department and Sales Org
Multiple selection Filters example
Let me start with the consumption CDS view and then take you through the code in the ABAP layer.
My consumption CDS view will look like this
“Dept” and “SalesOrg” are my multiselect filters
@AbapCatalog.sqlViewName: 'ZCGETOFFER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Get offers'
@ObjectModel: {
usageType.serviceQuality: #D,
usageType.sizeCategory: #XL,
usageType.dataClass: #TRANSACTIONAL
}
define view ZC_GET_OFFER
with parameters
@Consumption.hidden: true
P_WHERE : abap.char(1333)
as select from ZTABL_ALL_OFR(P_WHERE:$parameters.P_WHERE) as _AllOfr
{
key _AllOfr.OfrId as OfrId,
key _AllOfr.OfrIntId as OfrIntId,
_AllOfr.Dept as Department,
_AllOfr.SalesOrg as SalesOrg
}
Let me expand the table function – ZTABL_ALL_OFR
@AccessControl.authorizationCheck: #CHECK
@ClientHandling.type: #CLIENT_DEPENDENT
@EndUserText.label: 'Offer details / Min adzone'
define table function ZTABL_ALL_OFR
with parameters
P_WHERE : abap.char(1333)
returns
{
key clnt : abap.clnt;
key OfrId : /dmf/ext_offer_id;
key OfrIntId : /bobf/conf_key;
Dept : char2;
SalesOrg : /dmf/org_unit_id_so;
}
implemented by method
ZCL_COM_FINAL=>ALL_OFFERS;
Let’s get more deeper into the AMDP method
method all_offers
by database function
for hdb
language sqlscript
options read-only
using ztabl_ofr.
itab_ofr = SELECT _Ofr.mandt as clnt , _Ofr.OfrId , _Ofr.OfrIntId ,_Ofr.Dept , _Ofr.SalesOrg FROM
ztabl_ofr AS _Ofr where _Ofr.mandt = session_context('CLIENT');
itab_final = apply_filter ( :itab_ofr , :P_WHERE) ;
return select * from itab_final;
endmethod
After all the drill down , lets complete the top layer
Building a dynamic where clause and passing it as a parameter to the CDS entity
method zc_Offer_get_entityset.
constants : lc_dept type c length 4 value 'DEPT',
lc_salesorg type c length 7 value 'SALESORG'.
data: dept type /iwbep/t_cod_select_options,
salesorg type /iwbep/t_cod_select_options.
data: lo_tech_request_clone type ref to /iwbep/cl_mgw_request,
lt_headers type tihttpnvp,
lr_request type ref to /iwbep/if_mgw_core_srv_runtime=>ty_s_mgw_request_context.
data(ls_request) = cast /iwbep/cl_mgw_request( io_tech_request_context )->get_request_details( ).
data ls_key like line of ls_request-technical_request-key_tab
try.
data(lt_dep) = it_filter_select_options[ property = lc_dept ]-select_options.
catch cx_sy_itab_line_not_found.
clear lt_dep.
endtry.
try.
data(lt_salesorg) = it_filter_select_options[ property = lc_Salesorg ]-select_options.
catch cx_sy_itab_line_not_found.
clear lt_salesorg.
endtry.
data(lv_where) =
cl_shdb_seltab=>combine_seltabs( it_named_seltabs = value #( ( name = 'Dept' dref = ref #( lt_Dep[] ) )
( name = 'SalesOrg' dref = ref #(
lt_salesorg[] ) ) ) ).
ls_key-name = 'P_WHERE'.
ls_key-value = lv_where.
DESCRIBE TABLE ls_request-technical_request-key_tab lines DATA(lv_lines).
Add 1 to lv_lines.
insert ls_key into ls_request-technical_request-key_tab index lv_lines .
get reference of ls_request into lr_request.
data(lo_model_fw) = cast /iwbep/if_mgw_odata_fw_model( cast /iwbep/cl_mgw_dp_facade( me- >/iwbep/if_mgw_conv_srv_runtime~get_dp_facade( ) )->/iwbep/if_mgw_dp_int_facade~get_model( ) ).
lo_tech_request_clone = new #( ir_request_details = lr_request it_headers = lt_headers io_model =
lo_model_fw ).
try.
call method super->zc_get_offer_get_entityset
exporting
iv_entity_name = iv_entity_name
iv_entity_set_name = iv_entity_set_name
iv_source_name = iv_entity_set_name
it_filter_select_options = it_filter_select_options
is_paging = is_paging
it_key_tab = it_key_tab
it_navigation_path = it_navigation_path
it_order = it_order
iv_filter_string = iv_filter_string
iv_search_string = iv_search_string
io_tech_request_context = lo_tech_request_clone
importing
et_entityset = et_entityset
es_response_context = es_response_context.
catch /iwbep/cx_mgw_busi_exception .
clear : et_entityset,es_response_context.
catch /iwbep/cx_mgw_tech_exception .
clear : et_entityset,es_response_context.
endtry.
endmethod.
This way we can take down the multiple select options till the database layer and not disturb the other query options rendered by the framework!
Now you are all set for a lightning fast application !
Do let me know how it worked for you! I will meet you in my next blog post where I shall discuss more on the analytical CDS views and their graphical representation in the fiori application
Cheers!
Santhosini K
