Skip to Content
0
Former Member
Apr 28, 2016 at 12:14 PM

Strange procedure performance issue

23 Views

Hi!

I am new to HANA, maybe my question is obvious, but I can resolve it.

So there is this built in SP:

CREATE PROCEDURE SAP_HANA_DEMO."sap.hana.democontent.epm.Procedures::get_bp_addresses_by_role" (

IN im_partnerrole NVARCHAR(3),

OUT ex_bp_addresses SAP_HANA_DEMO."sap.hana.democontent.epm.data::Procedures.tt_bp_addresses")

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

--DEFAULT SCHEMA <default_schema_name>

READS SQL DATA AS

BEGIN

/*****************************

Write your procedure logic

*****************************/

ex_bp_addresses =

select a."PARTNERID", a."PARTNERROLE", a."EMAILADDRESS", a."COMPANYNAME",

a."ADDRESSES.ADDRESSID" as "ADDRESSID" , b."CITY", b."POSTALCODE", b."STREET"

from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.BusinessPartner" as a

inner join "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Addresses" as b

on a."ADDRESSES.ADDRESSID" = b."ADDRESSID"

where a."PARTNERROLE" = '1';

END;

And I test this select by these 2 methods read below:

1./ CALL "SAP_HANA_DEMO"."sap.hana.democontent.epm.Procedures::get_bp_addresses_by_role"('1',NULL)

with this result:

successfully executed in 5:40.568 minutes (server processing time: 5:40.578 minutes)

Fetched 39 row(s) in 6 ms 276 µs (server processing time: 0 ms 212 µs)

2./ Running the select directly

select a."PARTNERID", a."PARTNERROLE", a."EMAILADDRESS", a."COMPANYNAME",

a."ADDRESSES.ADDRESSID" as "ADDRESSID" , b."CITY", b."POSTALCODE", b."STREET"

from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.BusinessPartner" as a

inner join "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Addresses" as b

on a."ADDRESSES.ADDRESSID" = b."ADDRESSID"

where a."PARTNERROLE" = '1';

with this result:

successfully executed in 4 ms 278 µs (server processing time: 1 ms 131 µs)

Fetched 39 row(s) in 2 ms 718 µs (server processing time: 0 ms 86 µs)

Result table is the same!

What can cause this huge difference in performance?

Thanks, in advance!

Peter