How SAP ASE 16.0 SP03 is boosting CDS performance
Starting with SAP NetWeaver 7.40 SP5 the Core Data Services (CDS) are supported by ABAP. And the number of supported features has been growing with later releases. A very good overview can be found in the Core Data Services (CSD) ABAP Feature Matrix.
One of the central ideas of CDS is to push down some calculations from the ABAP layer to the database. For example SAP NetWeaver 7.50 added a lot of date/time functions. This means that calculations with date fields that in the past required a lot of data to be read by the application server can now be directly done on the database. Of course modern databases offer date calculations built in in their native SQL dialect. But first of all ABAP stores date fields not in the native date/time data types offered by the different databases. And even more important: it has to be ensured that calculations done with CDS give the same result on all supported databases and the logic should be the same as if it has been done in ABAP. To give you an example: In ABAP you can do calculations with date fields that are prior to the year 1582. In that year the papal bull Inter Gravissimas was issued – changing the calendar used in most countries nowadays. While the story behind this is quite interesting it’s making date calculations a bit more complex. Of course your company was most likely not in business at that point in time there still might be data in your system that is using such dates (maybe an application programmer used ‘1234’ for ‘since ever’ – which is a bad idea but such things exist). Changing an application from traditional ABAP to CDS you expect exactly the same results as before. And date calculations are just the starting point: calculating with timestamps can be really complex if you are not using UTC but wallclock time and have to consider timezones, daylight saving times… Anyhow: as a programmer you expect that such a calculation is giving you the same result on any database. And while this is making your life easier it meant that over the last years several developers at SAP had to implement such functions for any database supported.
For SAP Adaptive Server Enterprise (ASE) we started with a concept available in most modern databases: user defined functions (UDF). So for example we created a function to rpad.
> sp_help 'REPLACE' Name Owner Object_type Object_status Create_date ----------- ----- ------------ ------------- ------------------- SAP_REPLACE dbo SQL function -- none -- May 19 2017 4:10PM Parameter_name Type Length Prec Scale Param_order Mode -------------- ------- ------ ---- ----- ----------- ---- @STR varchar 16384 NULL NULL 1 in @PATTERN varchar 16384 NULL NULL 2 in @REPLACEMENT varchar 16384 NULL NULL 3 in Return Type varchar 16384 NULL NULL 4 out
If you are interested in the implementation:
> sp_showtext 'SAP_REPLACE' CREATE FUNCTION SAP_REPLACE( @STR VARCHAR(16384), @PATTERN VARCHAR(16384), @REPLACEMENT VARCHAR(16384) ) RETURNS VARCHAR(16384) AS BEGIN return CASE WHEN @STR IS NULL THEN NULL WHEN @PATTERN IS NULL THEN NULL WHEN @REPLACEMENT IS NULL THEN NULL WHEN RTRIM(@STR) IS NULL THEN '' WHEN RTRIM(@PATTERN) IS NULL THEN @STR WHEN RTRIM(@REPLACEMENT) IS NULL THEN STR_REPLACE(CAST(RTRIM(@STR) AS VARCHAR(16384)), RTRIM(@PATTERN), null) ELSE STR_REPLACE(CAST(RTRIM(@STR) AS VARCHAR(16384)), RTRIM(@PATTERN), RTRIM(@REPLACEMENT)) END END
Quite complex for just replacing a string. And there are built-ins replace and str_replace in T-SQL which are very similar.
With this approach all features needed to support CDS on SAP ASE have been implemented and if you are running any application that uses CDS on ASE you will use them. But there is a drawback using UDFs: Executing the SQL inside the UDF will take some time. In general functions built into the database server’s SQL dialect shows way better performance. The nice thing here is: As Adaptive Server Enterprise is an SAP product it has been possible to enhance it in a way perfect for the needs of CDS. No, we could not make all that functions part of the T-SQL dialect. The reason is that some functions are only working for NetWeaver ABAP systems as they directly access database tables that are part of the NetWeaver stack. But with ASE 16.0 SP03 the database server has been enhanced in a way that for ABAP systems a shared library can be loaded that contains all the needed functions not as SQL-UDFs but as functions written in C – resulting in way faster execution times on such functions. If you look at the objects in your <SID>-database you will see a new type of objects:
> select type from sysobjects where name = 'SAP_REPLACE' F
Let’s have a more detailed look:
> sp_help 'SAP_RPAD' Name Owner Object_type Object_status Create_date -------- ------ ------------- ------------- ------------------- SAP_RPAD SAPSR3 SQLJ function -- none -- Jan 15 2018 12:32PM Parameter_name Type Length Prec Scale Param_order Mode -------------- ------- ------ ---- ----- ----------- ----------- Return Type (NULL) 4000 NULL NULL 0 Return Type @str varchar 4000 NULL NULL 1 in @len int 4 NULL NULL 2 in @pattern varchar 4000 NULL NULL 3 in
As this is not longer an SQL-written user defined functions you will not be able to get the source code behind it with sp_showtext. But at least you will get some more information about the object
> sp_showtext 'SAP_REPLACE' create function SAPSR3.SAP_REPLACE ( a varchar(16384), b varchar(16384), c varchar(16384) ) returns varchar(16384) deterministic language c parameter style c external name 'udfsybslib.str_replace'
So how to get those C-written functions for your NetWeaver system? This is a feature supported starting with SAP ASE 16.0 SP03. The functions will be part of the installation or database upgrade. There is no need for manual interaction, the SAP installation/upgrade tools will take care of the deployment.