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 rows 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
February 9, 2026 at 10:04 pm
Also, just to confirm but I suspect you are right with the CASE statement, but is it the CASE statement or the date comparison that is slow? If it is the date, you may benefit from adding an index on the date (if it doesn't already exist). Might not make a difference, but just another thing to check out. Also, is that column actually a date or is it a CHAR/VARCHAR? If it is a date, may not hurt to put the date value into a variable to avoid any type conversions going on. Might not make a lick of difference, but I've seen implicit conversion cause all sorts of slowdowns before.
Another thing that MAY help performance (may not) would be to put the indexable data into a temp table/table variable. What I mean is pull in the data related to the WHERE clause part and use logic similar to the UNION ALL suggested above except since you have pre-filtered the data into a temp table/table variable for the date and service type being not null already, you only need to compare the service type to the vision flag. I've found that SOMETIMES this can help with performance, SOMETIMES it makes things worse, and SOMETIMES it makes no difference, but it's an easy thing to test and can help performance. USUALLY if it helps performance it is due to linked server or cross database queries in my experience.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 10, 2026 at 9:04 am
The <> will be slow as you still have to compare claims since 2019
Depending how often it has to run it may benefit from a computed column (logic) + index (persist on disk)
February 11, 2026 at 5:27 pm
What everybody says above +
Based on the complexity of the query, (comparing a column in one table to a column in another) and from the NOLOCK hints, it looks like you're running and analytical query in a transactional database. Without some kind of denormalization, you can't get around having at least a range index scan on both tables. If this is something that needs to run frequently and fast, I would look to denormalize the tables, compute
p.claimproc_servicetype <> (coalesce(pt.vision_flag, 2) + 1)
at write time and index on that in the denormalized table.
February 11, 2026 at 5:49 pm
Thank you so much for the reply but I'm getting an error:
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
February 23, 2026 at 9:51 pm
The UNION ALL error you're getting is almost certainly because your original query used SELECT * which pulls columns from all three joined tables. Steve's rewrite uses SELECT p.* which only grabs columns from tBRClaimLinePreCalc. If you need columns from the other tables too, you'll have to list them explicitly in both halves so the column count and order matches:
SELECT p.claim_procedure_id, p.claimProc_ServiceType, cp.date_created, pt.vision_flag
FROM pec.tBRClaimLinePreCalc p WITH (NOLOCK)
JOIN dbo.tclaim_procedure cp WITH (NOLOCK) ON cp.claim_procedure_id = p.claim_procedure_id
JOIN dbo.TPEC_RGV_ProcedureType pt WITH (NOLOCK) ON pt.claim_procedure_id = p.claim_procedure_id
WHERE p.claimProc_ServiceType IS NULL
UNION ALL
SELECT p.claim_procedure_id, p.claimProc_ServiceType, cp.date_created, pt.vision_flag
FROM pec.tBRClaimLinePreCalc p WITH (NOLOCK)
JOIN dbo.tclaim_procedure cp WITH (NOLOCK) ON cp.claim_procedure_id = p.claim_procedure_id
JOIN dbo.TPEC_RGV_ProcedureType pt WITH (NOLOCK) 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)
Just swap in whatever columns you actually need, as long as both SELECT lists are identical.
One thing to double check with Steve's COALESCE(vision_flag, 2) + 1 simplification: it works perfectly if vision_flag only ever contains 0, 1, or NULL. If there are any other values hiding in that column (a 2, a 3, whatever), the math will produce results different from your original CASE. Quick sanity check would be:
SELECT DISTINCT vision_flag FROM dbo.TPEC_RGV_ProcedureType
If you only see 0, 1, and NULL you're good.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply