Skip to Content
0
Mar 31, 2017 at 05:42 PM

Scalar Function Memory Allocation - Poor Performance

712 Views Last edit Mar 31, 2017 at 05:43 PM 2 rev

Hi all,

I am doing tests using Scalar Functions and I realize that using this type of function my SQL queries get much slower. To try to understand the reasons for this poor performance I created two scenarios to compare.

I'm using TABLE_1 whith 6k records.

SELECT FIELD1, FIELD2, (SELECT 4 FROM DUMMY) AS VLR  FROM "TABLE_1" ;

and the plan of above SQL

To compare, I create a simple Scalar Fucntion as follow:

FUNCTION "MY_SCHEMA"."comp.test.functions::return_value" (P_1 NUMBER ) 
	RETURNS RESULT NUMBER
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN


 DECLARE V_V1  NUMBER;
 
 SELECT :P_1 INTO V_V1 FROM DUMMY;
 
 RESULT := V_V1;
 
END;
SELECT FIELD1, FIELD2,"MY_SCHEMA"."comp.test.functions::return_value" (3 ) AS VLR FROM "TABLE_1"

and the plan of above SQL

Perceive the difference between the execution times, compilation and the volume of memory allocated.

Why Scalar Function are so slow?

Why does Scalar Function allocate so much memory? (14GB in second image)

Thanks

Attachments

plan1.png (20.8 kB)
plan2.png (34.0 kB)