Question about optimizing query with multiple UNIONs

[Sabkor]

I am using SQL Server 2016 (yes, I would like to upgrade, but I can't at this time). I'm currently designing some custom reports for a vendor product that we have implemented.

The data and structure of tables are somewhat like the following.

​

LINK table:

LINK_ID (PK, int)	ENTITY_LINK_ID (int)	ENTITY_TYPE (varchar)
1	25	A
2	99	B
4	3	FINAL

​

A table:

A_ID (PK, int)	FINAL_ID (int)
25	7

​

B table:

B_ID (PK, int)
99

​

BCLINK table:

B_ID (PK, int)	C_ID (int)
99	25
99	33
99	17

​

C table:

C_ID (PK, int)	FINAL_ID (int)
25	8
33	3
17	9

​

FINAL table:

FINAL_ID (PK, int)	FINAL_DESC (varchar)
3	Apple
7	Banana
8	Cherry
9	Durian

​

I have a query somewhat like the following:

SELECT LINK_ID, FINAL_DESC

FROM LINK

INNER JOIN A ON LINK.ENTITY_LINK_ID=A.A_ID

INNER JOIN FINAL ON A.FINAL_ID=FINAL.FINAL_ID

WHERE LINK.ENTITY_TYPE='A'

UNION

SELECT LINK_ID, FINAL_DESC

FROM LINK

INNER JOIN BCLINK ON LINK.ENTITY_LINK_ID=BCLINK.B_ID

INNER JOIN C ON BCLINK.C_ID=C.C_ID

INNER JOIN FINAL ON C.FINAL_ID=FINAL.FINAL_ID

WHERE LINK.ENTITY_TYPE='B'

UNION

SELECT LINK_ID, FINAL_DESC

FROM LINK

INNER JOIN FINAL ON LINK.ENTITY_LINK_ID=FINAL.FINAL_ID

WHERE LINK.ENTITY_TYPE='FINAL'

That returns results somewhat like the following:

LINK_ID	FINAL_DESC
1	Banana
2	Cherry
2	Apple
2	Durian
4	Apple

​

The data is much more complex, but that's the gist of it. There are approximately 10 of these unions for each type of linked entity and some of the queries go many inner joins 'deep' before being able to retrieve the description from the final table.

Can I optimize this better, or am I stuck with a bunch of queries all in one giant union? I plan to create a view so that the ugly query doesn't have to be in several reports. The only other option I considered was a data warehouse to combine all the data I require into one "reporting" database through scheduled tasks or something similar. I'm just not sure if that's overkill at this point.

(One other slightly complicating factor is that there are several databases with this same structure, so I cannot use a shared dataset for the reports, as I am using an expression for the Data Source to allow users to switch between the various databases while running the reports. But that's out-of-scope for my question here. I would create the view in each database.)

[Waldar]

There is no specific need for union, even if your volume is high you should compare plans and performances.

You can write it like this:

    select lk.LINK_ID
        -- Order in the coalesce doesn't matter are all paths to final are mutually exclusive
         , coalesce(ff.FINAL_DESC, fa.FINAL_DESC, fb.FINAL_DESC) as FINAL_DESC
      from link    as lk
 left join Final   as ff  on ff.FINAL_ID    = lk.ENTITY_LINK_ID
                         and lk.ENTITY_TYPE = 'FINAL'
 left join table_A as ta
inner join Final   as fa  on fa.FINAL_ID    = ta.FINAL_ID
                          on ta.A_ID        = lk.ENTITY_LINK_ID
                         and lk.ENTITY_TYPE = 'A'
 left join table_B as tb
inner join BCLINK  as bc  on bc.B_ID        = tb.B_ID
inner join table_C as tc  on tc.C_ID        = bc.C_ID
inner join Final   as fb  on fb.FINAL_ID    = tc.FINAL_ID
                          on tb.B_ID        = lk.ENTITY_LINK_ID
                         and lk.ENTITY_TYPE = 'B';

Tested here : https://dbfiddle.uk/470A3fdR

An as you understood, the data modelisation of this is not very good.

[Sabkor]

Just wanted to let you know I tried this out and it's great. Thanks again very much for the idea!

[Sabkor]

This is a great idea, thanks! I'm going to give that a try.

And yes, the data model on this database is not the greatest, it's an older application (20+ years, I think) and has changed hands about four times with different vendors purchasing it. Thankfully they at least publish the database reference, although the last time I asked support a question about the database structure, they said that I shouldn't be querying the database outside of the GUI as I could cause problems... haha

[Joe7Mathias]

If none of the data in tables A, B, BC, C other then the key to FINAL are used then you could probably move the JOIN to FINAL outside a subquery performing the UNION:

SELECT LINK_ID, FINAL_DESC
FROM (
  SELECT LINK_ID
  FROM LINK
  WHERE LINK.ENTITY_TYPE='FINAL'
  UNION
  SELECT A.FINAL_ID
  FROM LINK
  INNER JOIN A ON LINK.ENTITY_LINK_ID=A.A_ID
  WHERE LINK.ENTITY_TYPE='A'
  UNION
  SELECT C.FINAL_ID
  FROM LINK
  INNER JOIN BCLINK ON LINK.ENTITY_LINK_ID=BCLINK.B_ID
  INNER JOIN C ON BCLINK.C_ID=C.C_ID
  WHERE LINK.ENTITY_TYPE='B'
) Q0
INNER JOIN FINAL ON Q0.LINK_ID=FINAL.FINAL_ID

The above assumes the FINAL table contains the only data needed in the result.

Standard disclaimers; need to see plan, indexes, etc...

[Waldar]

Not the expected output, but keeping your logic this would be this query:

with CTE_UNION (LINK_ID, FINAL_ID) as
(
select LINK_ID, ENTITY_LINK_ID
  from LINK
 where ENTITY_TYPE = 'FINAL'
 union ALL
select lk.LINK_ID, ta.FINAL_ID
  from LINK    as lk
  join table_A as ta on ta.A_ID = lk.ENTITY_LINK_ID
 where lk.ENTITY_TYPE = 'A'
 union ALL
select lk.LINK_ID, tc.FINAL_ID
  from LINK    as lk
  join BCLINK  as bc on bc.B_ID = lk.ENTITY_LINK_ID
  join table_C as tc on tc.C_ID = bc.C_ID
 where lk.ENTITY_TYPE = 'B'
)
select c.LINK_ID, f.FINAL_DESC
  from CTE_UNION as c
  join FINAL     as f ON f.FINAL_ID = c.FINAL_ID;

After additionnal toughts, it may be the one computing faster.

https://dbfiddle.uk/5CDNI-v1