Customer Relationship Management Blogs by SAP
Stay up-to-date on the latest developments and product news about intelligent customer experience and CRM technologies through blog posts from SAP experts.
cancel
Showing results for 
Search instead for 
Did you mean: 
dmitry_sharshatkin
Active Participant
0 Kudos
Hello All,

Nowadays it's getting more and more popular to use custom-developed virtual database table fields. However we all know that such fields should not be used for the searches (or joins) for performance reasons.

In this document I will show you how under certain conditions, you can still nicely consume such fields...

 

Use Case

In one of our applications, we have a search over the 1Order documents.

It uses the tables:

  • CRMD_SRV_SUBJECT (Service Subject) and

  • CRMD_SRV_OSSET (Service Subject Reference Object).


However, we need to pass the following “Reasons” as search criteria.































Technical Key Meaning
Z2ZS460001 Description 1
Z2ZS460002 Description 2
Z2ZS460003 Description 3
Z2ZS460004 Description 4
Z2ZS460005 blablabla
etc... etc.

 

The problem is that those reasons (in other meaning “Results”) are for the combination of the fields KATALOGART (Catalog), CODEGRUPPE (Code Group) and CODE (Code) in the table CRMD_SRV_SUBJECT.



I.e. Z2ZS460001 is translated into KATALOGART = “Z2”, CODEGRUPPE = “ZS46”, CODE = “0001”.

If you are using just one technical key with search option EQ (equal), it’s not a problem.

But when you are using multiple technical keys referring to different catalogs or code groups, when you are not using just EQ, but NE (not equal), or Exclusions or even GE (Greater or Equal), then the processing of the search parameters becomes a real nightmare!

The root cause of the problem is that we are using search criteria, which are split among three different database fields.  If this would be just one field, no issue then.

Why not to do so?

With ABAP CDS we can easily add a virtual column to the table CRMD_SRV_SUBJECT, which would then concatenate those three fields: concat( concat(katalogart,codegruppe ),code ) as zs_full_code.

 
@AbapCatalog.sqlViewName: 'ZDMSHVCOLV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Dima''s Virtual Column'
define view ZDMSH_VIRT_COL as
select from crmd_srv_subject as sbj
{
sbj.guid,
sbj.guid_ref,
sbj.username,
sbj.timestamp,
sbj.katalogart,
sbj.codegruppe,
sbj.code,
sbj.defectclass,
sbj.quality,
sbj.defquantity,
sbj.guid_hierarchy,
sbj.sort,
sbj.ext_code,
sbj.asp_id,
sbj.cat_id,
sbj.katalog_type,
concat( concat(katalogart,codegruppe ),code ) as zs_full_code
}

 

Functionally this solution works perfectly fine, however there are some performance drawbacks. The thing is that, if you are using the new virtual column as a search criteria (as in our case), the database should first “materialize” a complete table (if no other criteria are applied) and only then do further steps. In our system, the table CRMD_SRV_SUBJECT contains more than 24 million entries, so the above solution does not seem to be an option for us…

Can we do something else? Yes…

After checking out the value help tables, we discovered that all possible combination for the fields KATALOGART, CODEGRUPPE and CODE are kept in the customizing table QPCD (Inspection Catalog Codes). Therefore, we can first apply all our filters to this little table (in our case it contains 6.5 thousand records) and then we can continue to process the bigger tables.  And again, we need a virtual column, so we create an ABAP CDS view as below.
@AbapCatalog.sqlViewName: 'ZS_QPCD_V'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Inspection Catalog Codes (Advanced)'
define view ZS_QPCD as
select from qpcd
{
mandt,
katalogart,
codegruppe,
code,
defectclass,
ersteller,
e_datum,
aenderer,
a_datum,
inaktiv,
verwendung,
concat( concat(katalogart,codegruppe ),code ) as zs_full_code
}

 

In your implementation, you can use ABAP FOR ALL ENTRIES option, combining those three fields. But we created another ABAP CDS, joining application tables and previously created ABAP CDS.
@AbapCatalog.sqlViewName: 'ZS_ACT_REASONV'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #NOT_RELEVANT
@EndUserText.label: 'Activity Reason Code (RFW)'
define view ZS_ACTIVITY_REASON as
select from crmd_srv_osset as sos

inner join crmd_srv_subject as sbj on sbj.client = sos.client
and sbj.guid_ref = sos.guid
and sbj.client = sos.client

/* Join Little Customizing Table For Performance Reasons */
inner join ZS_QPCD as cod on cod.mandt = sbj.client
and cod.katalogart = sbj.katalogart
and cod.codegruppe = sbj.codegruppe
and cod.code = sbj.code
{
sos.client,
sos.guid_set,
sos.profile_type,
sbj.guid,
sbj.guid_ref,
sbj.username,
sbj.timestamp,
sbj.katalogart,
sbj.codegruppe,
sbj.code,
sbj.defectclass,
sbj.quality,
sbj.defquantity,
sbj.guid_hierarchy,
sbj.sort,
sbj.ext_code,
sbj.asp_id,
sbj.cat_id,
sbj.katalog_type,
cod.zs_full_code as zs_reason_code
}

 

Finally, CRM Reporting Framework (RFW) has been updated (tables CRMC_REPDY and CRMC_REPDY_DB) to be able to understand a new field.



 



 

Thanks, Dima