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

CONNECT Engine - ODBC Table Views: Efficience Improval

    Details

      Description

      Hi!

      I have configured a connection between SQL Server and MariaDB via ODBC. However, tables in SQL Server have huge size (millions and millions of records), and therefore, the transfer via ODBC of the hole tables for being able to filter them is rather inefficient.

      I try to use Remote Indexing whenever it's possible (Great feature by the way) with regular SELECTs, but in this case it was not possible because of the complex joins the query required. Therefore, I opted to use CONNECT ODBC Tables with Views, by using SRCDEF clause in table_options, being able to modify such View via a prepared statement.

      However, the current development of CONNECT Engine for ODBC Tables for Views has the following limitations:

      • It forces you to transfer the hole table created in the View (SRCDEF) Clause for being able to filter it. Not feasible when working with millions of records.
      • And SRCDEF has a limit of characters, so I can not create "View Definitions" as long as I want with IN clauses.

      My example is the following:

       SET @@group_concat_max_len = 18446744073709547520;
       
      SELECT
            GROUP_CONCAT(DISTINCT CONCAT("'", MachineUT, "'") SEPARATOR ', ')
            INTO @Var_CONNECT_MachineUT_Current_Population
      FROM
            scada_data_processing.`t0_windone_wtg_envelopes_population`;
          
      SET @@group_concat_max_len = 4000;
       
      SELECT 
          CONCAT("'", MIN(DateTimeOFF), "'") AS `WTG Population's First DateTimeOFF`,
          CONCAT("'", MAX(DateTimeOFF), "'") AS `WTG Population's Last DateTimeOFF`
      INTO 
          @Var_CONNECT_MIN_DateTimeOFF_Population,
          @Var_CONNECT_MAX_DateTimeOFF_Population
      FROM
          scada_data_processing.`t0_DISPOIN_imported_data`;
       
      SET @Var_CONNECT_Create_EO_OTS_STOPS_Table =
      CONCAT('
      CREATE TABLE  scada_data_processing.`t0_temp_aux_eo_ots_stops_subset_population` 
      (
         `NumOT` varchar(36) NOT NULL,
         `OTDescription` varchar(200) DEFAULT NULL,
         `UT` varchar(120) DEFAULT NULL,
         `OTTypeCode` varchar(20) DEFAULT NULL,
         `Subtipo` varchar(120) DEFAULT NULL,
         `LCNId` varchar(6) DEFAULT NULL,
         `StopStartDate` datetime(6) NOT NULL,
         `StopEndDate` datetime(6) NOT NULL,
         `CauseCode` varchar(12) NOT NULL
      ) ENGINE=CONNECT
        TABLE_TYPE=ODBC
        CONNECTION="exp_operativa"  
        OPTION_LIST="UseDSN=Yes,User=***********,Password=*************"
        SRCDEF =
      "
        SELECT
           A.[NumOT],
           [OTDescription],
           [UT],
           [OTTypeCode],
           [Subtipo],
           [LCNId],
           [StopStartDate],
           [StopEndDate],
           [CauseCode]
      FROM
           exp_operativa.GPA99.T_OTS AS A
           LEFT JOIN exp_operativa.GPA99.T_STOPS AS B
                ON A.[NumOT] = B.[NumOT]
           LEFT JOIN exp_operativa.GPA99.T_MACHINES AS C
                ON A.[UT] = C.[MachineUT]
      WHERE
           (A.[UT] IN (', @Var_CONNECT_MachineUT_Current_Population, '))
           AND ([StopStartDate] IS NOT NULL)
           AND ([StopStartDate] BETWEEN 
                CONVERT(DATETIME,', @Var_CONNECT_MIN_DateTimeOFF_Population, ')
                AND CONVERT(DATETIME,', @Var_CONNECT_MAX_DateTimeOFF_Population, '))
           AND 
                ([StopEndDate] <= DATEADD(HOUR, 24, CONVERT(DATETIME,',                
                @Var_CONNECT_MAX_DateTimeOFF_Population, ')))
           AND (A.[Enabled] = 1 AND B.[Enabled] = 1 AND C.[Enabled] = 1)
           AND (ISNULL([MainStartDate], CAST(-53690 as DATETIME)) <= [StopStartDate])
           AND ([StopEndDate] <= ISNULL([MainEndDate], CAST(2958463 as DATETIME)))
      "');
       
      PREPARE Prepare_Statement_EO_OTS_Subset_Population 
            FROM @Var_CONNECT_Create_EO_OTS_STOPS_Table;
              
      DROP TABLE IF EXISTS    scada_data_processing.`t0_temp_aux_eo_ots_stops_subset_population`;
       
      EXECUTE Prepare_Statement_EO_OTS_Subset_Population;
       
      DEALLOCATE PREPARE Prepare_Statement_EO_OTS_Subset_Population;
      

      Some interesting solutions could be:

      • Increase the SRCDEF table option word limit.
      • Or change the behaviour of the WHERE clause when working with Views, so that the WHERE clause is also performed in the Remote Server.

      Thank you.

      Kind regards,
      Juan

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: