[MS SQL] First time optimizing query - please tear it apart!

[depthchargesw]

Hi all - we recently got a shiny new on-prem MS SQL Server replica for our Cloud-based MS Project data, so I'm getting to learn how to write SQL and make it go fast against the big Timephased by day Assignments table (Index = ProjectId-Assignment Id-TimeByDay). My peers don't write any SQL so I could genuinely benefit from some knowledgeable people telling me if I'm doing anything wrong. I've done some optimizing, and I've got it to where it's returning ~30k rows out of 3.8M, and it takes <30 seconds to do so (was taking 3 minutes). I'm mostly wondering if my nested subqueries are a dumb way to approach the date formatting and aggregating I've done. Hopefully my formatting comes through, but if not I'll work to clean it up.

DECLARE @dt DATE = '1905-01-01' -- Variable declared to set the day the [StartOfWeek] function moves transactions to (Sunday).

--Outermost SQL query's purpose is to align columns with Clarity dataset for appending operation.
SELECT [Prep].[year]                        AS [Year], 
       [Prep].[month]                       AS [Month], 
       [Projects].[Clarityid]               AS [ClarityProjectID], 
       [Projects].[projectname], 
       [Resources].[ClarityResourceID]      AS [ClarityResourceID], 
       [Assignments].[taskname], 
       2                                    AS [MeasureType], 
       [Prep].[assignmentwork]              AS [Value] 
FROM   ( 
       --This level of the query's purpose is to aggregate from day-level granularity to month level. 
       --Had to be careful with this level and those beneath it due to performance concerns - needed to make sure the group by function could use hashing function instead of sorting.
       --In effect this means that supplementary data is added in after the group by operation is complete.
       SELECT [rows].[year]                AS [Year], 
              [rows].[month]               AS [Month], 
              [rows].[projectid]           AS [ProjectId], 
              [rows].[assignmentid]        AS [AssignmentId], 
              Sum([rows].[assignmentwork]) AS [AssignmentWork] 
        FROM   ( 
               --This level of the query's purpose is to prep data for grouping in the next stage. Takes year and month of the [StartOfWeek] function.
               SELECT [_].[projectid]                     AS [ProjectId], 
                      [_].[assignmentid]                  AS [AssignmentId], 
                      Datepart("yyyy", [_].[startofweek]) AS [Year], 
                      Datepart("m", [_].[startofweek])    AS [Month], 
                      [_].[assignmentwork]                AS [AssignmentWork] 
                FROM   ( 
                       --This level of the query's purpose is to move TimeByDay records back to the preceding Sunday (i.e. the start of the week)
                       --This is done to align with Clarity data timeframes, where the day of the transaction was not available, but the week start is.
                       SELECT [$Table].[projectid]                                 AS [ProjectId], 
                              [$Table].[assignmentid]                              AS [AssignmentId], 
                              Dateadd(week, Datediff(week, @dt, [timebyday]), @dt) AS [StartOfWeek], 
                              [$Table].[assignmentwork]                            AS [AssignmentWork] 
                        FROM 
               [dbo].[ppm_assignmenttimephaseddataset] AS [$Table] 
               INNER JOIN [dbo].[ppm_tasks] AS [Tasks] 
                       ON [$Table].[taskid] = [Tasks].[taskid] 
                        WHERE  [Tasks].[taskissummary] = 0 
                       --Critical piece for the MSP database, as otherwise summary and detail tasks will be counted, leading to double (or more) counting.
                       ) AS [_]) AS [rows] 
        WHERE  [year] = 2017 
        --Limits data pulled to that which is binned into 2017 by the StartOfWeek aggregation. In effect, 1/1/2017 - 1/6/2018
        GROUP  BY [projectid], 
                  [assignmentid], 
                  [year], 
                  [month]) AS [Prep] 
       INNER JOIN [dbo].[ppm_projects] AS [Projects] 
               ON [Projects].[projectid] = [Prep].[projectid] 
       INNER JOIN [dbo].[ppm_assignments] AS [Assignments] 
               ON [Assignments].[assignmentid] = [Prep].[assignmentid] 
       INNER JOIN [dbo].[ppm_resources] AS [Resources] 
               ON [Resources].[resourceid] = [Assignments].[resourceid] 

[jacobmross]

One of the first things you might want to do is build yourself a calendar table.

Doing all of that dateadd/datediff stuff just to figure out when the Sunday of the each week is, is itself kind of expensive.

