How to avoid Multiple Tables Union All

Question

I have a table called TblVesselEngineSummary, which has a field called EngineConfiguration containing these values

'Single' - Single Engine
'Twin' - Double Engine - two records needed
'Triple' - Triple Engine - three records needed

In order to achieve it, I have doing union all to same table 3 times. it is impacting the performance, is there a better way to implement this?

I have EngineId as primary key, in order to make each entry unique, I adding 90000000 or 50000000.

SELECT * FROM (

Select
    EngineSummaryID AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Port' AS EngineLocation, EngineHoursPort AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '1' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y'

UNION ALL
--'Twin','Triple'
Select
    EngineSummaryID + 90000000 AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Starboard' AS EngineLocation, EngineHoursStarboard AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '2' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y' AND EngineConfiguration IN ('Twin','Triple')

UNION ALL
--'Triple'
Select
    EngineSummaryID + 50000000 AS EngineId, VesselID_PK AS EngineBoatId,
    
    'Starboard' AS EngineLocation, EngineHoursStarboard AS 'Hours', 
    strEngineManufacturer AS 'EngineMake', strEngineModel AS 'EngineModel', 
    strEngineType AS 'EngineType', EngineYear, 
    EngineHP AS 'PowerHP', SerialForPort AS 'SerialNumber', 
    '3' AS 'Sort', UpdatedOn 

from TblVesselEngineSummary 
WHERE IsActiveVesselSummary = 'Y' AND EngineConfiguration = 'Triple'


) allEngines  ORDER BY EngineBoatId, Sort

EngineConfiguration EngineSummaryID VesselID_PK EngineHoursStarboard strEngineManufacturer strEngineModel strEngineType EngineYear EngineHP SerialForPort UpdatedOn
Twin   27092 484405 2825             YANMAR JH57           InBoard  2020 57  5/14/24 3:35 PM
Twin   27090 441067 3351             Yanmar 4JH4-E         InBoard  2006 54  5/10/24 12:52 PM
Single 27080 431834                  MerCruiser 6.2L       InBoard  2008 300  5/7/24 2:43 PM
Twin   27078 495706 1466             Volvo Penta D2-30     InBoard  2019 30   5/7/24 12:58 PM
Triple 27052 496093 125 (Center) 127 YAMAHA 350TXR 350 TUR Outboard 2008 350 4/10/24 4:21 PM

Please add sample data and desired results so that its clear to us what you are trying to accomplish. But you can probably use a few case expressions to make that a single query. — Dale K
– Dale K, Commented May 19, 2024 at 22:33
Use a case expression for the columns that vary: Id and engine location, I think. — shawnt00
– shawnt00, Commented May 19, 2024 at 22:37
No sure how to share the data, I am pasting here: EngineConfiguration EngineSummaryID VesselID_PK EngineHoursStarboard strEngineManufacturer strEngineModel strEngineType EngineYear EngineHP SerialForPort UpdatedOn Twin 27092 484405 2825 YANMAR JH57 InBoard 2020 57 5/14/24 3:35 PM Twin 27090 441067 3351 Yanmar 4JH4-E InBoard 2006 54 5/10/24 12:52 PM Single 27080 431834 MerCruiser 6.2L InBoard 2008 300 5/7/24 2:43 PM Twin 27078 495706 1466 Volvo Penta D2-30 InBoard 2019 30 5/7/24 12:58 PM Triple 27052 496093 125 (Center) 127 YAMAHA 350TXR 350 TUR Outboard 2008 350 4/10/24 4:21 PM — Rajiv Ranjan
– Rajiv Ranjan, Commented May 19, 2024 at 22:51
Definitely not in a comment... there is a guide for how to use the editor, which includes how to display data. — Dale K
– Dale K, Commented May 20, 2024 at 0:19
Note that tables have rows and columns. (Not records or fields.) — jarlh
– jarlh, Commented May 20, 2024 at 7:18

T N · Accepted Answer · 2024-05-20 01:22:03Z

