[MDEV-25838] FUNCTION parameters slow execution time dramatically despite DETERMINISTIC Created: 2021-06-01  Updated: 2021-06-09

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Procedure
Affects Version/s: 10.3.27
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Edouard Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: functions, performance
Environment:

Debian Buster


 Description   

I know it may not be a bugg.

Here what I observed:

CREATE FUNCTION GetName() RETURNS VARCHAR(128)
 
DETERMINISTIC
 
BEGIN 
RETURN (SELECT name FROM MyVIEW WHERE id1=26 AND id2=30);
 
END
 
SELECT GetName();

Execution last few ms.

CREATE FUNCTION GetName(id_1 INT, id_2 INT) RETURNS VARCHAR(128)
 
DETERMINISTIC
 
BEGIN 
RETURN (SELECT name FROM MyVIEW WHERE id1=id_1 AND id2=id_2);
 
END
 
SELECT GetName(26, 30);

It last more than 8 sec.

This view is full DETERMINISTIC. What can explain such a gap? Any ways to avoid this?

Regards,



 Comments   
Comment by Edouard [ 2021-06-03 ]

Wouldn't be possible to create a kind of "macro" type function?

PREPARE, EXECUTE statement are forbidden in FUNCTION. I know it's sub-optimal. But in this particular case, something like:

CREATE FUNCTION GetName(id_1 INT, id_2 INT) RETURNS VARCHAR(128)
 
MACRO
 
BEGIN 
RETURN EXECUTE IMMEDIATE CONCAT('SELECT name FROM MyVIEW WHERE id1=',id_1,' AND id2=',id_2);
 
END

would be faster.

Regards,

Comment by Edouard [ 2021-06-09 ]

Hi guys,

If you are here and find this situation crazy. Here is another crazy workaround I didn't find documentation for.

CREATE FUNCTION GetName(id_1 INT, id_2 INT) RETURNS VARCHAR(128)
 
DETERMINISTIC
 
BEGIN 
SET @id_1 = id_1
 
SET @id_2 = id_2
 
RETURN (SELECT name FROM MyVIEW WHERE id1=@id_1 AND id2=@id_2);
 
END
 
SELECT GetName(26, 30);

It get executed in few ms.

I would be so happy to meet anyone that could explain me why.

Regards,

Comment by Alice Sherepa [ 2021-06-09 ]

Could you please add the full test case to demonstrate the problem?
I tried with some simple table and view and getting ~the same execution time in both cases.

MariaDB [test]> create table t1 (name varchar(128), id1 int, id2 int);
 
Query OK, 0 rows affected (0.009 sec)
 
 
 
MariaDB [test]> insert into t1 select rand()*1000, rand()*1000, rand()*1000 from seq_1_to_1000000;
 
Query OK, 1000000 rows affected (2.994 sec)
 
Records: 1000000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> create view v1 as select * from t1;
 
Query OK, 0 rows affected (0.003 sec)
 
 
 
MariaDB [test]> SELECT name FROM v1 WHERE id1=688 AND id2=537
 
    -> ;
 
+--------------------+
 
| name               |
 
+--------------------+
 
| 300.42516561264654 |
 
+--------------------+
 
1 row in set (0.378 sec)
 
 
 
MariaDB [test]> CREATE FUNCTION f1(id_1 INT, id_2 INT) RETURNS VARCHAR(128) DETERMINISTIC 
    ->     RETURN (SELECT name FROM v1 WHERE id1=id_1 AND id2=id_2);
 
Query OK, 0 rows affected (0.002 sec)
 
 
 
MariaDB [test]> CREATE FUNCTION f2() RETURNS VARCHAR(128) DETERMINISTIC 
    ->     RETURN (SELECT name FROM v1 WHERE id1=688 AND id2=537);
 
Query OK, 0 rows affected (0.002 sec)
 
 
 
MariaDB [test]> SELECT f1(688, 537);
 
+--------------------+
 
| f1(688, 537)       |
 
+--------------------+
 
| 300.42516561264654 |
 
+--------------------+
 
1 row in set (0.381 sec)
 
 
 
MariaDB [test]> SELECT f2();
 
+--------------------+
 
| f2()               |
 
+--------------------+
 
| 300.42516561264654 |
 
+--------------------+
 
1 row in set (0.379 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.3.27-MariaDB |
 
+-----------------+
 
1 row in set (0.001 sec)

Comment by Edouard [ 2021-06-09 ]

Well how could we proceed? It's a production DB and we have 500 lignes of view source code we would prefer not to share.

SELECT final_nom FROM eg_product_final_lang;
 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| final_nom                                                                                                                                                                                                                            |
 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 3572 lines                                                                                                                                                                                             |
 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
3572 rows in set (9.526 sec)
 
 
 
SELECT final_nom FROM eg_product_final_lang WHERE id_lang = 5;
 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| final_nom                                                                                                                                                                                                                            |
 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 1786 lines                                                                                                                                                                                             |
 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1786 rows in set (4.814 sec)
 
 
 
SELECT final_nom FROM eg_product_final_lang WHERE id_product = 105;
 
+-----------------------------------+
 
| final_nom                         |
 
+-----------------------------------+
 
| Sandale à imprimé Libertie Nice   |
 
| Sandale à imprimé Libertie Nice   |
 
+-----------------------------------+
 
2 rows in set (0.032 sec)
 
 
 
SELECT final_nom FROM eg_product_final_lang WHERE id_lang = 5 AND id_product = 105;
 
+-----------------------------------+
 
| final_nom                         |
 
+-----------------------------------+
 
| Sandale à imprimé Libertie Nice   |
 
+-----------------------------------+
 
1 row in set (0.030 sec)
 
 
 
SELECT product_name(105, 5);
 
+-----------------------------------+
 
| product_name(105, 5)              |
 
+-----------------------------------+
 
| Sandale à imprimé Libertie Nice   |
 
+-----------------------------------+
 
1 row in set (10.933 sec)
 
 
 
SELECT product_name_with_local_var(105,5);
 
+------------------------------------+
 
| product_name_with_local_var(105,5) |
 
+------------------------------------+
 
| Sandale à imprimé Libertie Nice    |
 
+------------------------------------+
 
1 row in set (0.039 sec)

It looks like when not using the local var in the function, a SELECT * is done and then the filter, while in the other case the view execution is optimized.

Regards,

Generated at Thu Feb 08 09:40:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.