Need some help transposing a query

  • Hey guys, I have a stored procedure I'm trying to make more efficient. Its been a while since I was in college transposing queries. The below query is using a cte that Id like to eliminate and just add it in the where clause.

    with S as (SELECT

    MCRNUS.MASTER_CODING_RESULTS_ID

    FROM G_MASTER_CODING_RESULTS AS MCRNUS With (NOLOCK)

    WHERE MCRNUS.REVIEW_TYPE <> 'HCR'

    AND (MCRNUS.C_V12_HCC = 96

    OR MCRNUS.C_V22_HCC = 100

    OR MCRNUS.C_V22_B_HCC = 100

    OR MCRNUS.C_V22_C_HCC = 100))

    Select *

    FROM dbo.G_MASTER_CODING_RESULTS AS mcr WITH (NOLOCK)

    left join S on mcr.MASTER_CODING_RESULTS_ID = S.MASTER_CODING_RESULTS_ID

    where S.MASTER_CODING_RESULTS_ID is null

    How can I remove the CTE and just include a filter so I can save time of joining to the CTE?

  • It's unlikely to save any time, CTEs aren't inherently slow.

    I took the nolock hints out, as I suspect you probably don't want incorrect results every now and again. If you do, you can put the hint back.

    SELECT <explicit column list>

    FROM dbo.G_MASTER_CODING_RESULTS AS mcr

    WHERE NOT EXISTS ( SELECT 1

    FROM G_MASTER_CODING_RESULTS AS MCRNUS

    WHERE MCRNUS.REVIEW_TYPE <> 'HCR'

    AND (MCRNUS.C_V12_HCC = 96

    OR MCRNUS.C_V22_HCC = 100

    OR MCRNUS.C_V22_B_HCC = 100

    OR MCRNUS.C_V22_C_HCC = 100

    )

    AND MCRNUS.MASTER_CODING_RESULTS_ID = mcr.MASTER_CODING_RESULTS_ID );

    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
  • Thanks but I was trying to figure a way I wouldn't need to use an embedded query. Something like turning

    MCRNUS.REVIEW_TYPE <> 'HCR'

    AND (MCRNUS.C_V12_HCC = 96

    OR MCRNUS.C_V22_HCC = 100

    OR MCRNUS.C_V22_B_HCC = 100

    OR MCRNUS.C_V22_C_HCC = 100

    )

    into something like

    MCRNUS.REVIEW_TYPE = 'HCR'

    OR(MCRNUS.C_V12_HCC <> 96

    AND MCRNUS.C_V22_HCC <> 100

    AND MCRNUS.C_V22_B_HCC <> 100

    AND MCRNUS.C_V22_C_HCC <> 100

    )

    I've tried that but didn't get the same rowcount

  • Any of those columns is nullable? Can you post DDL and sample data?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Select mcr.*

    FROM dbo.G_MASTER_CODING_RESULTS AS mcr WITH (NOLOCK)

    left join (

    SELECT

    MCRNUS.MASTER_CODING_RESULTS_ID

    FROM G_MASTER_CODING_RESULTS AS MCRNUS With (NOLOCK)

    WHERE MCRNUS.REVIEW_TYPE <> 'HCR'

    AND (MCRNUS.C_V12_HCC = 96

    OR MCRNUS.C_V22_HCC = 100

    OR MCRNUS.C_V22_B_HCC = 100

    OR MCRNUS.C_V22_C_HCC = 100)

    ) as S on mcr.MASTER_CODING_RESULTS_ID = S.MASTER_CODING_RESULTS_ID

    WHERE S.MASTER_CODING_RESULTS_ID is null

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Given the name MASTER_CODING_RESULTS_ID, it's highly likely that this is the primary key, in which case there is no reason to reference the table more than once.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • my 2 cents...

    CTEs make the code easier to read because you can encapsulate complicated logic away from the main query and generally you can unit test the CTE as it does not rely on records outside the selection, plus you can use the same CTE in the main query more than once which means you only have one bit of code to edit.

    As Gail pointed out, NOLOCK is generally a bad idea and is normally used when people don't really understand where their performance issues are, and in most cases the CTE will generate the same query plan as a nested sub-select

    For your particular problem, I would approach it slightly differently, use the CTE to get the records that you DON'T want and then select the remaining records with an EXCEPT union

    WITH CTE_EXCLUDE AS

    (

    Select mcr.*

    FROM dbo.G_MASTER_CODING_RESULTS AS mcr WITH (NOLOCK)

    WHERE

    WHERE MCRNUS.REVIEW_TYPE = 'HCR'

    AND NOT (MCRNUS.C_V12_HCC = 96

    OR MCRNUS.C_V22_HCC = 100

    OR MCRNUS.C_V22_B_HCC = 100

    OR MCRNUS.C_V22_C_HCC = 100)

    )

    Select *

    FROM dbo.G_MASTER_CODING_RESULTS AS mcr WITH (NOLOCK)

    EXCEPT

    Select * FROM CTE_EXCLUDE

    Note that I have inverted the logic for the CTE_EXCLUDE by changing the "_TYPE <>" to "_TYPE =" and changing the nested ORs to AND NOT

    You may want to check the two query plans, but I think the code above is much easier to read.

  • Its late on a Fridyay.

    As Drew pointed out, you are only selecting data from one table so there should be no need to reference it twice

    SELECT

    mcr.*

    FROM

    dbo.G_MASTER_CODING_RESULTS AS mcr WITH (NOLOCK)

    WHERE

    NOT(

    mcr.REVIEW_TYPE <> 'HCR'

    AND

    (

    mcr.C_V12_HCC = 96

    OR

    mcr.C_V22_HCC = 100

    OR

    mcr.C_V22_B_HCC = 100

    OR

    mcr.C_V22_C_HCC = 100)

    )

    )

Viewing 8 posts - 1 through 7 (of 7 total)

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