Post reply

exclude CTE values

  • 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

  • February 27, 2014 at 5:56 am

    #1692622

    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

    #1692624

    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

  • February 27, 2014 at 6:08 am

    #1692627

    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

    #1692635

    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

  • February 27, 2014 at 6:29 am

    #1692642

    I would like to gain performance also 🙁

    Thanks for the reply

  • February 27, 2014 at 6:33 am

    #1692647

    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

  • February 27, 2014 at 6:41 am

    #1692658

    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

  • February 27, 2014 at 6:52 am

    #1692662

    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

    #1692712

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 27, 2014 at 9:25 am

    #1692754

    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)
    Intro to Tally Tables and Functions

  • February 27, 2014 at 10:48 pm

    #1692944

    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

    #1693053

    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)
    Intro to Tally Tables and Functions

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

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