• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

simplify EXISTS query

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
25112
Asked:
25112
  • 5
  • 4
  • 3
  • +2
7 Solutions
 
Shaun KlineLead Software EngineerCommented:
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
 
25112Author Commented:
a is 21k records.. b is 2k records.. are you referring to this or the complexity of syntax within (a) and (b)?
0
 
virtuadeptCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
awking00Commented:
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
 
virtuadeptCommented:
It would definitely help to see the existing query and some sample data.
0
 
25112Author Commented:
will get the query and sample data in the morning. thx.
0
 
25112Author Commented:
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
 
25112Author Commented:
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
 
awking00Commented:
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
 
awking00Commented:
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
 
25112Author Commented:
king, your 2 points are well taken.. the result set is based on the combination of the whole query.
0
 
Anthony PerkinsCommented:
Perhaps you are not aware that your DISTINCT clause is redundant with a UNION clause.
0
 
virtuadeptCommented:
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
 
awking00Commented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now