i have sql server table 2 datetime fields (cnxstartdatetime, cnxenddatetime). each row represents transmission of information. i trying find maximum number of concurrent transmissions based on these 2 timestamps. have working query both slow , extremely cumbersome. know there must better way go can't come any.
for current version, if run 5 "levels" , results have go , add ton of sql test if there instances of 6 concurrent transmissions, etc. once query gets 7-8 "levels" deep becomes slow.
snippet of current version:
select t1.id, t2.id, t3.id, t4.id, t5.id, t6.id, t7.id, t8.id, t9.id, t10.id dbo.mytable t1, dbo.mytable t2, dbo.mytable t3, dbo.mytable t4, dbo.mytable t5, dbo.mytable t6, dbo.mytable t7, dbo.mytable t8, dbo.mytable t9, dbo.mytable t10 (((t2.cnxstartdatetime >= t1.cnxstartdatetime) , (t2.cnxstartdatetime <= t1.cnxenddatetime)) or ((t2.cnxenddatetime >= t1.cnxstartdatetime) , (t2.cnxenddatetime <= t1.cnxenddatetime))) , t2.id != t1.id , (((t3.cnxstartdatetime >= t2.cnxstartdatetime) , (t3.cnxstartdatetime >= t1.cnxstartdatetime)and (t3.cnxstartdatetime <= t1.cnxenddatetime) , (t3.cnxstartdatetime <= t2.cnxenddatetime)) or ((t3.cnxenddatetime >= t2.cnxstartdatetime) , (t3.cnxenddatetime >= t1.cnxstartdatetime)and (t3.cnxenddatetime <= t1.cnxenddatetime) , (t3.cnxenddatetime <= t2.cnxenddatetime))) , t3.id != t2.id , t3.id != t1.id , (((t4.cnxstartdatetime >= t3.cnxstartdatetime) , (t4.cnxstartdatetime >= t1.cnxstartdatetime)and (t4.cnxstartdatetime >= t2.cnxstartdatetime) , (t4.cnxstartdatetime <= t1.cnxenddatetime) , (t4.cnxstartdatetime <= t3.cnxenddatetime)and (t4.cnxstartdatetime <= t2.cnxenddatetime)) or ((t4.cnxenddatetime >= t3.cnxstartdatetime) , (t4.cnxenddatetime >= t1.cnxstartdatetime)and (t4.cnxenddatetime >= t2.cnxstartdatetime) , (t4.cnxenddatetime <= t1.cnxenddatetime)and (t4.cnxenddatetime <= t3.cnxenddatetime)and (t4.cnxenddatetime <= t2.cnxenddatetime))) , t4.id != t3.id , t4.id != t2.id , t4.id != t1.id ... *snip* edit many of responses suggesting use cross join. not achieve results looking for. here's example of results of cross join 1 record's "overlaps." list gives me id 11787 can see, 11781 not overlap 11774 list of record time span intersects 11787
11774 2011-04-29 01:02:56.780 2011-04-29 01:02:58.793 11777 2011-04-29 01:02:56.780 2011-04-29 01:02:58.843 11778 2011-04-29 01:02:56.780 2011-04-29 01:02:58.950 11775 2011-04-29 01:02:56.793 2011-04-29 01:02:58.843 11776 2011-04-29 01:02:56.793 2011-04-29 01:02:58.890 11780 2011-04-29 01:02:58.310 2011-04-29 01:03:02.687 11779 2011-04-29 01:02:58.327 2011-04-29 01:03:02.543 11787 2011-04-29 01:02:58.530 2011-04-29 01:03:08.827 ** 11781 2011-04-29 01:02:59.030 2011-04-29 01:03:05.187 11782 2011-04-29 01:02:59.247 2011-04-29 01:03:05.467 11784 2011-04-29 01:02:59.293 2011-04-29 01:03:05.810 11791 2011-04-29 01:03:00.107 2011-04-29 01:03:13.623 11786 2011-04-29 01:03:00.843 2011-04-29 01:03:08.983 11783 2011-04-29 01:03:02.560 2011-04-29 01:03:05.793 11785 2011-04-29 01:03:02.717 2011-04-29 01:03:07.357 11790 2011-04-29 01:03:05.200 2011-04-29 01:03:14.153 11804 2011-04-29 01:03:05.687 2011-04-29 01:03:25.577 11811 2011-04-29 01:03:07.093 2011-04-29 01:03:35.153 11799 2011-04-29 01:03:07.123 2011-04-29 01:03:24.437 11789 2011-04-29 01:03:08.793 2011-04-29 01:03:13.577 i've attempted writing cte recursion can't figure out how insure current id doesn't match previous id in current stack of concurrency. below recurses upon until hits limit.
with transmissionconcurrency (starttime, endtime, concurrencylevel) ( select cnxstartdatetime starttime, cnxenddatetime endtime, 1 concurrencylevel dbo.mytable union select case when d.cnxstartdatetime > tc.starttime d.cnxstartdatetime else tc.starttime end starttime, case when d.cnxenddatetime < tc.endtime d.cnxenddatetime else tc.endtime end enddate, tc.concurrencylevel + 1 concurrencylevel dbo.mytable d inner join transmissionconcurrency tc on ((d.cnxstartdatetime between tc.starttime , tc.endtime) or (d.cnxenddatetime between tc.starttime , tc.endtime) or (d.cnxstartdatetime <= tc.starttime , d.cnxenddatetime >= tc.endtime)) ) select * transmissionconcurrency order concurrencylevel, starttime, endtime i've come below diagram try better explain i'm looking for.
a [--------] b [-----] c [------] d [---] e [---] f [-] in instance, cross join methods tell me maximum concurrency a 6 (a b, c, d, e , f) i'm looking max concurrency of 3 (a b,f or a c,e)
jeff. i've written similar query once - in oracle - not sure whether work in sql-server, it's worth trying: maybe it'll give idea:
select t.time b, lead(t.time) on (order t.time, t.weight desc) e, sum(t.weight) on (order t.time, t.weight desc) cnt ( select trunc(:astartwith) time, 0 weight dual union select req_recieved time, +1 weight log_tbl trunc(req_recieved, 'mi') between :astartwith - interval '10' minute , :aendwith + interval '10' minute union select response_sent time, -1 weight log_tbl trunc(req_recieved, 'mi') between :astartwith - interval '10' minute , :aendwith + interval '10' minute union select trunc(:aendwith) time, 0 weight dual ) t the general idea go through requests between :astartwith date , :aendwith date assigning +1 weight portion every request starts in given period , -1 every request end in same period.
here assume requests no longer 10 minutes (where trunc(req_recieved, 'mi') between :astartwith - interval '10' minute , :aendwith + interval '10' minute); , select ... dual boundary conditions.
then analytic functions find end time of request (lead(t.time) on (order t.time, t.weight desc) e) , sum weights current request - give number of requests starting @ time b , ending @ time e (sum(t.weight) on (order t.time, t.weight desc) cnt).
to find maximum number of requests can wrap query desired evaluations.
could please try if scenario works you? hope :)
Comments
Post a Comment