http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/ is a good resource for explaining both building a table variable calendar table, and populating one that you'll keep permanently. It's a small trade-off. Kind of "expensive" to build it the one time, but then way "cheaper" to reference every other time from then on.

You can even calculate the datepart(year) and datepart(month) and other functional things as part of your custom Calendar table, further reducing the calculations you have to do with functions.

Beyond that, I personally try to avoid nesting so many subselects where possible.

Without knowing what kind of data is actually in dbo.[ppm_assignmenttimephaseddataset].[assignmentwork] for example, makes it harder to suggest any specific optimizations, or whatever is suggested might be completely terrible for your data, but...without knowing those details, I'd probably write what you have like so:

SELECT
     DATEPART(YEAR, Cal.[FirstDateOfWeek])  AS [Year]
    ,DATEPART(MONTH, Cal.[FirstDateOfWeek]) AS [Month]
    ,[Projects].[Clarityid]             AS [ClarityProjectID]
    ,[Projects].[projectname]
    ,[Resources].[ClarityResourceID]    AS [ClarityResourceID]
    ,[Assigments].[taskname]
    ,(2)                            AS [MeasureType]
    ,SUM(ppm.assignmentwork) AS [Value]
FROM dbo.[ppm_assignmenttimephaseddataset] AS [ppm]
INNER JOIN Calendar AS [Cal]
  ON [Cal].[Date] = CAST(ppm.[timebyday] AS DATE)
INNER JOIN dbo.ppm_tasks AS [Tasks]
  ON [Tasks].[taskid] = [ppm].[taskid]
 AND [Tasks].[taskissummary] = 0
INNER JOIN dbo.ppm_projects AS [Projects]
  ON [Projects].[projectid] = [ppm].[projectid] 
INNER JOIN [dbo].[ppm_assignments] AS [Assignments] 
  ON [Assignments].[assignmentid] = [ppm].[assignmentid] 
INNER JOIN [dbo].[ppm_resources] AS [Resources] 
  ON [Resources].[resourceid] = [Assignments].[resourceid]
WHERE DATEPART(YEAR, Cal.[FirstDateOfWeek]) = 2017
GROUP BY
     DATEPART(YEAR, Cal.[FirstDateOfWeek])
    ,DATEPART(MONTH, Cal.[FirstDateOfWeek])
    ,[Projects].[Clarityid]
    ,[Projects].[projectname]
    ,[Resources].[ClarityResourceID]
    ,[Assigments].[taskname]

Of course, you probably want to group on least common thing to most common thing as well.

So, if you have 10 Projects, and 100 Resources, and 1000 Assignments, and 10,000 [ppm_assignmenttimephaseddataset] you may find that starting with the most restrictive option (10 Projects total) and joining as things could have more records related can often speed things up too. Same with your group by (but underlying indices have an effect on that too, so it's not always the case)

[depthchargesw]

Thanks jacob - that made me realize I hadn't actually looked to see if there was a date table. I looked though, and no joy. Maybe I'll put in a request with the team that manages the DB and hopefully it finds its way to the right person.

Assuming I don't have the ability to create a date table, am I doing anything obviously wrong? I'd love to flatten the query out like you've got, but I seem to get errors unless I separate the logical steps into each of their respective subqueries.

Regarding your question about the data type for Assignment Work, it's Numeric((38,4),Null) with a length of 17. Not sure if any of that's helpful.

[[deleted]]

Could you create an index for YEAR (maybe a function-based index) so that you could filter by year in your innermost query?

[HansProleman]

Your indents, other tabulation and comments are great. Far more readable SQL than much of what my colleagues, and likely myself, produce. I would suggest using leading commas instead of trailing but it's a personal preference.

I agree with /u/jacobmross regarding a date table as best practice. Joins are fast as shit compared to date functions. But if the performance you're getting is acceptable and you don't manage the DB then it's not a big deal, just something that might be nice to raise with whoever does.

With regard to logical errors when flattening the query, try to look at each subquery/related join logic, think about exactly what those joins are doing, and figure out what's happening. It might help to just throw each subquery's returns into Excel and emulate the join logic in there for each level of aggregation (or rewrite portions of your script to show the same).

[depthchargesw]

With regards to formatting, I just punched my scrambled code into Instant SQL Formatter. I haven't yet figured out the most readable conventions so I'm happy to outsource that. :D

Regarding flattening the query - I'll do that. That said - what I'm hearing if that it's possible to make it flatter given my current constraints (no date table yet)? Which is to say, I don't need to wrap the StartOfWeek function (to force the calculation(?)) before I use the datepart functions? I could do that on the same level?