0

Is there anything I can do to improve this query performance? I have included the execution plan and the Server Profiler output. Is there a index that can help? I see there is a Index Seek that is taking some time can I do anything to help that? in the Server profile I see the reads looks high is can there be something to help that?

SELECT dbo.Templates.VersionKey
FROM            dbo.Templates INNER JOIN
                         dbo.Indications ON dbo.Templates.Id = dbo.Indications.TemplateId INNER JOIN
                         dbo.Sentences ON dbo.Indications.SentenceId = dbo.Sentences.Id INNER JOIN
                         dbo.SentenceParts ON dbo.Sentences.Id = dbo.SentenceParts.SentenceId INNER JOIN
                         dbo.SentencePartValues ON dbo.SentenceParts.Id = dbo.SentencePartValues.SentencePartId
WHERE        (dbo.Templates.Status <> 8) AND 
(dbo.Templates.IsCurrentVersion = 1) AND 
(dbo.Indications.IsActive = 1) AND 
(dbo.Indications.IsCurrentVersion = 1)  
AND (NOT (Sentences.[IsDeleted] = 1)) 
AND (NOT (SentenceParts.[IsDeleted] = 1)) 
AND (NOT (SentencePartValues.[IsDeleted] = 1)) 
and
(SUBSTRING(dbo.SentencePartValues.LiteralValue, 12, 36) = 'a2278339-8add-ff3d-8baa-9654b8b8d00b')

execution plan https://www.brentozar.com/pastetheplan/?id=By68aKtfp

SQL Server Profiler enter image description here

1
  • 1
    AND (NOT (Sentences.[IsDeleted] = 1)) etc might be better as AND Sentences.[IsDeleted] = 0 to avoid an inequality predicate. Oct 28, 2023 at 22:23

1 Answer 1

3

yes

Don't use substring in your where clause:

NUTS

All of the time in this query is spent seeking through 25,219,726 rows in SentencePartValues, then applying the filter/substring to LiteralValue.

Of course, it's easy to say that, but it doesn't do you much good in practice.

It may be easier for you to do a double-wildcard LIKE search, but if LiteralValue is an n/varchar(max), or sql_variant, then you're screwed either way.

You may need to add a computed column to the table and then index it appropriately for your query.

ALTER TABLE dbo.SentencePartValues ADD LV_substring AS TRY_CAST(SUBSTRING(dbo.LiteralValue, 12, 36) AS uniqueidentifier);

or

ALTER TABLE dbo.SentencePartValues ADD LV_substring AS TRY_CAST(SUBSTRING(dbo.LiteralValue, 12, 36) AS varchar(36));

The TRY_CAST to uniqueidentifier may fail if certain values end up in the substring that can't be converted.

Adding the column without the PERSISTED keyword will prevent a long blocking operation from occurring while you add the column. Of course, like I said, you'll need to index it for it to be useful in general.

CREATE INDEX 
    whatever 
ON dbo.SentencePartValues
    (LV_substring, SentencePartId)
INCLUDE
    (IsDeleted) 
WHERE 
    IsDeleted = 1
WITH
    (ONLINE = ON, SORT_IN_TEMPDB = ON);

You can use ONLINE = ON if you're on Enterprise Edition, otherwise you'll have to omit it and suffer.

2
  • Would a join on a SUBSTRING be better ?
    – Jefferson
    Oct 28, 2023 at 7:26
  • 2
    @Jefferson absolutely not. Oct 28, 2023 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.