Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Cathalc
Contributor III
Contributor III

Extremely slow SQL code works fine in SSMS

 

Spoiler
Hi all,


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))

Thank you and best regards,

cathal


Labels (3)
1 Solution

Accepted Solutions
martinpohl
Partner
Partner

I think there is no solution because in your srcipt you join and aggr in your select-statement.

Try to load the basic tables to qvd (maybe incremental?) and reuse them in script. this could be faster.

View solution in original post

1 Reply
martinpohl
Partner
Partner

I think there is no solution because in your srcipt you join and aggr in your select-statement.

Try to load the basic tables to qvd (maybe incremental?) and reuse them in script. this could be faster.

View solution in original post