February 6, 2026 at 9:14 pm
Hi
So the case statement is slowing this down - but for the life of me I cant think/find a more efficient way to get the results:
select *
FROM pec.tBRClaimLinePreCalc WITH (NOLOCK)
JOIN dbo.tclaim_procedure WITH (NOLOCK) ON
tclaim_procedure.claim_procedure_id = tBClaimLinePreCalc.claim_procedure_id
JOIN dbo.TPEC_RGV_ProcedureType WITH (NOLOCK) ON
TPEC_RGV_ProcedureType.claim_procedure_id = tClaimLinePreCalc.claim_procedure_id
WHERE
claimProc_ServiceType IS NULL
OR
(
tBRClaimLinePreCalc.claimProc_ServiceType
<>
CASE WHEN vision_flag = 0 THEN 1
WHEN vision_flag = 1 THEN 2
WHEN vision_flag IS NULL THEN 3
END
AND
tclaim_procedure.date_created >= '1/1/2019'
)
If I remove everything after the first case statement it runs pretty well. So guessing it has to loop through every record here?
February 6, 2026 at 10:06 pm
What table is vision_flag in? Helps for others to follow your code if you use aliases for table and then ALWAYS add an alias for every column name used in the query, something like below.
For true performance, we'd need to table sizes and index(es) on the tables. In this case, some stats about how many row match each condition might also help, but we can get to all that later.
SELECT *
FROM pec.tBRClaimLinePreCalc br WITH (NOLOCK)
JOIN dbo.tclaim_procedure cp WITH (NOLOCK) ON
cp.claim_procedure_id = br.claim_procedure_id
JOIN dbo.TPEC_RGV_ProcedureType pec WITH (NOLOCK) ON
pec.claim_procedure_id = br.claim_procedure_id
WHERE
br.claimProc_ServiceType IS NULL
OR
(
cp.date_created >= '20190101'
AND
br.claimProc_ServiceType
<>
CASE WHEN ??.vision_flag = 0 THEN 1 --<<--which table alias??
WHEN ??.vision_flag = 1 THEN 2 --<<--which table alias??
WHEN ??.vision_flag IS NULL THEN 3 --<<--which table alias??
END
)
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".
February 7, 2026 at 12:14 am
Maybe the issue is the query could possibly be more efficiently evaluated if expressed as two queries. It always depends on the actual situation with cardinalities and indexes etc. If performance is the issue then sargability would seem to be achieved by splitting the query on the OR
select p.*
from pec.tbrclaimlineprecalc p
join dbo.tclaim_procedure cp on cp.claim_procedure_id = p.claim_procedure_id
join dbo.tpec_rgv_proceduretype pt on pt.claim_procedure_id = p.claim_procedure_id
where p.claimproc_servicetype is null
union all
select p.*
from pec.tbrclaimlineprecalc p
join dbo.tclaim_procedure cp on cp.claim_procedure_id = p.claim_procedure_id
join dbo.tpec_rgv_proceduretype pt on pt.claim_procedure_id = p.claim_procedure_id
where cp.date_created >= datefromparts(2019,1,1)
and p.claimproc_servicetype is not null
and p.claimproc_servicetype <> (coalesce(pt.vision_flag, 2) + 1);
The mapping 0-->1, 1-->2, NULL-->3 is equivalently COALESCE(vision_flag, 2) + 1
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply