Solved

simplify EXISTS query

Posted on 2013-12-12
14
332 Views
Last Modified: 2014-01-04
is this query in its most simplied form?

()a is a big UNION (the code goes between ())
and b() is a simpler query with conditions

the below logic is perfect.. but not sure how efficient it is... can you review it please?

select * from
() a /*a - this has 7 big unions within the () */
where not exists
(select 0  from ()b where a.key1=b.key1  and a.key2 = b.key2) /*b- This is a two table join with 3 conditions */
0
Comment
Question by:25112
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 25

Accepted Solution

by:
Shaun Kline earned 72 total points
Comment Utility
Efficiency would be related to the amount of data brought back from you big 7 Union versus the amount of data brought back by your B query.

It may be more efficient to use a CTE for your B query, and perform the NOT EXISTS on each of the 7 UNIONS as this would exclude records prior to the UNION putting them together.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
a is 21k records.. b is 2k records.. are you referring to this or the complexity of syntax within (a) and (b)?
0
 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 143 total points
Comment Utility
Based on those record counts I would do as Shaun suggested and make a Common Table Expression for ()B and use that in a Not EXISTS clause for each part of the union for ()A.

Syntax is roughly:

WITH B_CTE (columns for B)
(
   -- existing ()B code here
)
SELECT *
FROM  -- first union block for ()A here
..
AND NOT EXISTS (Select 1 from B_CTE where key1 = B_CTE.key1 and key2=B_CTE.key2)
UNION ALL
-- next union block, with the not exists clause added also
...
-- last union block with not exists clause
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 214 total points
Comment Utility
Without understanding what all is going on with your join and union, it's a little difficult to say with any certainty what might be done to improve the efficiency of your query. The cte concept already proposed is certainly worth investigation. Some questions arise that might shed a little more light on the subject. Is your big union comprised of like columns from many tables or many columns from like tables (or both)? Do the tables in your ()b join also exist in your union query or are they separate? Would it be possible to narrow down your big union and table join to just those columns that are relevant and provide some sample data to that effect?
0
 
LVL 8

Expert Comment

by:virtuadept
Comment Utility
It would definitely help to see the existing query and some sample data.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
will get the query and sample data in the morning. thx.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
the below is "b"

             select distinct NIANE_ID,
 right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4) TagNumber  
from NianeStatHist join PotScore on PotScorePK=PotScoreFK
where  statCd=20 and biddate is null
and TagNumber <>12
-----------------------
the final line conditions are

where FAI_KEY=NIANE_ID  and Tag_ID = TagNumber)
 -----------------------

and 'a' is:

