Load large Fact table performance

• Lidou123

  Hall of Fame

  Points: 3186

  More actions

  February 12, 2019 at 5:23 pm

  #409509

  Hi All

  I need your help.
  I am working in a project to create a datawarhouse in azure sql server
  I'am trying to load about 31 millions rows in a fact table
  This the stored procedure I 've created to do this.

  
  CREATE PROCEDURE [Dwh].[AlimFctSurvey]

  AS
  BEGIN

      SET NOCOUNT ON;

  DECLARE @StartDate Date = (SELECT (ISNULL (MAX(LoadingDate), ( SELECT MIN (DATE) from [Dwh].[DimDate] )) ) FROM [Dwh].[FactSurvey] )

  INSERT INTO [Dwh].[FactSurvey] with (TABLOCK)
  ([IdDate]
  ,[IdRespondant]
  ,[IdResponse]
  ,[IdCountry]
  , IdModule
  ,[Count]
  ,[LoadingDate]
  , LineId
  , FileId
  )

  SELECT ISNULL (B.IdDate, -1)IdDate
  , ISNULL (C.IdRespondant, -1)IdRespondant
  , ISNULL (E.IdResponse, -1)IdResponse
  , ISNULL (D.IdCountry, -1)IdCountry
  , ISNULL (F.IdModule, -1)IdModule
  , [Count] = 1
  , Loadingdate = Getdate()
  , LineId
  , FileId

  FROM [Ods].[Sas_Output] A

  LEFT JOIN [Dwh].[DimDate] B ON B.IdDate = 100 * cast (A.Mois as int) + 1

  LEFT JOIN [Dwh].[DimRespondant] C ON A.[Respondant_FunctionnalKey]= C.Respondant_FunctionnalKey

  LEFT JOIN [Dwh].[DimCountry] D ON A.Country_FunctionnalKey= D.Country_FunctionnalKey      

  LEFT JOIN Dwh.DimResponse E on E.Response_FunctionnalKey = a.Response

  LEFT JOIN Dwh.DimModule F on F.Module_FunctionnalKey = a.Module

  WHERE
  1=1
  and A.LoadingDate > @StartDate

  AND FILETYPE = 'MAIN'
  AND isnull ([FLAG_RA1A], 0) = 0

  AND MOIS <= '201803'

  END

  
  All tables in the queries have a columnstore index
  I don't know why the query runs for hours without loading the table.
  Do you have an idea ?
  Thank you for your help