Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.27
-
None
-
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,
Attachments
Activity
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,
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)
|
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,
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:
MACRO
BEGIN
END
would be faster.
Regards,