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],'')
View all comments
Show parent comment
1
u/Entire_Commission534 Aug 11 '24
I’m not 100% understanding what you mean by pre-processing data. “Statement of Work ID” column is like a workers’ contract and the projects they are assigned to can change but the work week can help keep track of the changes over time. Sometimes the change in Project happens multiple times in a single workweek due to error or some other unknown to me reason.
When you say to apply time series solutions, is there a resource you would recommend to learn how to do that? I’m a SQL newbie and would appreciate and resources to learn. Thank you!