title: Query optimization - indexing
body: <p>I use the following script for inserting data into a SQL Server table. </p>
  
  <p>All the columns from <code>TempCdbSap</code> are <code>nvarchar</code>, this means all the join are made with nvarchar columns. <code>Amount</code> columns also needs a convertion to <code>decimal</code>.</p>
  
  <p>I also have a nonclustered index on <code>TempCdbSap</code> for column <code>Consolidation</code>
  which includes:  </p>
  
  <pre><code>[Year], [Month], [Branch], [CostCenter], [CostCategorySAP], [Amount]
  </code></pre>
  
  <p>The query will return/insert ~ 8 mil rows. </p>
  
  <p>Could anyone suggest some optimization on this?</p>
  
  <pre><code>insert into FactTable (...)
     select distinct 
         da.ID_Date as [ID_Date] ,
         sc.Pk_Scenario as [ID_Scenario],
         co.Pk_Consolidation as [ID_Consolidation],
         br.PK_ShareBranch as [Id_Branch],
         ka.PK_Cost as [ID_CostCategory],
         cc.Pk_CostCenter as [ID_CostCenter],
         isnull(case ka.AccountType
                  when 'c' then -1*cast(replace (amount,',','.') as decimal(18,2))
                  when 'r' then 1*cast(replace (amount,',','.') as decimal(18,2)) 
                  when 's' then  cast(replace (amount,',','.') as decimal(18,2))
                end, 0)  as Amount,
         isnull(case ka.AccountType
                  when 'c' then -1*cast(replace (amount,',','.') as decimal(18,2))
                  when 'r' then 1*cast(replace (amount,',','.') as decimal(18,2)) 
                  when 's' then  cast(replace (amount,',','.') as decimal(18,2))
                end, 0) as SharedAmount,
         cy.Pk_Currency as ID_Currency
     from 
         [dbo].[TempCdbSap] cdb
     inner join 
         DimBranchShare br on br.[Branch No] = cdb.branch
     inner join 
         DimDate da on da.MonthName = cdb.[Month] and da.Year = cdb.[year]
     inner join 
         DimCostCenter cc on cc.CostCenterName = cdb.CostCenter
     inner join  
         DimScenario sc on sc.ScenarioTypeDEU = cdb.Scenario
     inner join 
         DimConsolidation co on co.ConsolidationType = cdb.Consolidation
     inner join 
         DimCostCategory ka on ka.AccountNo = cdb.CostCategorySAP
     inner join 
         DimCurrency cy on cy.CurrencyCode = cdb.Currency
     where
         da.ID_Date &gt;= year(br.RowStartDate) * 100 + month(br.RowStartDate) 
         and da.ID_Date &lt; year(br.RowEndDate) * 100 + month(br.RowEndDate)  
  </code></pre>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>The execution plan is:</p>
  
  <p><a href="https://i.stack.imgur.com/o3GSz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/o3GSz.png" alt="enter image description here"></a></p>