采用CE(Calculation Engine)执行SAP HANA 8相同的功能,比较两者区别,执行效率。
1、新建文件NewFile创建get_bp_addresses_by_role_ce.procedure
2、Local Table Type
create typett_bp_addresses as table (
PartnerIdnvarchar(10),
PartnerRolenvarchar(3),
EmailAddressnvarchar(255),
CompanyNamenvarchar(80),
AddressIdnvarchar(10),
City nvarchar(40),
PostalCodenvarchar(10),
Street nvarchar(60)
)
3、SQLScript
CREATE PROCEDURE get_bp_addresses_by_role_ce (
in partnerrole nvarchar(3),
out bp_addresses tt_bp_addresses
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
/*****************************
Write your procedure logic
*****************************/
lt_bp =
CE_COLUMN_TABLE("SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::businessPartner",
["PartnerId", "PartnerRole", "EmailAddress", "CompanyName", "AddressId" ]);
lt_bp_proj = CE_PROJECTION(:lt_bp,
["PartnerId", "PartnerRole", "EmailAddress" , "CompanyName", "AddressId" ],
'"PartnerRole" = :partnerrole' );
lt_address =
CE_COLUMN_TABLE("SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::addresses",
["AddressId", "City", "PostalCode", "Street"]);
bp_addresses = CE_JOIN(:lt_bp_proj, :lt_address, ["AddressId"],
["PartnerId", "PartnerRole", "EmailAddress", "CompanyName",
"AddressId", "City", "PostalCode", "Street"]);
END;
4、Team→Commit& Activate之后,在SQL Console中调用代码
call"_SYS_BIC"."WorkShop.sessiona.00.models/get_bp_addresses_by_role_sql"(
partnerrole =>'02', bp_addresses => ? )
call"_SYS_BIC"."WorkShop.sessiona.00.models/get_bp_addresses_by_role_ce"(
partnerrole =>'02', bp_addresses => ? )
执行结果304 ms 427µs VS 690 ms 49µs
因采用CloudShare的服务器,速度比较慢,貌似纯SQL写的Procedure胜出了,CE写的Procedure速度偏慢。
PS:CE封装了很多的内置函数,如下