Home SQL lag statement taking too long to run on Azure SQLDB
Reply: 0

SQL lag statement taking too long to run on Azure SQLDB

user765
1#
user765 Published in July 17, 2018, 7:21 pm

I have a statement as follows

SELECT H.[StorageItemId]
  ,H.[FullyQualifiedName]
      ,V.DateTime
     ,V.Value        
         --,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.

Any suggestions will be greatly appreciated.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.326947 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO