August 7, 2020 at 5:21 am
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
August 7, 2020 at 12:43 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2020 at 1:25 pm
What is your thinking behind using
SELECT FROM (SELECT ... )
rather than a single SELECT?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 7, 2020 at 3:08 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply