on 04-28-2016 1:14 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.