cancel
Showing results for 
Search instead for 
Did you mean: 

Strange procedure performance issue

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

chandan_praharaj
Contributor
0 Kudos

It is a bit strange ...

if you have simple view , can you try creating a separate procedure with direct table as join criteria and share the stats please.

Former Member
0 Kudos

Hi!

Sources are tables, not views. As I can see, the only difference is that, simple select gives back a simple recordset, but the procedure creates a temporary table, or something like that with this type:

     "sap.hana.democontent.epm.data::Procedures.tt_bp_addresses"

This "table"(called ex_bp_addresses) is the OUT parameter of the procedure, and this is the result of the call proc statement.

Anyway i dont think it is an explanation of this bad performance.

I tried some other simple procedures too, and performance is always poor. Can be any general reason of it?

There are some other strange issues:

- session with selecting a table with some thousand of records freezes, or takes minutes

- copying a user takes minutes

- and so on

Thanks in advance!

Peter

lbreddemann
Active Contributor
0 Kudos

This is not standard behavior and should be analyzed in depth.

I recommend to open a support incident.

SergioG_TX
Active Contributor
0 Kudos

that is a huge difference...

I do notice in your join condition.. a."ADDRESSES.ADDRESSID" = b."ADDRESSID"

is the join condition correct on your a alias ?

how is your network? are you on vpn - even if so, there shouldn't be a huge difference.

have you tried to run the plan visualizer to see what is happening when executing the stored proc?

in your sql console, highlight the call statement, right click.. visualize plan / explain

it will tell how where the time is being spent/how many rows are returning from any sql operations.. similarly run the plan visualizer on your select statement to see why that one is so much faster

Former Member
0 Kudos

Dear Sergio!

Yes, join condition is correct. SP runs (very slowly but runs) without error. Table A has a field call "ADDRESSES.ADDRESSID", table B has a column call ADDRESSID. The SELECT statement above, and the SELECT statement built in the SP is the same.

Last week I hade simple wifi connection in our office, now i tried on VPN. But the difference is almost the same.

Yes I tried visualizer, now I repeated it. Result of SP run visualization is attached:

As you can see, executing model takes 60sec. But I still dont know why? I think no any real difference in performance shouldn't appear.

Thanks, in advance,

Peter