making union into a LEFT JOIN select to make execution time faster

Question

I have a select in my MSSQL and it has UNION , JOIN , LIKE. It connects many tables and it has like that is why the execution time is very slow. I have read that union is by nature very slow and I have to use JOINS so I converted below

SELECT 
acctcode , 
SUM(pcchrgamt) AS pcchrgamt,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode
FROM ( 
SELECT DISTINCT 
t3.acctcode, 
pcchrgamt ,
patlast,
patfirst,
patmiddle,
patsuffix,
accCode
FROM hpatchrg AS t1 INNER JOIN 
hperson AS t2 ON t1.hpercode = t2.hpercode INNER JOIN 
hcharge AS t3 ON t1.chargcode = t3.chrgcode INNER JOIN 
[les].[uacsCodes] AS t4 ON t3.uacs = t4.accCode 
WHERE t1.acctno = '2007-000000209' )
sub 
GROUP BY 
acctcode ,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode  

UNION


SELECT 
acctcode , 
SUM(pcchrgamt) AS pcchrgamt,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode
FROM ( 
SELECT DISTINCT 
t8.acctcode, 
pcchrgamt ,
patlast,
patfirst,
patmiddle,
patsuffix,
accCode
FROM hdocord AS t5 JOIN 
hperson AS t6 ON t5.hpercode = t6.hpercode INNER JOIN 
hprocm AS t7 ON t5.proccode = t7.proccode  INNER JOIN 
hcharge AS t8 ON t7.prmapto = t8.chrgcode INNER JOIN 
[les].[uacsCodes] AS t9 ON t8.uacs = t9.accCode
WHERE t5.acctno = '2007-000000209' )
sub 
GROUP BY 
acctcode ,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode  


UNION

SELECT 
acctcode , 
SUM(pcchrgamt) AS pcchrgamt,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode
FROM ( 
SELECT DISTINCT 
t12.acctcode, 
pcchrgamt ,
patlast,
patfirst,
patmiddle,
patsuffix,
accCode
FROM hrqd AS t10 INNER JOIN 
hperson AS t11 ON t10.hpercode = t11.hpercode INNER JOIN 
hcharge AS t12 ON t10.locacode = t12.chrgcode INNER JOIN 
[les].[uacsCodes] AS t13 ON t12.uacs = t13.accCode 
WHERE t10.acctno = '2007-000000209')
sub 
GROUP BY 
acctcode ,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode

UNION

SELECT 
acctcode , 
SUM(pcchrgamt) AS pcchrgamt,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode
FROM ( 
SELECT DISTINCT 
t16.acctcode, 
pcchrgamt ,
patlast,
patfirst,
patmiddle,
patsuffix,
accCode
FROM hrxo AS t14 INNER JOIN 
hperson AS t15 ON t14.hpercode = t15.hpercode INNER JOIN 
hcharge AS t16 ON t14.orderfrom = t16.chrgcode INNER JOIN 
[les].[uacsCodes] AS t17 ON t16.uacs = t17.accCode
WHERE t14.acctno = '2007-000000209')
sub 
GROUP BY 
acctcode ,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode

Into below

SELECT 
acctcode , 
SUM(pcchrgamt) AS pcchrgamt,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode
FROM ( 
SELECT DISTINCT 
coalesce(t3.acctcode,t8.acctcode,t12.acctcode,t16.acctcode  ) AS acctcode, 
coalesce(t1.pcchrgamt,t5.pcchrgamt,t10.pcchrgamt,t14.pcchrgamt ) AS pcchrgamt,
t2.patlast AS patlast,
t2.patfirst AS patfirst,
t2.patmiddle AS patmiddle,
t2.patsuffix AS patsuffix,
coalesce(t4.accCode,t9.accCode,t13.accCode,t17.accCode  ) AS accCode



FROM 


hpatchrg AS t1 LEFT JOIN
hdocord AS t5 ON t1.acctno = t5.acctno LEFT JOIN 
hrqd AS t10 ON t1.acctno = t10.acctno LEFT JOIN
hrxo AS t14 ON t1.acctno = t14.acctno LEFT JOIN 


hperson AS t2 ON t1.hpercode = t2.hpercode INNER JOIN 
hcharge AS t3 ON t1.chargcode = t3.chrgcode INNER JOIN 
[les].[uacsCodes] AS t4 ON t3.uacs = t4.accCode LEFT JOIN
hperson AS t6 ON t5.hpercode = t6.hpercode INNER JOIN 
hprocm AS t7 ON t5.proccode = t7.proccode  INNER JOIN 
hcharge AS t8 ON t7.prmapto = t8.chrgcode INNER JOIN 
[les].[uacsCodes] AS t9 ON t8.uacs = t9.accCode  INNER JOIN 
hperson AS t11 ON t10.hpercode = t11.hpercode INNER JOIN 
hcharge AS t12 ON t10.locacode = t12.chrgcode INNER JOIN 
[les].[uacsCodes] AS t13 ON t12.uacs = t13.accCode  INNER JOIN
hperson AS t15 ON t14.hpercode = t15.hpercode INNER JOIN 
hcharge AS t16 ON t14.orderfrom = t16.chrgcode INNER JOIN 
[les].[uacsCodes] AS t17 ON t16.uacs = t17.accCode
WHERE t1.acctno = '2007-000000209' 
)
sub 
GROUP BY 
acctcode ,
patlast ,
patfirst,
patmiddle,
patsuffix,
accCode

What I want for this is select 4 main tables mainly (t1,t5,t10,14) the code and then join them with separate tables to get other information. What it does it sometimes code exist on for tables sometimes only but it has to exist in one of table. So i used LEFT JOIN. But the left join i made didnt return any result. I get a blank result.

How to convert the UNION based select to a LEFT JOIN based select

I think your question will be much easier to understand if you provide sample data and expected result — GuidoG, Aug 11 '17 at 09:13
@GuidoG data is a bit big because I have so many tables to re create and i dont know where to recreate the tables — Martin, Aug 11 '17 at 09:18
Please keep in mind that there are two `UNION` statements: `UNION` and `UNION ALL`. `UNION` - is slow, where `UNION ALL` is "fast". See: https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all . Keep in mind that having multiple JOINs is not going to be fast. — Alex, Aug 11 '17 at 09:18
Why do you need to recreate the tables to show us sample data ? Also we dont have to see every column of every table, just a few to demonstrate what you need — GuidoG, Aug 11 '17 at 09:20

making union into a LEFT JOIN select to make execution time faster

0 Answers0