Click here to Skip to main content
14,984,060 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
PROBLEM:

I need to perform INNER JOIN between 2 SQL queries, but that query would have to access 2 MySQL databases located on their own servers.

Small example might explain better the problem I face:
SQL
select * 
from Table1,        -- Table1 is located on server 72.93.200.11
INNER JOIN Table2   -- Table2 is located on server 109.93.1.219
on Table1.Id = Table2.Id;

RELEVANT INFORMATION:
  • MFC and ODBC are used for database access
  • I haven't used ODBC, nor MFC for database programming before
  • application is a legacy one, so I can not use C++ 11 or newer
  • Visual Studio 2008 is used

If further information is required please leave a comment.

QUESTION:

If using m_strFilter[^] with the second CRecordset is possible, can you instruct me how to do it (again, I have no prior experience with MFC and ODBC)?

I will accept C++ solution as well, but remember that I may not use C++ 11 or newer, since the application is a legacy one.

What I have tried:

This SO post[^] suggest usage of FEDERATED ENGINE.

I am reluctant to use this approach since it has poor performance, according to various comments in the post.

Other option would be to perform both queries separately, and filter recordsets in code.

I have successfully executed separate queries, but m_strFilter accepts fixed string only, not CRecordset.

While writing this question, I am trying to figure out how to bypass the above limitation.
Posted
Updated 20-Feb-19 4:48am
Comments
CHill60 20-Feb-19 4:12am
   
The performance concerns are over large inserts or table reads. If Id is indexed then you shouldn't have too great a problem - see https://dev.mysql.com/doc/refman/8.0/en/federated-usagenotes.html.
Are you aware of the error in your code snippet? There should be no comma after table1
Stefan_Lang 20-Feb-19 4:17am
   
While I can't help on the exact implementation, I see no reason at all why you couldn't use C++ - unless otheres need to be able to work with your code and for some reason are using outdated compilers.
Stefan_Lang 20-Feb-19 4:29am
   
I'm not familiar with 'federated tables', but judging by the SO comments a better solution would be to split up that join into several queries like this:
1. Select unique list of 'Id's from Table1 from the first server
2. Select * from Table2 (from server 2) where Id is in (first select result list)
3. Select Id from (step 2 result list)
4. Select * from Table1 where Id is in (step 3 result list)
5. Join data from steps 2 and 4 locally

This limits the amount of data that needs to be transferred to a minimum.

An alternative would be stored procedures that basically perform the same steps on either of the servers. This would have the advantage that you don't need to transfer results from intermediate steps to your client, only the end results. (plus the performance is probably much better than on your client)

1 solution

You create 2 connections in your program; one to each server.

Query one; then query the other using the results of the first.

Of course, this requires thinking beyond "join all".

SQL Servers support "distributed queries". You can get to MySQL via ODBC from SQL Server.

Linked servers and distributed queries | SQL Bad Practices[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900