Testing waters and why not with a real simple execution plan

ivanrdgz 2013-03-08 16:32:54

This query is coming from a SAP Small Business One application so probably I won't be able to change the actual query but if you have suggestions for rewritting it I will gladily accept them as well. This query run very often.

Goal – make thinner that fat line! ๐Ÿ™‚

Thank you!

Ivan

Aaron Bertrand 2013-03-08 18:12:46
Also please note that you use LEFT JOIN to t1, but then mention t1 in a WHERE clause. This turns your LEFT JOIN into an INNER JOIN. Not that this will make that fat line thinner, but in case you believe it is operating as an outer join, it is not.
SQLkiwi 2013-03-08 18:10:45
Add a computed column (not persisted, takes no space in the table):

ALTER TABLE dbo.TLOG
ADD cc01 AS ISNULL([Priority], 2);

The optimal index is then:

CREATE INDEX TLOG_Status_cc01_LogNum_SchDate_NC_IDX
ON dbo.TLOG ([Status], cc01, LogNum, SchDate)
INCLUDE ([Object], [Company], [Command], [Priority], [ErrLng], [Draft])

Strictly, you do not need SchDate in the key, it can be an included column. This is especially true if there is a correlation between SchDate and LogNum. The core part of the query is:

SELECT TOP (1)
    t.LogNum,
    t.Company,
    t.[Object],
    t.Command,
    t.[Priority],
    t.SchDate,
    t.ErrLng,
    t.Draft
FROM dbo.TLOG AS t
WHERE
    t.[Status] = 'C'
    AND t.[Object] <> 81
    AND (t.SchDate IS NULL OR t.SchDate <= CURRENT_TIMESTAMP)
ORDER BY
    ISNULL([Priority], 2), LogNum

For which the execution plan is:

Estimated plan

Unless the optimizer is having a very bad day, this should also reduce your 'fat line' in the original plan to one row without changing the query text at all.

ivanrdgz 2013-03-08 19:59:55
I am not sure I will be able to alter the table but it is a valid suggestion. Thank you for your input.
SQLkiwi 2013-03-08 21:09:23
If you can't alter the table or the query, you'll be struggling ๐Ÿ™‚
ivanrdgz 2013-03-08 21:21:16
I will make the suggestion to the vendor. I am just afraid their application might break since I have seen a lot of SELECT * coming from their application. By the way I already tested in an test environment and the execution plan is now in excellent shape.
SQLkiwi 2013-03-08 21:24:08
Great news, thank you. Good luck with the vendor conversation.