Comments - Data limit on joins with connect tables?

10 years ago Olivier Bertrand

Perhaps you have not a recent version of connect or because the on line CONNECT documentation is outdated, but there another possibility to join remote tables when they belong to the same data source. It is to create a table that make the remote server to the join. Here is an example:

create table categories engine=connect table_type=ODBC block_size=10 Connection='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

create table products engine=connect table_type=ODBC block_size=10 Connection='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;';

If you do:

select productid, productName, p.categoryid, categoryname, description from products p natural join categories;

MariaDB has to retrieve the relevant parts of the two tables and make the join locally. This is not good performance wise (except for small tables) and can be limited by the used storage. To have the join done by the data source you can do:

create table prodcat engine=connect table_type=ODBC block_size=10 Connection='DSN=MS Access Database;DBQ=C:/Program Files/Microsoft Office/Office/1033/FPNWIND.MDB;' srcdef='select p.ProductID, p.ProductName, p.CategoryID, c.CategoryName, c.Description from products p inner join categories c on p.CategoryID=c.CategoryID';

This table is based on a query specified by the SRCDEF option that will be executed remotely by the data source (note that the syntax must be the data source syntax) Now you can get the result of the join by:

select * from prodcat;

In your case, perhaps this will avoid loosing result rows.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.