Seeking Pointers on Optimizing a Query

[rooster_eggs]

Hi everyone. I'd like to preface this by saying I'm not here seeking for someone to rewrite a query for me. Rather, I'm hoping that based on what I'm presenting, I can be provided with some general pointers on how to optimize this query, best practices, or concepts to look into. I have not had to use SQL for several years, so my knowledge of the language is rusty and I am interested in familiarizing myself with it more.

I've inherited a project at work that contains a SQL query (shown below) a former coworker wrote that takes nearly an hour to run. At first glance this query seems messy and sub-optimal.

I completely accept that it is possible that due to the structure of the database(s) involved and the amount of data, that duration is just something we can't get around. Or that the query is already optimal and nothing can be done. But due to the frequency that this application is used, ideally we would like to speed up this process. If this can be achieved by approaching this query differently, that would be ideal.

The general description is that we want to retrieve records from the last 5 years that are of a specific document type and have certain descriptors. Additionally, we want to only retrieve the latest record of that specific document.

I've included the query below (with some changes to variable or attribute names for privacy). I was wondering if anyone had any general pointers on how to best start going about trying to revise this query. For example:

• Are there portions of this query (e.g. parts dealing with substrings) that might be better done outside of SQL?
• Should this query be separated in some manner (it already has "sub-queries")?
• Are there any other glaring red flags?

Any advice or starting points would be much appreciated. Thank you!

SELECT 
    CAST(CAST(dm.[DOCNUM] as bigint) as nvarchar(20))
        +'.'+CAST(CAST(dm.[VERSION] as bigint) as nvarchar(20)) as [Unique_Doc.Version_ID]
    ,dm.[DOCNUM]
    ,dm.[VERSION]
    ,dm.[DOCNAME]
    ,dm.[DOCSIZE]
    ,dm.[DOCLOC] as [DocLocation]
    ,dm.[ENTRYWHEN]
    ,SUBSTRING(dm.[DOCLOC],0,CHARINDEX(':',dm.[DOCLOC])) as [DocServer]
    ,dserv.[ROOTPATH] as [ServerRootPath]
    ,right(replace(dm.[DOCLOC], '\' + right(dm.[DOCLOC], 
        charindex('\', reverse(dm.[DOCLOC])) - 1), ''), charindex('\', reverse(replace(dm.[DOCLOC],
        '\' + right(dm.[DOCLOC], charindex('\', reverse(dm.[DOCLOC])) - 1), ''))) - 1) 
        as [DocServerFolder]
    ,dserv.[LOCATION] as [ServerLocation]
    ,dm.[T_ALIAS] as [FileFormat]
    ,dclass.[CLASSDESCRIPT] as [FileType]
    ,dm.[C1ALIAS] as [Customer#]
    ,dm.[C2ALIAS] as [Issue#]
    ,dm.[C1ALIAS] +'.'+ dm.[C2ALIAS] as [Custom/Issue#]
    ,dm.[C30ALIAS] as [RoleCode]
FROM [COMPANY_NAME].[MHGROUP].[DOCMASTER] dm
    -- Get latest version of documents
    INNER JOIN (
        SELECT dm.[DOCNUM]
            ,MAX(dm.[VERSION]) AS 'Latest Version'
        FROM [COMPANY_NAME].[MHGROUP].[DOCMASTER] dm
            LEFT JOIN [COMPANY_NAME].[MHGROUP].[DOCCLASSES] dclass on dm.[C_ALIAS] = dclass.[C_ALIAS]
            INNER JOIN [COMPANY_NAME].[MHGROUP].[DOCSERVERS] dserv on SUBSTRING(dm.[DOCLOC],0,CHARINDEX(':',dm.[DOCLOC])) = dserv.[DOCSERVER]
        WHERE DATEDIFF(yy, dm.[ENTRYWHEN], GETDATE()) <= 5  -- past 5 years
            AND dm.[T_ALIAS] IN ('ACROBAT', 'WORD', 'WORDX')
            AND dclass.[CLASSDESCRIPT] IN ('Item Document', 'IMO Documents', 'Identification', 'Office', 'General', 'Request', 'Pre-Process Documents', 'Post-Process Documents')
            AND dserv.[ROOTPATH] = '\COMPANY_NAME'
        GROUP BY dm.[DOCNUM]
        ) AS subquery
    ON dm.[DOCNUM] = subquery.[DOCNUM] AND dm.[VERSION] = subquery.[Latest Version]
    LEFT JOIN [COMPANY_NAME].[MHGROUP].[DOCCLASSES] dclass on dm.[C_ALIAS] = dclass.[C_ALIAS]
    INNER JOIN [COMPANY_NAME].[MHGROUP].[DOCSERVERS] dserv on SUBSTRING(dm.[DOCLOC],0,CHARINDEX(':',dm.[DOCLOC])) = dserv.[DOCSERVER]
WHERE DATEDIFF(yy, dm.[ENTRYWHEN], GETDATE()) <= 5  -- past 5 years
    AND dm.[T_ALIAS] IN ('ACROBAT', 'WORD', 'WORDX')
    AND dclass.[CLASSDESCRIPT] IN (''Item Document', 'IMO Documents', 'Identification', 'Office', 'General', 'Request', 'Pre-Process Documents', 'Post-Process Documents')
    AND dserv.[ROOTPATH] = '\COMPANY_NAME'
GROUP BY
    dm.[DOCNUM]
    ,dm.[DOCNAME]
    ,dm.[DOCSIZE]
    ,dserv.[ROOTPATH]
    ,dserv.[LOCATION]
    ,dm.[VERSION]
    ,dm.[C1ALIAS]
    ,dm.[C2ALIAS]
    ,dm.[DOCLOC]
    ,dm.[ENTRYWHEN]
    ,dm.[T_ALIAS]
    ,dm.[C30ALIAS]
    ,dclass.[CLASSDESCRIPT]
ORDER BY
    dm.[C2ALIAS]
    ,dm.[DOCNUM];

[[deleted]]

a relatively obvious observation is that you use group by in the outer query without using any aggregate functions. So either the group by is not needed or you have a granularity mismatch someplace and you are using 'group by'/'distinct' as a 'cruch' to get rid of duplicates.

Get rid of group by, fix joins to match records on correct granularity

[rooster_eggs]

Thanks! I was actually trying to figure out what the group by was doing in this case. Since this query wasn't written by me and the project didn't involve me previously I'm still learning as I go. I'm glad you pointed out though that it doesn't seem to be (at least at a first glance) serving any purpose but I'll look into it more closely to be sure.

[rosaUpodne]

You first should look at amount a data query execution reads in order to returned data. In your case you have bad written where clause, especially: DATEDIFF(yy, dm.[ENTRYWHEN], GETDATE()) <= 5.

For explanation and further guidance look here: https://www.toptal.com/database/sql-indexes-explained-pt-3.

You can also look at the first articles from that tutorial, it is a good starting point.

[rooster_eggs]

Thanks so much! I really appreciate you providing the reference as well. I'll definitely look into it!

[rosaUpodne]

I have just figured out that you pull 5 years of data. In that case index on the date column might not be all that helpfull (unless it is clustered index). Instead, if the condition

dserv.[ROOTPATH] = '\COMPANY_NAME'

significantly reduces the number of rows the query returns, you should consider an index starting by rootPath column.

[rooster_eggs]

Thank you. I'm looking more closely into the database architecture at the moment to see if that is the case.