SQL lag statement taking too long to run on Azure SQLDB
user4940 Published in September 19, 2018, 11:30 am
I have a statement as follows
--,lag(V.Value) over (order by V.DateTime) as prevVal
FROM [dbo].[History_StorageItem] as H
JOIN [dbo].[History_Integer] as V on H.StorageItemId = V.StorageItemId
where H.FullyQualifiedName like '%.Flt_No'
and V.QualityDetail=192 and V.DateTime>getdate() -8
If I comment the lag part I get about 50 records and it runs in seconds. But when I include the lag part, the query takes about 5 mins to execute. The pricing Tier on Azure is premium p4 with 500 DTUS. But I didn't think a lag of 50 records would take this long.