CODEWITHSUNDEEP
sqlsql-servert-sqljoinoptimization
Ondřej Rehák
Ondřej Rehák

Reputation: 603

Optimizing SQL Query - Joining 4 tables

I am trying to join 4 tables. Currently I've achieved it by doing this. 

    SELECT columns
    FROM tableA
    LEFT OUTER JOIN tableB ON tableB.address_id = tableA.address_id
    INNER JOIN tableC ON  tableC.company_id = tableA.company_id AND tableC.client_id = ?
    UNION
    SELECT columns
    FROM tableA
    LEFT OUTER JOIN tableB ON tableB.address_id = tableA.gaddress_id
    INNER JOIN tableD ON tableD.company_id = tableA.company_id AND tableD.branch_id = ?

The structure of tableC and tableD is very similar. Let's say that tableC contains data for clients. And tableD contains data for client's branch. tableA are companies and tableB are addresses My goal is to get data from tableA that are joined to table B (All companies that has addresses) and all the data from tableD and also from tableC.

This wroks nice, but I am afraid that is would be very slow.

Upvotes: 1

Views: 372

Answers (3)

Sam
Sam

Reputation: 910

Try CTE so that you don't have to go through TableA and TableB twice for the union.

; WITH TempTable (Column1, Column2, ...)
    AS (    SELECT columns
            FROM tableA
            LEFT OUTER JOIN tableB
                 ON tableB.address_id = tableA.gaddress_id
        )
SELECT Columns
FROM    TempTable
INNER JOIN tableC 
    ON  tableC.company_id = tableA.company_id AND tableC.client_id = ?
UNION
SELECT Columns
FROM    TempTable
INNER JOIN tableD ON tableD.company_id = tableA.company_id AND tableD.branch_id = ?

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Why should that be slow? You select client adresses and branch addresses and show the complete result. That seems straight-forward.

You join on IDs and this should be fast (as there should be indexes available accordingly). You may want to introduce composite indexes on

create index idx_c on tableC(client_id, company_id)

and

create index idx_d on tableD(branch_id, company_id)

However: UNION is a lot of work for the DBMS, because it has to look for and eliminate duplicates. Can there even be any? Otherwise use UNION ALL.

Upvotes: 0

user6468499
user6468499

Reputation:

I think you can trick it like this:

First UNION between C,D and only the join to the rest of the query, it should improve the query significantly :

SELECT columns
FROM TableA
LEFT OUTER JOIN tableB ON tableB.address_id = tableA.address_id
INNER JOIN(SELECT Columns,'1' as ind_where FROM tableC
           UNION ALL
           SELECT Columns,'2' FROM TableD) joined_Table
 ON  (joined_Table.company_id = tableA.company_id AND joined_Table.New_Col_ID= ?)

The New_Col_ID -> just select both branch_id and client_id in the same column and alias it as New_Col_ID or what ever

In addition you can index the tables(if not exists yet) :

TableA(address_id,company_id)
TableB(address_id)
TableC(company_id,client_id)
TableD(company_id,branch_id)

Upvotes: 3

Related Questions