[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# simplify EXISTS query

Posted on 2013-12-12
Medium Priority
352 Views
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
Question by:25112
[X]
###### 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
• 5
• 4
• 3
• +2

LVL 27

Accepted Solution

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

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

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

LVL 32

Assisted Solution

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

ID: 39715340
It would definitely help to see the existing query and some sample data.
0

LVL 5

Author Comment

ID: 39715816
will get the query and sample data in the morning. thx.
0

LVL 5

Author Comment

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

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

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

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

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

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

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
``````

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

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

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vulnâ€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
###### Suggested Courses
Course of the Month14 days, 18 hours left to enroll

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

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