?
Solved

simplify EXISTS query

Posted on 2013-12-12
14
Medium Priority
?
354 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 27

Accepted Solution

by:
Shaun Kline earned 288 total points
ID: 39714977
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
ID: 39715033
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 572 total points
ID: 39715056
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 856 total points
ID: 39715203
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
ID: 39715340
It would definitely help to see the existing query and some sample data.
0
 
LVL 5

Author Comment

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

Author Comment

by:25112
ID: 39716886
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
 
LVL 5

Author Comment

by:25112
ID: 39716903
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 32

Assisted Solution

by:awking00
awking00 earned 856 total points
ID: 39716963
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 32

Assisted Solution

by:awking00
awking00 earned 856 total points
ID: 39716992
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
ID: 39718232
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 284 total points
ID: 39719369
Perhaps you are not aware that your DISTINCT clause is redundant with a UNION clause.
0
 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 572 total points
ID: 39721496
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 32

Expert Comment

by:awking00
ID: 39721548
>>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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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