I am checking the missing index in my query and if so I skip the row in SQL. But because there are too many checks the query take long time and getting time out error in Power Query. My SQL query
SELECT em.EqmmspvDate,
em.EqmmspvEqmId,
em.EqmmspvValue,
em.Rownumber,
em.t
FROM (
SELECT e.[eqmmspveqmid],
e.[eqmmspvdate],
e.[eqmmspvvalue],
e.t,
e.rownumber,
Lag(e.rownumber, 1, 0)
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC)
AS
'previuos_rownumber'
FROM (SELECT Row_number()
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC) AS
Rownumber,
[eqmmspveqmid],
[eqmmspvdate],
Lag(eqmmspvvalue, 1, 0)
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC) AS
'previuos_kilometerage',
Lead(eqmmspvvalue, 1, 0)
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC) AS
'next_kilometerage',
Max(Max(eqmmspvvalue))
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC) AS
'Last_value',
eqmmspvvalue,
[eqmmspvdifference] AS t
FROM [ULTIMO].[dba].[equipmentmspvalue] AS e
WHERE e.eqmmspvrecstatus = 2
AND e.[eqmmspvmspid] = 'DEFAULT'
GROUP BY [eqmmspveqmid],
eqmmspvdate,
[eqmmspvvalue],
[eqmmspvdifference]
) AS e
INNER JOIN #temp AS te
ON te.rownumber = e.rownumber
WHERE e.EqmmspvValue >= e.previuos_kilometerage
GROUP BY e.[eqmmspveqmid],
e.[eqmmspvdate],
e.eqmmspvvalue,
e.t,
e.rownumber) as em
WHERE em.previuos_rownumber + 1 = em.Rownumber
AND previuos_rownumber <> 0
The bottelneck is here :
Lag(e.rownumber, 1, 0)
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC)
AS
'previuos_rownumber'
Can we do the same in Dax?
regards,
Ezz
