In my scenario i need to optimize a stored procedure, that i used to import data into DB from an exchange DB. I am relly puzzled because, the solution with INNER JOIN is lower than the LEFT OUTER JOIN solution, basically seems that the way i check the "relation" exist cause a huge slowdown.
To be more clear, i have a Final_DB with few tabels, one for the artciles (tbl_ana_Articles) one for articles attributes aka characteristiscs (tbl_ana_Characteristics), and few other tables. In another Exchange_DB i get relation between articles and attribute/characteristics (it's used to update periodically relations in the Final_DB).
The table with relations, provided by exchange DB, need to be unpivoted first, before it will be usefull (it's not a cross join table when i get it, it become a cross join after the unpivote).
So basically i write the query in that way :
WITH ExchangeArticleCode_CharacteristicCode AS ( SELECT [ACODAR], SUBSTRING([TNCAR],5,2) AS [TNCAR] , [TVALOR] FROM [EXCHANGE_DB].[dbo].[ANART00F] UNPIVOT ( [TVALOR] FOR [TNCAR] in ([ACAR01], ACAR02, ACAR03, ACAR04 , ACAR05 , ACAR06 , ACAR07 , ACAR08 , ACAR09 , ACAR10 , ACAR11 , ACAR12 , ACAR13 , ACAR14 , ACAR15 , ACAR16 , ACAR17 , ACAR18 , ACAR19 , ACAR20) ) AS [UNPIVOT] WHERE [TVALOR] IS NOT NULL AND [TVALOR] != '' ) SELECT Characteristic.[ID] AS [ID_CHARACTERISTIC] ,Article.[ID] AS [ID_ARTICLE] ,Characteristic.[ID_FILTER] AS [ID_FILTER] FROM ExchangeArticleCode_CharacteristicCode LEFT OUTER JOIN [dbo].[tbl_ana_Articles] AS Article ON (ExchangeArticleCode_CharacteristicCode.ACODAR collate Latin1_General_CI_AS) = Article.CODE LEFT OUTER JOIN [dbo].[tbl_ana_Characteristics] AS Characteristic ON (ExchangeArticleCode_CharacteristicCode.TNCAR collate Latin1_General_CI_AS) + '_' + (ExchangeArticleCode_CharacteristicCode.TVALOR collate Latin1_General_CI_AS) = Characteristic.ID_ERP WHERE Characteristic.[IS_ACTIVE] = 1
This solution is surprising fast, but have issues, sometimes there is junk into exchange DB, so the left join fail to match the codes and in the result table i get some NULL. If i try to prevent the NULL, replacing LEFT OUTER JOIN with INNER JOIN or adding a check (IS NOT NULL) into the where condition, the query become really slow and heavy to execute. It's not clear to me why and how to avoid that.
Here the execution plan of the fast query : https://www.brentozar.com/pastetheplan/?id=ryMBHCryp
Here the execution plan of the slow query : https://www.brentozar.com/pastetheplan/?id=SywALRS16
To be fair, it seems to me that the slow query have an expensive nested loop, but why the INNER JOIN is transated in such nested loop?