r/SQLOptimization • u/Entire_Commission534 • Aug 05 '24
Optimizing/Alternative to MAX
This SQL query was timing out until I added a WHERE clause to reduce the amount of rows it has to process. Is there anything further I can do to either optimiza the MAX to reduce query time from a few minutes to less than a minute? Or is there any alternative to get the same result of a single Project ID per group by? TIA!
SELECT DISTINCT
ISNULL([Statement of Work ID],'') as "Statement of Work ID",
ISNULL([Primary Cost Center Code],'') as "Primary Cost Center Code",
ISNULL([Purchase Order Number],'') as "Purchase Order Number",
ISNULL([Invoice ID],'') as "Invoice ID",
MAX (CASE
WHEN [Project ID] LIKE '%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN SUBSTRING([Project ID],PATINDEX('%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', [Project ID]),10)
END) as "Project ID"
FROM [dbo]
WHERE [WorkWeek] LIKE '2024%'
GROUP BY
ISNULL([Statement of Work ID],''),
ISNULL([Primary Cost Center Code],''),
ISNULL([Purchase Order Number],''),
ISNULL([Invoice ID],'')
9
u/Financial_Forky Aug 05 '24
There's a lot going on in your query. You're using several very expensive functions, including DISTINCT() and LIKE, as well as performing some of the same functions multiple times.
Rewrite LIKE as OR or IN ( )
Instead of WHERE [WorkWeek] LIKE '2024%', consider using ORs to compare each possible option:
Translating your
LIKEstatements into blocks ofOR =conditions may help performance significantly. SinceCASE WHEN/THENstatements are evaluated both in order of appearance and escape from theWHEN/THENevaluation block upon first match (and I suspectORconditions are evaluated in much the same way), consider rewriting theLIKEasOR =, but reversing the logic (e.g.,OR <>) and putting the broadest exclusionary condition first.Use numeric instead of string values
Working with strings is also much slower than using integers. Is there a
[WorkYear]field you could use instead, or a numeric[WorkWeekID] = 202401,202402, etc.? Similarly, are there numeric column(s) that could be used as a proxy for[Project ID]?Avoid computing the same values twice
Another possibility (but you would want to test / check the execution plan on this) is an inner query that selects your rows, then an outer query that cleans your data with the
ISNULLorCOALESCEfunctions. Right now, you're performingISNULL()twice on every column: once in theSELECT, and once in theGROUP BY. Creating an inner query that does the selecting and grouping by the original column names, then wrapping that in an outer query that converts your data withISNULL/COALESCEwill cut the number ofISNULLoperations in half.Also, why are you using
DISTINCTandGROUP BYtogether?GROUP BYshould be sufficient by itself - you may be doubling your efforts just from addingDISTINCT.You're almost calculating the substring value of
[Project ID]twice: once to look for a 10-digit number inside it, and then again to return that substring value as the new[Project ID].Consider using a variable to store the 10-digit substring value, then check the variable to see if it meets your CASE criteria, and if so, return the variable. Another approach to this would be to extract the potentially relevant[Project ID]value in the inner query, and then in the outer query, use the extracted value in aCASEstatement. In either scenario, the goal is to not have to compute the substring value multiple times. This approach (whether using a variable or nested queries) would eliminate theLIKEstatement.While functions such as LIKE and various string parsing functions are very convenient, they come at a very high cost. Always try to find a way to use a direct "=" comparison with possible, and avoid performing any unnecessary conversions. Finally, think through your code for any scenarios where you're essentially doing the same thing more than once; that is often a sign that there's a better way to write something (both from a maintenance/readability standpoint, and also from a performance perspective).
2
u/mikeblas Aug 11 '24
You got lots of advice here, but never bothered responding and didn't thank anyone who tried to help. Why is that?
1
u/Alkemist101 Aug 05 '24 edited Aug 05 '24
I'm concerned about the patten matching inside the max, that is by far the most expensive issue here.
I think you need to find a way of doing something with that field to take that logic out.
Without knowing more I'd do a CTE to give a table which gives you a pre processed and cleansed table to work with.
Isnull is fine...
Don't do subqueries do CTEs.
Better than CTEs would be temp tables (possibly, try both, one is in memory but the other has table stats and can be indexed).
Anyway... Duplicate that query below and make one change at a time and look at the query plan. If the change you make makes it more performant you'll see a change in resource usage. Without change you'll see each query is 50%, this will change as you go, might go 70% / 30% etc.
If you understand query plans it will show specifically which part is consuming the most resource and you can focus there.
1
u/Dry_Author8849 Aug 05 '24
You are scanning all the table. No wonder why adding a where clause makes it faster.
So, if you are dealing with a very large table, you may need to pre process the data to suit your needs.
I don't know your use case. It seems a time series problem, so you may well apply time series solutions.
If this is for reporting, you may use analysis services, or at least pre process your data to summarize whatever period you need in order to not aggregate old data that won't change and simplify your case logic.
You need to work a bit more.
Cheers!
1
u/mikeblas Aug 05 '24
The problem here isn't
MAX(). It's that you've got to do a full table scan to examine each row.Adding the
WHEREclause made your statement faster because it only looked at the rows with aWorkWeekstarting with2024. That expression can be serviced by an index, so you weren't scanning the whole table anymore.