INNER JOIN implementation

Question

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 19-Feb-19 14:53pm

MyOldAccount

Updated 20-Feb-19 4:48am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gerry Schmitz · Answer 1 · 2019-02-20T04:49:00

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[^]