1

You can take the variable parts of your query and wrap them up in a cross apply, which can generate multiple variations in the data and can also apply the necessary conditionals.

Something like:

SELECT E.*, VES.*
FROM TblVesselEngineSummary VES
CROSS APPLY (
    SELECT
        VES.EngineSummaryID AS EngineId,
        'Port' AS EngineLocation,
        VES.EngineHoursPort AS Hours,
        1 AS 'Sort'
    UNION ALL
    SELECT
        VES.EngineSummaryID + 90000000 AS EngineId,
        'Starboard' AS EngineLocation,
        VES.EngineHoursStarboard AS Hours,
        2 AS 'Sort'
    WHERE VES.EngineConfiguration IN ('Twin','Triple')
    UNION ALL
    SELECT
        VES.EngineSummaryID + 50000000 AS EngineId,
        'Starboard' AS EngineLocation,
        VES.EngineHoursStarboard AS Hours,
        3 AS 'Sort'
    WHERE VES.EngineConfiguration = 'Triple'
) E
WHERE VES.IsActiveVesselSummary = 'Y'
ORDER BY VES.VesselID_PK, E.Sort

A CROSS APPLY is like an INNER JOIN to a subselect, also sometimes called a lateral join. For each row from the TblVesselEngineSummary table, the cross apply above will construct one to three sets of data that are effectively joined to the initial table. Note that the subselect accesses the outer TblVesselEngineSummary table reference, so data is retrieved only once.

(I'll let you work out the details for the final select list.)

Sample results (with some minimal fabricated test data):

EngineId	EngineLocation	Hours	Sort	VesselID_PK	EngineSummaryID	IsActiveVesselSummary	EngineConfiguration	EngineHoursPort	EngineHoursStarboard
111	Port	1000	1	1	111	Y	Single	1000	0
222	Port	2200	1	2	222	Y	Twin	2200	2250
90000222	Starboard	2250	2	2	222	Y	Twin	2200	2250
333	Port	3300	1	3	333	Y	Triple	3300	3350
90000333	Starboard	3350	2	3	333	Y	Triple	3300	3350
50000333	Starboard	3350	3	3	333	Y	Triple	3300	3350

See this db<>fiddle for a demo.

Should SerialNumber also be variable? All three subqueries reference SerialForPort. Is there a SerialForStarboard?

edited May 20, 2024 at 1:22

answered May 19, 2024 at 22:46

T N

10.6k1 gold badge12 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rajiv Ranjan Over a year ago

Thank tou T N for taking time out for writing this query. CROSS APPLY is definately a news thing for me, but I didn't look complicated, when I run the query. I will do few more tweaking, hopefully it will improve the performance.

Rajiv Ranjan Over a year ago

For now we do not have SerialForStarboard, as it looks from the query. thank you for pointing it out.

T N Over a year ago

@RajivRanjan I made a few syntax fixes to the above (combined with other suggested edits). and added a bit of explanation on what the cross apply is doing.

Rajiv Ranjan Over a year ago

Thank you so much T N, it was so elaborate to visualize why this answer does the trick. Best part I learnt was - in CROSS APPLY data is retrieved only once, so powerful... I have implemented straight away...

MtwStark · Accepted Answer · 2024-05-20 16:40:32Z

1

I will try to avoid UNION ALL this way

;with
k as (
select *
from (values 
    (1, 'Single'),
    (2, 'Twin'),
    (3, 'Triple')
    ) k (n, kind)
)
select d.*, k.*
from TblVesselEngineSummary d
join k kk on kk.kind=d.EngineConfiguration
join k on k.n <= kk.n
order by 2,k.n

answered May 20, 2024 at 16:40

MtwStark

4,0961 gold badge23 silver badges34 bronze badges

1 Comment

Rajiv Ranjan Over a year ago

Wow this looks completely different approach, will explore more on this.

Collectives™ on Stack Overflow

How to avoid Multiple Tables Union All

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related