CDS View - Parameter vs. WHERE

[schoutenk]

Hi All,

I'm still learning to construct a solid CDS view. While I was developing I was curious about the performance of the CDS view while comparing the parameters versus the WHERE statement.

I've constructed a CDS view:

@AbapCatalog.sqlViewName: 'XXX'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Module Data'
@ClientDependent: true
define view XXX

with parameters p_idate:abap.dats,
                p_lang:abap.lang,
                p_objid:abap.numc(8)
  
-- The Main object
  as select distinct from hrp1000  as object
  
-- Module Credits
  left outer join hrp1741 as module_credits 
    on object.plvar = module_credits.plvar 
      and object.otjid = module_credits.otjid
                                
-- Module Capacity
  left outer join hrp1024 as capacity 
    on object.plvar = capacity.plvar
      and object.otjid = capacity.otjid
                                
-- Module Data
  left outer join hrp1746 as module_data 
    on object.plvar = module_data.plvar
      and object.otjid = module_data.otjid

{

    key object.objid             as ID,
    object.mc_short              as ShortName,
    object.stext                 as Name,
    object.begda                 as BeginDate,
    object.endda                 as EndDate,
    
    module_credits.cpmin         as MinCredits,
    module_credits.cpmax         as MaxCredits,
    module_credits.cpopt         as OptimumCredits,
    
    capacity.kapz1               as MinCapacity,
    capacity.kapz2               as OptimumCapacity,
    capacity.kapz3               as MaxCapacity,
    
    module_data.severity         as AcademicLevel,
    module_data.category         as Category,
    module_data.modrepeattype    as RepetitionType,
    module_data.waitl_level      as WaitingListLevel,
    module_data.waitl_number     as WaitlistInPercent,
    module_data.waitl_disabled   as WaitListIsDisabled,
    module_data.mult_sec_enabled as MltplSctnIsEnabled,

}
where object.plvar = '01'
  and object.otype = 'SM' 
  and object.objid = $parameters.p_objid
  and object.begda <= $parameters.p_idate 
  and object.endda >= $parameters.p_idate
  and object.langu = $parameters.p_lang
  and module_credits.begda <= $parameters.p_idate 
  and module_credits.endda >= $parameters.p_idate
  and capacity.begda <= $parameters.p_idate 
  and capacity.endda >= $parameters.p_idate
  and module_data.begda <= $parameters.p_idate 
  and module_data.endda >= $parameters.p_idate

The performance of this view is great. But I don't want to use the Objectid (OBJID) as a parameter. I just want to be able to select all objects which I need in each specific selection.

Performance when using the OBJID as parameter: ~120.000 microsec.

Performance when using the OBJID in the WHERE clause: ~800.000 microsec.

Why is the performance so much slower when using the OBJID in the WHERE clause instead of the OBJID as parameter?

[retired_member]

Sorry, I'm confused. What do you mean with

"OBJID as parameter" vs. "OBJID in the WHERE clause"?

Do you speak about the Open SQL Statement accessing the view, one time with object id passed as parameter and one time in its WHERE clause?

If yes, use SQL trace in ABAP Debugger or in ST05 in order to see the different SQL Statements generated by the DBI. That should give you a hint why the performance differs.

[schoutenk]

Hi Horst,

Thanks for your quick reply.

Maybe my initial question wasn't clear enough. So I'll try to explain.

I've created an CDS view as the one stated above. I'm not pleased with the performance of this CDS-view.

I've tested out the difference of performance while passing my selection (the OBJID) through the CDS-view parameter and through the WHERE clause.

So I've got two calls:

First one (through parameter)

SELECT *
  FROM zcmod_dd_module_data( p_idate = @sy-datum, p_lang = @sy-langu, p_objid = '12345678' )
  INTO TABLE @DATA(lt_module_data).

Second one (through WHERE clause)

SELECT *
  FROM zcmod_dd_module_data( p_idate = @sy-datum, p_lang = @sy-langu )
  INTO TABLE @DATA(lt_module_data)
    WHERE id = '12345678'.

When I do a trace with ST05 the first call has a duration of 119.903 microseconds.

Here is the execution plan for this call:

The second call has a duration of 641.768 microseconds.

Here is the execution plan for this call.

The execution plan is exactly the same. So why is the selection on the CDS-view parameter faster than the WHERE-clause?

[pokrakam]

Interesting question. As Horst suggested, do a trace.

The observed performance and execution plans diverge. My guess based on the performance is that a WHERE variant does a full CDS query for all OBJIDs and filters that.

[retired_member]

So you must have two different CDS views. One with a parameter for objid and one without. From these two CDS views different DB objects are instantiated. Please look at those (context menu in ADT) and check in ST05 the SQL statements that are used to access them.

My guess is, that the DB objects are both DB functions. One with objid as parameter and one without. For the first one, objid is part of the WHERE condition within the function and for the second one it is not. For the second one, there is then an additional WHERE condition with objid on the result set of the function (or the whatsoever DB object).

That might explain the difference. It shouldn't be that large, but this is then more a DB specific problem.

[schoutenk]

Hi Horst,

Thank you. This makes my problem more clear. Can you clearify the part were you mention the context menu in ADT? Where can I view the DB-object of my CDS view? Do you mean the SQL View?

Does this technique work differently on a HANA platform?

Edit:

I've just seen something interesting. I've tested the performance when totally discarding the CDS-parameters. Then I've just selected the records I want in the WHERE statement (when calling my CDS-view). This way the performance is excellent. This way I've come to the conclusion that we cannot combine CDS-parameters with a WHERE clause when calling the CDS. This is killing the performance because the WHERE is beiïng performed on the resultset.

[retired_member]

"Do you mean the SQL View?"

Yes. But it depends on the platform, how a CDS view with parameters is instantiated. It might be views or functions for platforms that do not directly support views with parameters.

[akshayrajkumar]

There where clause while calling acts as part of filtering in the SQL after the query hits the database, where as calling the CDS using parameters are passed as a part of CDS view definition. Hence the filtration of data is done in the DB during the execution of the call. Hence CDS with Parameters is faster compared to using where clause.