select NIANE_ID FAI_KEY, right(right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4),4) Tag_ID, '2013-01-01'  TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-01-01' THEN '2013-01-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
CASE TagCode.BarTag  
WHEN 'A01' THEN 'A001'  
WHEN 'A02' THEN 'A002'  
WHEN 'A03' THEN 'A003'  
WHEN 'A04' THEN 'A004'  
WHEN 'A05' THEN 'A005'  
WHEN 'A06' THEN 'A006'  
WHEN 'A07' THEN 'A007'  
WHEN 'A08' THEN 'A008'  
WHEN 'A09' THEN 'A009'  
WHEN 'A010' THEN 'A0010'  
WHEN 'A011' THEN 'A0011'
WHEN 'A012' THEN 'A0012'
WHEN 'A013' THEN 'A0013'  
WHEN 'A014' THEN 'A0014'
WHEN 'A015' THEN 'A0015'
ELSE TagCode.BarTag    END BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotScoreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag  IN ('A01', 'A02','A03','A04','A05','A06','A07','A08','A09','A010','A011','A012','A0015','A0013','A0014') and NianeCdPK in
            (          7,  8,  3,   1,   5 )
                  GROUP by NIANE_ID, TagNumber, TagCode.BarTag
      
            union
            
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30'  TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'K5A' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotScoreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag  IN ('K5H') and NianeCdPK in
            (          7,  8,  3,   1,   5 )
                  GROUP by NIANE_ID, TagNumber, TagCode.BarTag
      
             
             UNION
             
      select DISTINCT NIANE_ID FAI_KEY,right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30'  TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'K5P' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotScoreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag  IN ('K5H') and NianeCdPK in
            (          7,  8,  3,   1,   5 )
                        GROUP by NIANE_ID, TagNumber, TagCode.BarTag
      
                  
            union
            
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30'  TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'K4A' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotScoreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag  IN ('K4H') and NianeCdPK in
            (          7,  8,  3,   1,   5 )
                   GROUP by NIANE_ID, TagNumber, TagCode.BarTag
      
             UNION
             
      select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30'  TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'K4P' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotScoreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag  IN ('K4H') and NianeCdPK in
            (          7,  8,  3,   1,   5 )
                  GROUP by NIANE_ID, TagNumber, TagCode.BarTag
      
            
            union
            
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30'  TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'PKA' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotScoreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag  IN ('PreKH') and NianeCdPK in
            (          7,  8,  3,   1,   5 )
                  GROUP by NIANE_ID, TagNumber, TagCode.BarTag
      
             
             UNION
             
      select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30'  TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'PKP' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotScoreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag  IN ('PreKH') and NianeCdPK in
            (          7,  8,  3,   1,   5 )       
            GROUP by NIANE_ID, TagNumber, TagCode.BarTag
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:25112
Comment Utility
result:

10051      0744      2013-01-01      2013-01-01 00:00:00.000      A001
10051      0744      2013-01-01      2013-01-01 00:00:00.000      A007
10126      1216      2013-01-01      2013-01-01 00:00:00.000      A005
10126      1216      2013-01-01      2013-01-01 00:00:00.000      A0015
10127      0397      2013-01-01      2013-01-01 00:00:00.000      A0013
10127      4003      2013-01-01      2013-01-01 00:00:00.000      A001
10127      4003      2013-01-01      2013-01-01 00:00:00.000      A0010
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 214 total points
Comment Utility
For the result that you show, is that the result of the union query only? If so, what is the corresponding result of "b"? One quick thing I see that could be simpler is to select replace(TagCode.BarTag,'A0','A00') instead of the case statement. Haven't had a chance to research the union statements in depth yet.
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 214 total points
Comment Utility
Another quick thing I see is that you don't need to use the distinct word since the union operator will eliminate any duplicates.
0
 
LVL 5

Author Comment

by:25112
Comment Utility
king, your 2 points are well taken.. the result set is based on the combination of the whole query.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 71 total points
Comment Utility
Perhaps you are not aware that your DISTINCT clause is redundant with a UNION clause.
0
 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 143 total points
Comment Utility
Except for the first select in the 'a' union you have 7 identical clauses being unioned together where the only difference is a static field being changed for each one called "BarTag_Cd" where that is getting set to a different static value for each union.

This is extremely inefficent. What you should do is take all but the first union (and maybe you could do that one too I wasn't sure what the differences on it were) and convert those to just this:

      select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('000'+cast(TagNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30'  TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
x.BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK 
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotScoreFK 
join NianeCd on NianeCdPK = NianeCdFK
cross join (SELECT BarTag_Cd from #BarTag_Cd_List ) as x
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag  IN ('PreKH') and NianeCdPK in
            (          7,  8,  3,   1,   5 )       
            GROUP by NIANE_ID, TagNumber, TagCode.BarTag

Open in new window


Before your main select you need to populate temp table #BarTag_Cd_List  with the distinct values used in your unions such as 'K5A', 'PKP', etc. those hard coded values being put into the BarTag_Cd field in each of the union blocks.

Aside from that, You might also be able to use the CTE idea but lets just see how this would make it perform over using the 7 uneccessary unions.

EDIT: Also you could use a table variable instead of a temp table for that cross join for #BarTag_Cd_list.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>the result set is based on the combination of the whole query.<<
Including the not exists clause? If so, can you post the results from just your "b" table join?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now