February 27, 2014 at 5:54 am
Hi,
I have 5 union queries which have repetitive Not in clause. Can I exclude these using CTE
Please advise
thanks
February 27, 2014 at 5:56 am
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
February 27, 2014 at 6:00 am
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
February 27, 2014 at 6:08 am
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"
February 27, 2014 at 6:20 am
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
February 27, 2014 at 6:29 am
I would like to gain performance also
Thanks for the reply
February 27, 2014 at 6:33 am
pmadhavapeddi22 (2/27/2014)
I would like to gain performance alsoThanks 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
February 27, 2014 at 6:41 am
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
February 27, 2014 at 6:52 am
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
February 27, 2014 at 8:18 am
pmadhavapeddi22 (2/27/2014)
I would like to gain performance alsoThanks 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
February 27, 2014 at 9:25 am
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
Change is inevitable... Change for the better is not.
February 27, 2014 at 10:48 pm
Truly, I thought of using temp table but I need everything in a single query.
thanks for the advise
February 28, 2014 at 7:08 am
pmadhavapeddi22 (2/27/2014)
...but I need everything in a single query.
Why?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy