Speeding up SQL Server cross apply to get aggregated data -

-

in sql server, trying put single query grabs row , includes aggregated data 2 hour window before row aggregated data 1 hour window after. how can make run faster?

the rows have time stamps millisecond precision, , not evenly spaced. have on 50 million rows in table, , query not seem completing. there indexes in many places, don't seem help. thinking using window function, not sure possible have sliding window unevenly distributed rows. also, future 1 hour window, not sure how done sql window.

box string , has 10 unique values. process string , has 30 unique values. average duration_ms 200 ms. errors account less 0.1% of data. 50 million rows describes years worth of data.

select  c1.start_time, c1.end_time, c1.box, c1.process, datediff(ms,c1.start_time,c1.end_time) duration_ms, datepart(dw,c1.start_time) day_of_week, datepart(hour,c1.start_time) hour_of_day, c3.*, c5.* metrics_table c1 cross apply (select      avg(cast(datediff(ms,c2.start_time,c2.end_time) numeric)) avg_ms,     count(1) num_process_total,     count(distinct process) num_process_unique,     count(distinct box) num_box_unique     metrics_table c2     datediff(minute,c2.start_time,c1.start_time) <= 120     , c1.start_time> c2.start_time     , c2.error_code = 0 ) c3 cross apply (select     avg(case when datediff(ms,c4.start_time,c4.end_time)>1000 1.0 else 0.0 end) percent_over_thresh     metrics_table c4     datediff(hour,c1.start_time,c4.start_time) <= 1     , c4.start_time> c1.start_time     , c4.error_code= 0 ) c5 c1.error_code= 0

edit

version: sql azure 12.0

adding execution plan: enter image description here

the following should step in right direction... note: c2.start_time & c4.start_time no longer wrappen in datediff functions making them sargable...

select     c1.start_time,     c1.end_time,     c1.box,     c1.process,     datediff(ms, c1.start_time, c1.end_time) duration_ms,     datepart(dw, c1.start_time) day_of_week,     datepart(hour, c1.start_time) hour_of_day,     --c3.*,     avg_ms = case when      c5.*     dbo.metrics_table c1     cross apply (                 select                     avg(cast(datediff(ms, c2.start_time, c2.end_time) numeric)) avg_ms,                     count(1) num_process_total,                     count(distinct process) num_process_unique,                     count(distinct box) num_box_unique                                     dbo.metrics_table c2                                     --datediff(minute,c2.start_time,c1.start_time) <= 120                     c2.start_time <= dateadd(minute, -120, c1.start_time)                     --and c1.start_time> c2.start_time                     , c2.error_code = 0                 ) c3     cross apply (                 select                     avg(case when datediff(ms, c4.start_time, c4.end_time) > 1000 1.0 else 0.0 end                     ) percent_over_thresh                                     dbo.metrics_table c4                                     --datediff(hour, c1.start_time, c4.start_time) <= 1                     c4.start_time >= dateadd(hour, 1, c1.start_time)                     --and c4.start_time> c1.start_time                     , c4.error_code = 0                 ) c5     c1.error_code = 0;

of course, making query sargable doesn't unless there's appropriate index available. following should 3 metrics_table references... (see indexes available, there's chance may not need create new index)

create nonclustered index ixf_metricstable_errorcode_starttime on dbo.metrics_table (     error_code,     start_time     ) include (     end_time,     box,     process     )      error_code = 0;

Comments

Post a Comment

Popular posts from this blog

ios - MKAnnotationView layer is not of expected type: MKLayer -

-
Image
so code works fine logger riddled message. there way rid of or suppress it? postannotation.swift class postannotation: mkpointannotation { //mark: properties let post: post //mark: initialization init(post: post) { self.post = post super.init() self.coordinate = cllocationcoordinate2d(latitude: post.latitude, longitude: post.longitude) self.title = post.title self.subtitle = post.timestring() } } adding annotation let annotation = postannotation(post: post) self.map.addannotation(annotation) func mapview func mapview(_ mapview: mkmapview, viewfor annotation: mkannotation) -> mkannotationview? { if annotation mkuserlocation { return nil } var annotationview = mapview.dequeuereusableannotationview(withidentifier: "pin") as? mkpinannotationview if annotationview == nil { annotationview = mkpinannotationview(annotation: annotation, reuseidentifier: "pin")
Read more

ZeroMQ on Windows, with Qt Creator -

-
i have installed zmq link given below on windows 7. http://zeromq.org/distro:microsoft-windows 1) have c++ project in qt-creator , i'm using zmq c++ binding (zhelpers.hpp etc). 2) have added .lib installed zmq folder qtcreator 3) while compiling, compiler looks _imp__zmq_verion, in .dll files present in .lib have __imp_zmq_version. 4) causing undefined reference zmq_version. anyone else faced problem , has solution ?
Read more

unity3d - Unity SceneManager.LoadScene quits application -

-
i have 2 different scenes 1 named 'main' , other 'game' both added scenes in load. when use scenemanager.loadscene("game"); works no problem when tried return main scene code if (input.getkeydown (keycode.escape)) { scenemanager.loadscene("main"); } strangely game quits. thanks. sorry interruption. somewhere on other script found code. if (input.getkeydown (keycode.escape)) { application.quit (); } that's bad, know...
Read more