May 5, 2016 at 8:16 am
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?
May 5, 2016 at 8:22 am
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
May 5, 2016 at 8:30 am
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
May 5, 2016 at 9:31 am
Any of those columns is nullable? Can you post DDL and sample data?
May 5, 2016 at 1:23 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
May 5, 2016 at 4:29 pm
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
May 6, 2016 at 7:35 am
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.
May 6, 2016 at 7:42 am
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