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