Optimizer Building different Execution plans

Question

Optimizer Building different Execution plans

jody.a.morgan

SSC Rookie

Points: 27
More actions
October 28, 2015 at 6:50 am

#304631

I have a query that contains a base table and 3 inner joins 2 cross applys and one left outer join.
Here is my conundrum. I know the optimizer is supposed to pick the best execution path for the query but if I move the Left outer join though out the join sequence I get different execution plans and execution times.
As I understand it, this should not happen.
Can someone advise me as to what may be the issue that I am seeing?
Unfortunately I work for an institution that would terminate me and the sue me for posting any SQL from their source here but the following should be a good representation.
SELECT DISTINCT TOP 1000
[MT].[PKFKID] --CHAR(12)
, [MT].[ReferenceID] --CHAR(9)
, [MT].[PKFK_Item] --CHAR(4)
, [MT].[ADescription] --VARCHAR(45)
, [MT].[AType] --CHAR(9)
, [SPTCRT].[ParentDescription] AS [Upper_Description] --VARCHAR(80)
, [STCRT].[ProductDescription] --VARCHAR(30)
, .[Name] AS [SellerName] --VARCHAR(80)
, .[NameID] AS [SellerNameID] --INT
, [MT].[RefID] --VARCHAR(12)
, [MT].[AttributeDescriptor] --CHAR(9)
, [MT].[UpDate] --DATETIME
, [MT].[FinalizingDate] --DATETIME
, CONVERT(MONEY, [GPP].[Value]) AS [CurrentValue]
, [GPP].[ValueDate] AS [ValueAsOfDate] --Date
, [PA].[ItemQuantity] --DECIMAL(19,6)
, [PA].[ItemQuantityActive] --DECIMAL(19,6)
, [PA].[CustomersCount] --INT
, [ActiveCustomers] -- INT
, CONVERT(MONEY, ([GPP].[Value] * [PA].[ItemQuantity])) AS [TotalItemQuantity]
, CONVERT(MONEY, ([GPP].[Value] * [PA].[ItemQuantityActive])) AS [TotalItemQuantityActive]
, [GFLPD].FirstDate --DATETIME
, [GFLPD].LastDate --DATETIME
FROM
[dbo].[Syn_MaterTabe] AS [MT] WITH (NOLOCK)
LEFT OUTER JOIN [dbo].[Syn_ParentTypeCodeRefTable] AS [SPTCRT] WITH (NOLOCK) --8060 Milliseconds
ON [MT].[AType] = [SPTCRT].[ParentType]
AND [MT].[PKFK_Item] = [SPTCRT].[PKFK_Item]
INNER JOIN [dbo].[Syn_TypeCodeRefTable] AS [STCRT] WITH (NOLOCK)
ON [MT].[AType] = [STCRT].[AType]
AND [MT].[PKFK_Item] = [STCRT].[PKFK_Item]
--LEFT OUTER JOIN [dbo].[Syn_ParentTypeCodeRefTable] AS [SPTCRT] WITH (NOLOCK) --6260 Milliseconds
-- ON [MT].[AType] = [SPTCRT].[ParentType]
-- AND [MT].[PKFK_Item] = [SPTCRT].[PKFK_Item]
INNER JOIN [dbo].[Seller] AS WITH (NOLOCK)
ON .[PKFKID] = [MT].[PKFKID]
AND .[PKFK_Item] = [MT].[PKFK_Item]
--LEFT OUTER JOIN [dbo].[Syn_ParentTypeCodeRefTable] AS [SPTCRT] WITH (NOLOCK) --5083 Milliseconds
-- ON [MT].[AType] = [SPTCRT].[ParentType]
-- AND [MT].[PKFK_Item] = [SPTCRT].[PKFK_Item]
INNER JOIN [dbo].[ProductAggregation] AS [PA] WITH (NOLOCK)
ON [PA].[PKFK_Item] = [MT].[PKFK_Item]
AND [PA].[PKFKID] = [MT].[PKFKID]
--LEFT OUTER JOIN [dbo].[Syn_ParentTypeCodeRefTable] AS [SPTCRT] WITH (NOLOCK) --4273 Milliseconds
-- ON [MT].[AType] = [SPTCRT].[ParentType]
-- AND [MT].[PKFK_Item] = [SPTCRT].[PKFK_Item]
CROSS APPLY [dbo].[GetProductPrice]([MT].[PKFKID], [MT].[PKFK_Item], [dbo].[GetDateOnly]()) AS [GPP]
--LEFT OUTER JOIN [dbo].[Syn_ParentTypeCodeRefTable] AS [SPTCRT] WITH (NOLOCK) --5186 Milliseconds
-- ON [MT].[AType] = [SPTCRT].[ParentType]
-- AND [MT].[PKFK_Item] = [SPTCRT].[PKFK_Item]
CROSS APPLY [dbo].[GetFirstLastPurchaseDates([MT].[PKFKID], [MT].[PKFK_Item]) AS [GFLPD]
--LEFT OUTER JOIN [dbo].[Syn_ParentTypeCodeRefTable] AS [SPTCRT] WITH (NOLOCK) --7300 Milliseconds
-- ON [MT].[AType] = [SPTCRT].[ParentType]
-- AND [MT].[PKFK_Item] = [SPTCRT].[PKFK_Item]

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

The queries are the same to you but they are different queries to SQL Server, hence different execution plans.

Right-click on the leftmost operator - the SELECT - and examine the property sheet for "good enough plan found", "full" or "timeout".

Which is it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jody.a.morgan SSC Rookie Points: 27 More actions · Answer 2

If I select the "Select" in the Execution plans and click properties the "Reason for Early Termination of Statement Optimization" is "Time Out"

Alan Burstein SSC Guru Points: 61152 More actions · Answer 3

if I move the Left outer join though out the join sequence I get different execution plans and execution times.

Depending on your table structure, moving the LEFT JOIN may change the result set and therefore change the query plan.

SELECT a.x FROM a

INNER JOIN b ON a.x=b.x

LEFT OUTER JOIN c ON a.x=c.x

is not always the same thing as

SELECT a.x FROM a

LEFT OUTER JOIN c ON a.x=c.x

INNER JOIN b ON a.x=b.x

Also, I don't know if you are really using that top clause but, without an ORDER BY its a non-deterministic TOP clause. That could affect the plan.

Lastly, on a separate note, when you use NOLOCK table hints the right answer is no longer guaranteed by SQL Server. You may want to consider losing those.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

jody.a.morgan (10/28/2015)
If I select the "Select" in the Execution plans and click properties the "Reason for Early Termination of Statement Optimization" is "Time Out"

The query is too complex for SQL Server to be sure that the chosen plan is good enough. The result set will be correct but the execution time is likely to be poor and small changes to the query could result in completely different plans - which is what you are seeing. Try simplifying your query - perhaps run a logical chunk into a #temp table.

This, for instance:

SELECT

[MT].[PKFKID] --CHAR(12)

, [MT].[ReferenceID] --CHAR(9)

, [MT].[PKFK_Item] --CHAR(4)

, [MT].[ADescription] --VARCHAR(45)

, [MT].[AType] --CHAR(9)

, [MT].[RefID] --VARCHAR(12)

, [MT].[AttributeDescriptor] --CHAR(9)

, [MT].[UpDate] --DATETIME

, [MT].[FinalizingDate] --DATETIME

, [GPP].[Value]

, [GPP].[ValueDate] AS [ValueAsOfDate] --Date

, [ActiveCustomers] -- INT

, [TotalItemQuantityActive]

, [GFLPD].FirstDate --DATETIME

, [GFLPD].LastDate --DATETIME

INTO #Syn_MaterTabe

FROM [dbo].[Syn_MaterTabe] AS [MT] --WITH (NOLOCK)

CROSS APPLY [dbo].[GetProductPrice]([MT].[PKFKID], [MT].[PKFK_Item], [dbo].[GetDateOnly]()) AS [GPP]

CROSS APPLY [dbo].[GetFirstLastPurchaseDates([MT].[PKFKID], [MT].[PKFK_Item]) AS [GFLPD]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jody.a.morgan SSC Rookie Points: 27 More actions · Answer 5

Thanks all. I believe that the complexity is the issue unfortunately the solution s more complex than just a straight forward temp tables but I will work something out. Again thank you to all who replied.