March 12, 2015 at 7:45 am
Hi all,
I have a script which contains lots of union all statements like this:
SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID, IR.VSFORMNM as SVFORMNM, IR.VSUNS as SVUNS, IR.VSSEQ as SVSEQ, IR.VSNAME as VISIT, IR.VSNUM as VISITNUM, IR.SUBJID,s.STATUS, IR.DMSCRNUM, IR.DMRNDNUM, case when s.STATUS ='Screen Failed' then 'SF-' + S.DMSCRNUM else DMCOHRT + '-' + S.DMSCRNUM end as USUBJID
,IRYN,IRDOSE,IRASTYP
,'' as IRCDAT
,IRSTDY1 as IRSTDY, '' as IRCATTPT
,'' as IRPERF
,IRADAT
,IRTEMP1 as IRTEMP
,IRSNONE1 as IRSNONE
,'ITCHING AT SITE' as IRSCAT
,IRSORRES6 as IRSORRES
,IRSINTF6 as IRSINTF
,IRSPRVT6 as IRSPRVT
,IRSMED6 as IRSMED
,IRSGRADE6 as IRSGRADE
,'' as IRRMEAS
,'' as IRUNVYN
,'' as IRUNVAYN
,'' as IRMAXMEA
,'' as IRMAXWCH
,IRPGRADE
from IR left JOIN
dbo.DM2 AS S ON S.SUBJID = dbo.IR.SUBJID
where VSFORMNM not like 'Injection Site Reaction Phone Report Log Dose%'and ir.VSFORMID in (select id from [10.100.100.20].[CA5569F3-21B3-4731-95BF-9D715E348554].[dbo].visits where deleted =0)
UNION ALL
SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID, IR.VSFORMNM as SVFORMNM, IR.VSUNS as SVUNS, IR.VSSEQ as SVSEQ, IR.VSNAME as VISIT, IR.VSNUM as VISITNUM, IR.SUBJID,s.STATUS, IR.DMSCRNUM, IR.DMRNDNUM, case when s.STATUS ='Screen Failed' then 'SF-' + S.DMSCRNUM else DMCOHRT + '-' + S.DMSCRNUM end as USUBJID
,IRYN,IRDOSE,IRASTYP
,'' as IRCDAT
,IRSTDY1 as IRSTDY, '' as IRCATTPT
,'' as IRPERF
,IRADAT
,IRTEMP1 as IRTEMP
,IRSNONE2 as IRSNONE
,'ERYTHEMA' as IRSCAT
,IRSORRES7 as IRSORRES
,'' as IRSINTF
,'' as IRSPRVT
,'' as IRSMED
,IRSGRADE7 as IRSGRADE
,IRRMEAS7 as IRRMEAS
,'' as IRUNVYN
,'' as IRUNVAYN
,'' as IRMAXMEA
,'' as IRMAXWCH
,IRPGRADE
from IR left JOIN
dbo.DM2 AS S ON S.SUBJID = dbo.IR.SUBJID
where VSFORMNM not like 'Injection Site Reaction Phone Report Log Dose%'and ir.VSFORMID in (select id from [10.100.100.20].[CA5569F3-21B3-4731-95BF-9D715E348554].[dbo].visits where deleted =0)
UNION ALL
SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID, IR.VSFORMNM as SVFORMNM, IR.VSUNS as SVUNS, IR.VSSEQ as SVSEQ, IR.VSNAME as VISIT, IR.VSNUM as VISITNUM, IR.SUBJID,s.STATUS, IR.DMSCRNUM, IR.DMRNDNUM, case when s.STATUS ='Screen Failed' then 'SF-' + S.DMSCRNUM else DMCOHRT + '-' + S.DMSCRNUM end as USUBJID
,IRYN,IRDOSE,IRASTYP
,'' as IRCDAT
,IRSTDY1 as IRSTDY, '' as IRCATTPT
,'' as IRPERF
,IRADAT
,IRTEMP1 as IRTEMP
,IRSNONE2 as IRSNONE
,'DESQUAMATION' as IRSCAT
,IRSORRES8 as IRSORRES
,'' as IRSINTF
,'' as IRSPRVT
,'' as IRSMED
,IRSGRADE8 as IRSGRADE
,IRRMEAS8 as IRRMEAS
,'' as IRUNVYN
,'' as IRUNVAYN
,'' as IRMAXMEA
,'' as IRMAXWCH
,IRPGRADE
from IR left JOIN dbo.DM2 AS S ON S.SUBJID = dbo.IR.SUBJID
where VSFORMNM not like 'Injection Site Reaction Phone Report Log Dose%'and ir.VSFORMID in (select id from [10.100.100.20].[CA5569F3-21B3-4731-95BF-9D715E348554].[dbo].visits where deleted =0)
Is there any alternate way of inspite of using USING ALL so that performance can be improved..
Thanks in advance
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 12, 2015 at 8:08 am
Try to do the join after the union like this:
select ...
from (select ...
union all
select ...) as unioned
join jointable
on unioned.col = jointable.col
It may be better (if most of the rows are joined) or worse (if only a few rows are joined).
Also, try to add the constant data outside of the union all/join logic, if possible.
finally, ensure that the join and where predicates are on indexed columns and add appropriate indexes if not
Gerald Britton, Pluralsight courses
March 12, 2015 at 8:12 am
Am I missing something, or aren't all those unions to the same source tables with the same joins and where clauses? The only difference I can see is in the hard-coded values so, in effect, you are simply creating three records from each source record. If thats the case, why not create a temp table with just those three hardcoded values in it and then join it to the first query without the unions?
March 12, 2015 at 8:29 am
andyscott (3/12/2015)
Am I missing something, or aren't all those unions to the same source tables with the same joins and where clauses? The only difference I can see is in the hard-coded values so, in effect, you are simply creating three records from each source record. If thats the case, why not create a temp table with just those three hardcoded values in it and then join it to the first query without the unions?
Excellent idea. But I think you missed that the columns in the select list are different in each query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 8:31 am
This thing screams of denormalized data when I see columns like IRSORRES6, IRSORRES7, IRSORRES8. If you can normalize the data you could eliminate the need to have 3 queries here instead of 1. If you are stuck with the structures (which is highly likely) there isn't a lot you do. If performance is the issue we need to see the table structures, indexes and actual execution plans.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 8:43 am
Sean Lange (3/12/2015)
This thing screams of denormalized data when I see columns like IRSORRES6, IRSORRES7, IRSORRES8. If you can normalize the data you could eliminate the need to have 3 queries here instead of 1. If you are stuck with the structures (which is highly likely) there isn't a lot you do. If performance is the issue we need to see the table structures, indexes and actual execution plans.
He also has hard coded columns in the select with different values in them, I guess there's some business reason for that....
March 12, 2015 at 8:48 am
ZZartin (3/12/2015)
Sean Lange (3/12/2015)
This thing screams of denormalized data when I see columns like IRSORRES6, IRSORRES7, IRSORRES8. If you can normalize the data you could eliminate the need to have 3 queries here instead of 1. If you are stuck with the structures (which is highly likely) there isn't a lot you do. If performance is the issue we need to see the table structures, indexes and actual execution plans.He also has hard coded columns in the select with different values in them, I guess there's some business reason for that....
Yeah I saw that too. Probably something to do with each group has some hard coded value. Yet another argument for normalization. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 9:21 am
Assuming normalising the tables isn't an option for you, how about something like this:
WITH cte AS (SELECT * FROM (VALUES (1),(2),(3)) AS temp (no))
SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID, IR.VSFORMNM as SVFORMNM, IR.VSUNS as SVUNS, IR.VSSEQ as SVSEQ, IR.VSNAME as VISIT, IR.VSNUM as VISITNUM, IR.SUBJID,s.STATUS, IR.DMSCRNUM, IR.DMRNDNUM, case when s.STATUS ='Screen Failed' then 'SF-' + S.DMSCRNUM else DMCOHRT + '-' + S.DMSCRNUM end as USUBJID
,IRYN,IRDOSE,IRASTYP
,'' as IRCDAT
,IRSTDY1 as IRSTDY, '' as IRCATTPT
,'' as IRPERF
,IRADAT
,IRTEMP1 as IRTEMP
,IRSNONE1 as IRSNONE
,CASE WHEN No=1 THEN 'ITCHING AT SITE'
WHEN No=2 THEN 'ERYTHEMA'
WHEN No=3 THEN 'DESQUAMATION'
END AS IRSCAT
,CASE WHEN No=1 THEN IRSORRES6
WHEN No=2 THEN IRSORRES7
WHEN No=3 THEN IRSORRES8
END AS IRSORRES
,IRSINTF6 as IRSINTF
,IRSPRVT6 as IRSPRVT
,IRSMED6 as IRSMED
,IRSGRADE6 as IRSGRADE
,'' as IRRMEAS
,'' as IRUNVYN
,'' as IRUNVAYN
,'' as IRMAXMEA
,'' as IRMAXWCH
,IRPGRADE
FROM IR,cte
LEFT JOIN dbo.DM2 AS S ON S.SUBJID = dbo.IR.SUBJID
WHERE VSFORMNM not like 'Injection Site Reaction Phone Report Log Dose%'
AND ir.VSFORMID in (select id from [10.100.100.20].[CA5569F3-21B3-4731-95BF-9D715E348554].[dbo].visits where deleted =0)
with the same sort of CASE/WHEN as shown for IRSCAT and IRSORRES applied to the remaining columns.
NB I can't test this 'cos I don't have your tables but it appears to work in a harness!
March 12, 2015 at 10:53 am
ZZartin (3/12/2015)
Sean Lange (3/12/2015)
This thing screams of denormalized data when I see columns like IRSORRES6, IRSORRES7, IRSORRES8. If you can normalize the data you could eliminate the need to have 3 queries here instead of 1. If you are stuck with the structures (which is highly likely) there isn't a lot you do. If performance is the issue we need to see the table structures, indexes and actual execution plans.He also has hard coded columns in the select with different values in them, I guess there's some business reason for that....
But couldn't those also be added to the temp table? This does look a might messed up.
Also, the UNION ALL is probably not the source of slow performance. It's probably that WHERE clause. Have you looked at the execution plans to understand how the indexes are being used?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply