Query optimization

  • curious_sqldba

    SSC-Dedicated

    Points: 36310

    Hello..i am running into an issue where below query is requesting large amount of memory grant. I think the problem is at the left join inner query on table dn_t it has to do a full scan because there is no index. Wondering if there is any better way to write this?

     

    SELECT [P2].[c1]                   AS [C1], 
    [P2].[did] AS [did],
    [P2].[dcn] AS [Dcn],
    [P2].[originalfilename] AS [OriginalFileName],
    [P2].[filenamefull] AS [FileNameFull],
    [P2].[processdate] AS [ProcessDate],
    [P2].[filepath] AS [FilePath],
    [P2].[filecreationdate] AS [FileCreationDate],
    [P2].[schemafile] AS [SchemaFile],
    [P2].[xcontentcontainer] AS [xContentContainer],
    [P2].[filingreceived_dt] AS [FilingReceived_dt],
    [P2].[filing_dt] AS [Filing_dt],
    [P2].[formtype] AS [Formtype],
    [P2].[tid] AS [tId],
    [P2].[description] AS [Description],
    [P2].[Tdisplayname] AS [TDisplayName],
    [P2].[c2] AS [C2],
    [P2].[did1] AS [did1],
    [P2].[nid] AS [nid],
    [P2].[dprefix] AS [dPrefix],
    [P2].[isrootschema] AS [isRootSchema],
    [P2].[nid1] AS [nid1],
    [P2].[elementn] AS [Elementn],
    [P2].[commonn] AS [Commonn],
    [P2].[preferredprefix] AS [PreferredPrefix]
    FROM (SELECT [E1].[did] AS [did],
    [E1].[dcn] AS [Dcn],
    [E1].[originalfilename] AS [OriginalFileName],
    [E1].[filenamefull] AS [FileNameFull],
    [E1].[processdate] AS [ProcessDate],
    [E1].[filepath] AS [FilePath],
    [E1].[filecreationdate] AS [FileCreationDate],
    [E1].[schemafile] AS [SchemaFile],
    [E1].[xcontentcontainer] AS [xContentContainer],
    [E1].[filingreceived_dt] AS [FilingReceived_dt],
    [E1].[filing_dt] AS [Filing_dt],
    [E1].[formtype] AS [Formtype],
    [E1].[tid] AS [tId],
    [E1].[description] AS [Description],
    [E1].[Tdisplayname] AS [TDisplayName],
    1 AS [C1],
    [J1].[did] AS [did1],
    [J1].[nid1] AS [nid],
    [J1].[dprefix] AS [dPrefix],
    [J1].[isrootschema] AS [isRootSchema],
    [J1].[nid2] AS [nid1],
    [J1].[elementn] AS [Elementn],
    [J1].[commonn] AS [Commonn],
    [J1].[preferredprefix] AS [PreferredPrefix],
    CASE
    WHEN ( [J1].[did] IS NULL ) THEN Cast(NULL AS INT)
    ELSE 1
    END AS [C2]
    FROM [dbo].[d_t] AS [E1]
    LEFT OUTER JOIN (SELECT [E2].[did] AS
    [did],
    [E2].[nid] AS
    [nid1],
    [E2].[dprefix] AS
    [dPrefix]
    ,
    [E2].[isrootschema] AS [isRootSchema],
    [E3].[nid] AS [nid2],
    [E3].[elementn] AS [Elementn],
    [E3].[commonn] AS [Commonn],
    [E3].[preferredprefix] AS [PreferredPrefix]
    FROM [dbo].[dn_t] AS [E2]
    INNER JOIN [dbo].[n_t] AS [E3]
    ON [E2].[nid] =
    [E3].[nid]) AS [J1]
    ON [E1].[did] = [J1].[did]
    WHERE ( EXISTS (SELECT 1 AS [C1]
    FROM [dbo].[d_t] AS [E4]
    WHERE ( [E4].[dcn] LIKE
    '5e483e878556ea4138a9294674af123131'
    ESCAPE '~' )
    AND ( [E4].[did] =
    [E1].[did] )
    ) )
    OR ( [E1].[tid] =
    'D265498756-BCED-44FB-8151-5E1006548946' ))
    AS [P2]
    ORDER BY [P2].[did] ASC,
    [P2].[c2] ASC
  • Grant Fritchey

    SSC Guru

    Points: 396714

    Have you looked at the execution plan? I'd say it's probably the LIKE statement. However, you need to determine how SQL Server is resolving your query and that answer is in the execution plan. If it's scanning large tables or not using indexes you think it should, or maybe it's just about row estimates & statistics, use of tempdb for the ORDER BY... Hard to say without the execution plan.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Phil Parkin

    SSC Guru

    Points: 244739

    What is your thinking behind using

    SELECT FROM (SELECT ... )

    rather than a single SELECT?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jonathan AC Roberts

    SSCoach

    Points: 17335

    It's a bit of a long winded way to write that query. You could write it like this:

        SELECT 1                           AS [C1], 
    [E1].[did] AS [did],
    [E1].[dcn] AS [Dcn],
    [E1].[originalfilename] AS [OriginalFileName],
    [E1].[filenamefull] AS [FileNameFull],
    [E1].[processdate] AS [ProcessDate],
    [E1].[filepath] AS [FilePath],
    [E1].[filecreationdate] AS [FileCreationDate],
    [E1].[schemafile] AS [SchemaFile],
    [E1].[xcontentcontainer] AS [xContentContainer],
    [E1].[filingreceived_dt] AS [FilingReceived_dt],
    [E1].[filing_dt] AS [Filing_dt],
    [E1].[formtype] AS [Formtype],
    [E1].[tid] AS [tId],
    [E1].[description] AS [Description],
    [E1].[Tdisplayname] AS [TDisplayName],
    CASE
    WHEN [J1].[did] IS NULL THEN CAST(NULL AS INT)
    ELSE 1
    END AS [C2],
    [J1].[did] AS [did1],
    [J1].[nid1] AS [nid],
    [J1].[dprefix] AS [dPrefix],
    [J1].[isrootschema] AS [isRootSchema],
    [J1].[nid2] AS [nid1],
    [J1].[elementn] AS [Elementn],
    [J1].[commonn] AS [Commonn],
    [J1].[preferredprefix] AS [PreferredPrefix]
    FROM [dbo].[d_t] AS [E1]
    OUTER APPLY (SELECT [E2].[did] AS [did],
    [E2].[nid] AS [nid1],
    [E2].[dprefix] AS [dPrefix],
    [E2].[isrootschema] AS [isRootSchema],
    [E3].[nid] AS [nid2],
    [E3].[elementn] AS [Elementn],
    [E3].[commonn] AS [Commonn],
    [E3].[preferredprefix] AS [PreferredPrefix]
    FROM [dbo].[dn_t] AS [E2]
    INNER JOIN [dbo].[n_t] AS [E3]
    ON [E2].[nid] = [E3].[nid]
    WHERE [E1].[did] = [E2].[did]
    ) AS [J1]
    WHERE EXISTS (SELECT *
    FROM [dbo].[d_t] AS [E4]
    WHERE [E4].[dcn] LIKE '5e483e878556ea4138a9294674af123131' ESCAPE '~'
    AND [E4].[did] = [E1].[did])
    OR [E1].[tid] = 'D265498756-BCED-44FB-8151-5E1006548946'
    ORDER BY [E1].[did] ASC,
    CASE WHEN ( [J1].[did] IS NULL ) THEN CAST(NULL AS INT) ELSE 1 END ASC

    Run it showing the execution plan, then look what you need to do to optimise the query.

     

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply