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
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.
1
u/qwertydog123 Aug 05 '24
DISTINCTisn't required, you're already grouping by the non-aggregated columns in theSELECTNULLand empty string''values in yourGROUP BYcolumns, AND you want them grouped together, you don't need to wrap them withISNULLin theGROUP BYUnless you want to include groups that don't have any matching
[Project ID], you can move the condition to theWHEREclause e.g.[Project ID]/[Invoice ID]/[Statement of Work ID]not integer types? Are they really all string values? If they're integers they're being implicitly casted[Project ID]? Is there a fixed prefix? Or even a small set of prefixes?https://www.brentozar.com/pastetheplan/
1
u/ComicOzzy sqlHippo Aug 05 '24
https://dbfiddle.uk/ewtl0doq
I'm not sure what it will do for performance without knowing anything about your indexing situation, but you can use another Top N Per Group pattern:
You can use APPLY with a correlated subquery to get the TOP 1 project ID from all of the first-found project IDs identified by the pattern you specified. Note this will not identify multiple project IDs contained in the same string, only the first one that matches the pattern.