Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-650

stored procedures with queries invoked over odbc prepared statements take very long

    Details

    • Sprint:
      2017-6, 2017-7

      Description

      Install dbt3 data (i had 5g data set). Create stored procedure:

      MariaDB [dbt3]> delimiter //
      MariaDB [dbt3]> create procedure proctest() begin select l_shipmode, count(*) from lineitem group by 1; end; //
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [dbt3]> delimiter ;
      MariaDB [dbt3]> call proctest();
      +------------+----------+
      | l_shipmode | count(*) |
      +------------+----------+
      | AIR        |  4285543 |
      | MAIL       |  4282860 |
      | REG AIR    |  4285596 |
      | TRUCK      |  4288377 |
      | SHIP       |  4285381 |
      | FOB        |  4287168 |
      | RAIL       |  4284870 |
      +------------+----------+
      7 rows in set (1.67 sec)
      

      Now invoke this using ODBC using the mariadb 2.0 connector. I used https://odbcconnect.codeplex.com/. Invoke the same stored procedure call, it will take a long time. Now invoke it with mysql 5.3 odbc driver, it'll be in the same ballpark as mysql client.

      MariaDB ODBC driver always uses binary prepared statement protocol where mysql odbc uses straight statements by default. Theoretically this might reproduce in other client libraries using prepared statements for making the call but i ran into problems with making this work in php. I could try in java but php is probably preferable for a regression test.

      When i first tried to reproduce this i used a small data set and so it didn't seem like a problem, so i suspect it is doing something like re-running the query for each row returned or something like that which needs a larger query to see a material difference.

      Workaround for now is to use the mysql odbc driver and possibly not use prepared statements for other connectors (to be confirmed).

        Attachments

          Activity

            People

            • Assignee:
              dleeyh Daniel Lee
              Reporter:
              dthompson David Thompson
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: