exclude CTE values

  • Hi,

    I have 5 union queries which have repetitive Not in clause. Can I exclude these using CTE

    Please advise

    thanks

  • 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

  • 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
  • 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"

  • 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
  • I would like to gain performance also 🙁

    Thanks for the reply

  • 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

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

  • 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

  • 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)

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

    thanks for the advise

  • 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)

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

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