Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

simplify EXISTS query

Posted on 2013-12-12
14
346 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 26

Accepted Solution

by:
Shaun Kline earned 72 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 143 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 214 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 214 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 214 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 71 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 143 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 41
This query failed in sql 2014 5 33
Filtering characters in an SQL field 2 16
SQL Select in Access 2003 3 22
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 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