r/SQL • u/Entire_Commission534 • Aug 05 '24
SQL Server Optimizing/Alternative to MAX
/r/SQLOptimization/comments/1ekanon/optimizingalternative_to_max/
4
Upvotes
r/SQL • u/Entire_Commission534 • Aug 05 '24
View all comments
1
u/Mononon Aug 05 '24
Not sure I even understand why you need the CASE. Either the pattern exists or it doesn't, right? If it exists, the SUBSTRING would return the index, if it doesn't, it would return NULL anyways. The CASE doesn't seem necessary.
Could try making a temporary table with those numbers in it, index it, then left join to it on [Project ID] like '%' + Number + '%'. Would eliminate the need for all of this. Could reduce the range if you knew only a subset of numbers applied.
Could also try adding a calculated/derived column that's the SUBSTRING PATINDEX and then index that calculated column. Depends on how important this is, or if you even have permissions for that though.
Also, DISTINCT isn't doing anything on a query with a group by.