Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25838

FUNCTION parameters slow execution time dramatically despite DETERMINISTIC

Details

    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

          Gaulué Edouard added a comment -

          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,

          Gaulué Edouard added a comment - 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,
          Gaulué Edouard added a comment -

          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,

          Gaulué Edouard added a comment - 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,
          alice Alice Sherepa added a comment -

          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)
          

          alice Alice Sherepa added a comment - 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)
          Gaulué Edouard added a comment -

          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,

          Gaulué Edouard added a comment - 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,

          People

            Unassigned Unassigned
            Gaulué Edouard
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.