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

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: