|
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
|