Scalar functions slowing down performance of stored procedures?

Help
1

Hi, I have a stored procedure that gets called from a loop and inside it executes SCALAR three times in each iteration for something similar to this:

num_of_ids = SCALAR(CONCAT('SELECT COUNT(1) FROM test_table WHERE test_id=', v_test_id), QUERY(a INT));

The whole procedure takes a lot of time because it handles a large amount of data, but I’ve encountered many blogs where people talk how Scalar UDFs in general lower performance significantly because query optimisation does not work on them, as well as some other reasons that I do not fully understand yet :smiley:

Though the most of the blogs I’ve read this on are related to SQL Server, I’m starting to doubt my use of Scalar and if that is the case with SingleStore engine as well. Any information is welcome, as well as if this can be improved in some other way.

I’m stumbling in the dark a bit, but in some blogs they said that instead of SCALAR you should use TVFs. I’ve created the TVFs, but I’m still not sure how to use them inside the stored procedure since SELECT is not allowed…

Thanks!

2

That pattern you showed does not necessarily have to be slow. But it is using dynamic SQL so will have to re-parse the query every time, though since the query shape is standardized, it may not have to recompile every time.

Consider instead using SELECT INTO a local variable, and also a local variable reference in your query instead of CONCAT. Like this:

create table t(a int, b int);
insert t values(1,2),(3,4),(3,5);
       
delimiter //
do 
declare x int;
declare f int; 
begin
f = 3;
select count(1) into x from t where a = f;
echo select x;
end 
//      
delimiter ;

+------+
| x    |
+------+
|    2 |
+------+
1 row in set (0.03 sec)

Even so, I’m not sure this will make a difference, So consider using Query History · SingleStore Documentation
to make sure you really know what is taking the most time.