exclude CTE values

Question

exclude CTE values

pmadhavapeddi22

SSCertifiable

Points: 5306
More actions
February 27, 2014 at 5:54 am

#396340

Hi,
I have 5 union queries which have repetitive Not in clause. Can I exclude these using CTE
Please advise
thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Can you give an example of such a query?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Without seeing the query, absolutely no idea.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 3

select col_1 from table_1
inner join table_4
where table_4.id not in(select parent_id from table_4)
union
select col_1 from table_2
inner join table_4
where table_4.id not in(select parent_id from table_4)
union
select col_1 from table_3
inner join table_4
where table_4.id not in(select parent_id from table_4)

The above is an example. In addition to that i have few more conditions to each query, but "Not In" clause is same for all the queries. I want to eliminate the repetitive "Not IN"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You can, it'll clean up coding a bit, won't improve performance or change how the query runs.

WITH FilteredTable4 AS (

SELECT <whatever columns needed>

FROM table_4

WHERE table_4.id NOT IN (select parent_id from table_4)

)

select col_1 from table_1

inner join FilteredTable4 ON <Join condition>

union

select col_1 from table_2

inner join FilteredTable4 ON <Join condition>

union

select col_1 from table_3

inner join FilteredTable4 ON <Join condition>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 5

I would like to gain performance also 🙁

Thanks for the reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

pmadhavapeddi22 (2/27/2014)
I would like to gain performance also 🙁
Thanks for the reply

CTEs won't give you a performance gain.

Indexing might. Maybe you can create an indexed view.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Koen Verbeeck (2/27/2014)
Maybe you can create an indexed view.

Maybe, maybe not. Might make other things worse, might not be possible. No way to tell.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 8

GilaMonster (2/27/2014)
Koen Verbeeck (2/27/2014)
Maybe you can create an indexed view.
Maybe, maybe not. Might make other things worse, might not be possible. No way to tell.

That's why I started with "maybe" 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 398711 More actions · Answer 9

pmadhavapeddi22 (2/27/2014)
I would like to gain performance also 🙁
Thanks for the reply

You have to then look at the query plan to determine where things are slowing down and how you can address them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 10

pmadhavapeddi22 (2/27/2014)
select col_1 from table_1
inner join table_4
where table_4.id not in(select parent_id from table_4)
union
select col_1 from table_2
inner join table_4
where table_4.id not in(select parent_id from table_4)
union
select col_1 from table_3
inner join table_4
where table_4.id not in(select parent_id from table_4)
The above is an example. In addition to that i have few more conditions to each query, but "Not In" clause is same for all the queries. I want to eliminate the repetitive "Not IN"

Not everything has to be done in a single query in order for it to be effective, set-based code. In fact, it frequently helps quite a bit if you split a query up based on some common denominators. Obviously, I don't have your data to test with so the following is totally untested either for success or performance, but it does demonstrate what I'm talking about.

--===== Isolate the working rows in a temp table so

-- that you don't have to run the same stuff

-- 3 times (6 total hits on the same table

-- is reduced to just 2 and the result set is reduced).

SELECT id FROM dbo.table_4

INTO #Exceptions

EXCEPT

SELECT parent_id FROM dbo.table_4

;

--===== Then, you can do something like this.

SELECT col_1 FROM dbo.table_1 t JOIN #Exceptions e ON t.id = e.id UNION

SELECT col_1 FROM dbo.table_2 t JOIN #Exceptions e ON t.id = e.id UNION

SELECT col_1 FROM dbo.table_3 t JOIN #Exceptions e ON t.id = e.id

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 11

Truly, I thought of using temp table but I need everything in a single query.

thanks for the advise

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 12

pmadhavapeddi22 (2/27/2014)
...but I need everything in a single query.

Why?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)