February 12, 2019 at 5:23 pm
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
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy