I have the following piece of code that works fine in SSMS but takes more than 10 minutes to load in Qlik Sense Load Editor.
LIB CONNECT TO 'RALNHV (nhv_qlikadmin)'; /****** Find Running Component Removals ******/ RunningRemovals: Load*; SQL WITH CTE AS ( SELECT CONVERT(DATE, CONCAT(REPLACE(MIN(PeriodYearMonth COLLATE database_default), ' ', '-'), '-01'), 23) AS date, CONVERT(DATE, CONCAT(REPLACE(MAX(PeriodYearMonth COLLATE database_default), ' ', '-'), '-01'), 23) AS max_date
FROM [RALNHV].[dbo].[vtRelRepComponentsRemovalsByPartNo] UNION ALL SELECT DATEADD(MONTH, 1, date), max_date FROM CTE WHERE date < max_date ) SELECT FORMAT(CTE.date, 'yyyy MM') AS PeriodYearMonth , rp.Reg , rp.PartNo , COALESCE(v.ComponentRemovals, 0) AS ComponentRemovals , SUM(COALESCE(v.ComponentRemovals, 0)) OVER (PARTITION BY rp.Reg, rp.PartNo ORDER BY FORMAT(CTE.date, 'yyyy MM') ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RunningRemovals , COALESCE(v.ConfirmedFailures, 0) AS ConfirmedFailures , SUM(COALESCE(v.ConfirmedFailures, 0)) OVER (PARTITION BY rp.Reg, rp.PartNo ORDER BY FORMAT(CTE.date, 'yyyy MM') ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RunningFailures ,sum(COALESCE(rrrj.AfHrs, 0)) AS AfHrs ,sum(SUM(COALESCE(rrrj.AfHrs, 0))) OVER (PARTITION BY rp.Reg, rp.PartNo ORDER BY FORMAT(CTE.date, 'yyyy MM') ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RunningHrs FROM CTE
CROSS JOIN (SELECT DISTINCT Reg, PartNo FROM [RALNHV].[dbo].[vtRelRepComponentsRemovalsByPartNo]) rp LEFT JOIN [RALNHV].[dbo].[vtRelRepComponentsRemovalsByPartNo] v ON v.PeriodYearMonth = FORMAT(CTE.date, 'yyyy MM') AND v.Reg = rp.Reg AND v.PartNo = rp.PartNo LEFT JOIN [RALNHV].[dbo].[tRelRepRegJourney] rrrj ON rrrj.PeriodYearMonth = FORMAT(CTE.date, 'yyyy MM') AND rp.Reg = rrrj.Reg Group by CTE.date, rp.Reg, rp.PartNo, ComponentRemovals, ConfirmedFailures ORDER BY rp.Reg, rp.PartNo, CTE.date ;
I found that the issue is with the part in bold and more specific with the second time that I perform that action. Does anyone see an issues? Does anyone have an alternative? I was also wondering if -when there's no alternative- it's safe to keep loading this data (considering the 10 min loading time))
