CREATE TABLE [tmp].[T_abc_branch_](
	[DTST] [int] NOT NULL, [DTED] [int] NOT NULL, [Product_Key] [int] NOT NULL, [Branch_Key] [int] NOT NULL,
	[ABC_Количество] [nvarchar](5) NOT NULL,	[Rating_Количество] [bigint] NOT NULL,
	[ABC_Доход] [nvarchar](5) NOT NULL,		[Rating_Доход] [bigint] NOT NULL,
	[ABC_Оборот] [nvarchar](5) NOT NULL,		[Rating_Оборот] [bigint] NOT NULL,
	[ABC_Клиенты] [nvarchar](5) NOT NULL,		[Rating_Клиенты] [bigint] NOT NULL,
	[ABC_Частота] [nvarchar](5) NOT NULL,		[Rating_Частота] [bigint] NOT NULL,
	[ABC_ОстаткиШт] [nvarchar](5) NOT NULL,		[Rating_ОстаткиШт] [bigint] NOT NULL,
	[ABC_ОстаткиRUB] [nvarchar](5) NOT NULL,	[Rating_ОстаткиRUB] [bigint] NOT NULL,
	[ABC_ВЕС] [nvarchar](5) NOT NULL,		[Rating_ВЕС] [bigint] NOT NULL,
	[ABC_ОБЪЕМ] [nvarchar](5) NOT NULL,		[Rating_ОБЪЕМ] [bigint] NOT NULL
) 
CREATE UNIQUE CLUSTERED INDEX [IC_U_T_abc_branch_:Год:Product_Key:Branch_Key:] ON [tmp].[T_abc_branch_]
(
	[DTST] ASC,
	[DTED] ASC,
	[Product_Key] ASC,
	[Branch_Key] ASC
)
CREATE TABLE [dim].[ABC_Branch_SKU](
	[ABC_Key] [int] IDENTITY(1,1) NOT NULL,
	[ABC_Количество] [nvarchar](50) NOT NULL,	[ABC_Количество_ORD] [smallint] NOT NULL,
	[ABC_Доход] [nvarchar](50) NOT NULL,		[ABC_Доход_ORD] [smallint] NOT NULL,
	[ABC_Оборот] [nvarchar](50) NOT NULL,		[ABC_Оборот_ORD] [smallint] NOT NULL,
	[ABC_Клиенты] [nvarchar](50) NOT NULL,		[ABC_Клиенты_ORD] [smallint] NOT NULL,
	[ABC_Частота] [nvarchar](50) NOT NULL,		[ABC_Частота_ORD] [smallint] NOT NULL,
	[ABC_ОстаткиШт] [nvarchar](50) NOT NULL,	[ABC_ОстаткиШт_ORD] [smallint] NOT NULL,
	[ABC_ОстаткиRUB] [nvarchar](50) NOT NULL,	[ABC_ОстаткиRUB_ORD] [smallint] NOT NULL,
	[ABC_ВЕС] [nvarchar](50) NOT NULL,		[ABC_ВЕС_ORD] [smallint] NOT NULL,
	[ABC_ОБЪЕМ] [nvarchar](50) NOT NULL,		[ABC_ОБЪЕМ_ORD] [smallint] NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX [IC_U_ABC_Branch_SKU:ABC_Key:] ON [dim].[ABC_Branch_SKU]
(
	[ABC_Key] ASC
)

UPDATE STATISTICS [DWH_Staging_Area].[tmp].[T_abc_branch_] [IC_U_T_abc_branch_:Год:Product_Key:Branch_Key:] WITH FULLSCAN
UPDATE STATISTICS [DWH].[dim].[ABC_Branch_SKU] [IC_U_ABC_Branch_SKU:ABC_Key:] WITH FULLSCAN

		SELECT	 ABC_.ABC_Key, ABC.*
		FROM	[DWH_Staging_Area].[tmp].[T_abc_branch_] AS ABC
		JOIN	[DWH].[dim].[ABC_Branch_SKU] AS ABC_
			ON	ABC.[ABC_Количество] = ABC_.[ABC_Количество] AND
				ABC.[ABC_Доход] = ABC_.[ABC_Доход]  AND
				ABC.[ABC_Оборот] = ABC_.[ABC_Оборот]  AND
				ABC.[ABC_Клиенты] = ABC_.[ABC_Клиенты] AND
				ABC.[ABC_Частота] = ABC_.[ABC_Частота] AND
				ABC.[ABC_ОстаткиШт] = ABC_.[ABC_ОстаткиШт] AND
				ABC.[ABC_ОстаткиRUB] = ABC_.[ABC_ОстаткиRUB] AND
				ABC.[ABC_ВЕС] = ABC_.[ABC_ВЕС] AND
				ABC.[ABC_ОБЪЕМ] = ABC_.[ABC_ОБЪЕМ] 
		OPTION (RECOMPILE)	

	
SELECT	ABC_.ABC_Key, 
	ABC.DTST,	
	ABC.DTED,	
	Product_Key,	
	Branch_Key
into #q2
FROM	[DWH_Staging_Area].[tmp].[T_abc_branch_] AS ABC
JOIN	[DWH].[dim].[ABC_Branch_SKU] AS ABC_
  ON	ABC.[ABC_Количество] = ABC_.[ABC_Количество] AND
	ABC.[ABC_Доход] = ABC_.[ABC_Доход]  AND
	ABC.[ABC_Оборот] = ABC_.[ABC_Оборот]  AND
	ABC.[ABC_Клиенты] = ABC_.[ABC_Клиенты] AND
	ABC.[ABC_Частота] = ABC_.[ABC_Частота] AND
	ABC.[ABC_ОстаткиШт] = ABC_.[ABC_ОстаткиШт] AND
	ABC.[ABC_ОстаткиRUB] = ABC_.[ABC_ОстаткиRUB] AND
	ABC.[ABC_ВЕС] = ABC_.[ABC_ВЕС] AND
	ABC.[ABC_ОБЪЕМ] = ABC_.[ABC_ОБЪЕМ] 
OPTION (RECOMPILE , HASH JOIN)	

(242575 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 1435 ms,  elapsed time = 405 ms.
SQL Server parse and compile time: 
   CPU time = 5 ms, elapsed time = 5 ms.

SELECT	ABC_.ABC_Key, 
	ABC.DTST,	
	ABC.DTED,	
	Product_Key,	
	Branch_Key
into #q3
FROM	[DWH_Staging_Area].[tmp].[T_abc_branch_] AS ABC
JOIN	[DWH].[dim].[ABC_Branch_SKU] AS ABC_
  ON	ABC.[ABC_Количество] = ABC_.[ABC_Количество] AND
	ABC.[ABC_Доход] = ABC_.[ABC_Доход]  AND
	ABC.[ABC_Оборот] = ABC_.[ABC_Оборот]  AND
	ABC.[ABC_Клиенты] = ABC_.[ABC_Клиенты] AND
	ABC.[ABC_Частота] = ABC_.[ABC_Частота] AND
	ABC.[ABC_ОстаткиШт] = ABC_.[ABC_ОстаткиШт] AND
	ABC.[ABC_ОстаткиRUB] = ABC_.[ABC_ОстаткиRUB] AND
	ABC.[ABC_ВЕС] = ABC_.[ABC_ВЕС] AND
	ABC.[ABC_ОБЪЕМ] = ABC_.[ABC_ОБЪЕМ] 
OPTION (RECOMPILE)

(242575 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 4946 ms,  elapsed time = 1723 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.