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

CONNECT Engine - ODBC: SRCDEF Views Performance

    XMLWordPrintable

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

              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.