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

CONNECT Engine - ODBC: SRCDEF Views Performance

Details

    Description

      CONNECT » ODBC Engine's Documentation states in section "Accessing specified views"
      that when Creating CONNECT - ODBC Tables which access to views in the foreign server, WHERE clause is performed locally, instead of in the foreing server accessed via ODBC (As it would be done by default).

      MariaDB Documentation » CONNECT » ODBC

      Therefore, I would like to suggest that WHERE is also performed in the "Foreing Server", as it allows:

      • To reduce the data which is sent by the data source, preventing to have to send the hole table though the network for being able to filter it.

      An example would be as follows:

      CREATE TABLE custnum 
      ENGINE=CONNECT 
      TABLE_TYPE=ODBC 
      CONNECTION='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;'
      SRCDEF=
      'select country, count(*) as customers from customers group by country';
      

      SELECT
      *
      FROM
      custnum
      WHERE
      country IN (SELECT country FROM mylocalcountries);
      

      or

      SELECT
      *
      FROM
      custnum
      WHERE
      country = 'UK';
      

      Maybe this could be achieved by creating a "derived table" in the Foreing Server (which contains the View), to which the WHERE clause is applied afterwards.

      Thank you

      Hope my suggestion is useful!

      Attachments

        Issue Links

          Activity

            Currently WHERE clause with functions are not kept with ODBC and JDBC but they are kept and executed remotely by MYSQL tables; functions being the same. IN clauses are also eligible for remote execution.

            About SRCDEF tables, I realized that the problem is far more complex than expected when SRCDEF contains aliases, joins and/or GROUP BY clauses. I am working at a more general solution, but this is not trivial and will take time.

            bertrandop Olivier Bertrand added a comment - Currently WHERE clause with functions are not kept with ODBC and JDBC but they are kept and executed remotely by MYSQL tables; functions being the same. IN clauses are also eligible for remote execution. About SRCDEF tables, I realized that the problem is far more complex than expected when SRCDEF contains aliases, joins and/or GROUP BY clauses. I am working at a more general solution, but this is not trivial and will take time.
            Juan Juan Telleria added a comment -

            Thank you

            Juan Juan Telleria added a comment - Thank you
            Juan Juan Telleria added a comment -

            What about CONNECT Engine internally creating a derived table:

            SELECT
                 *
            FROM
                 (
                      SELECT
                           *
                      FROM
                           t_Table
                 ) AS `t_SRCDEF`
            WHERE
                 [...]
            HAVING
                 [...]
            

            Juan Juan Telleria added a comment - What about CONNECT Engine internally creating a derived table: SELECT * FROM ( SELECT * FROM t_Table ) AS `t_SRCDEF` WHERE [...] HAVING [...]

            This now implemented and documented. This was fixed some time ago and should be available in current versions.

            bertrandop Olivier Bertrand added a comment - This now implemented and documented. This was fixed some time ago and should be available in current versions.
            Juan Juan Telleria added a comment -

            Thank you

            Juan Juan Telleria added a comment - Thank you

            People

              bertrandop Olivier Bertrand
              Juan Juan Telleria
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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