Request for the maximum number of parallel time intervals

Question

Request for the maximum number of parallel time intervals

I have a SQL Server table with two datetime fields ( CnxStartdatetime , CnxEnddatetime ). Each line represents the transmission of information. I am trying to find the maximum number of concurrent transfers based on these two timestamps . I have a working request, but it is slow and extremely cumbersome. I know that there must be a better way to do this, but cannot come up with.

For the current version, if I run it with 5 levels and get the results, I need to go back and add a ton of SQL to check if there are instances of 6 simultaneous transfers, etc. As soon as the request receives 7-8 “levels” in depth, it becomes very slow.

Fragment of the current version:

 select t1.id, t2.id, t3.id, t4.id, t5.id, t6.id, t7.id, t8.id, t9.id, t10.id FROM 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 WHERE (((t2.cnxstartdatetime >= t1.cnxstartdatetime) and (t2.cnxstartdatetime <= t1.cnxenddatetime)) or ((t2.cnxenddatetime >= t1.cnxstartdatetime) and (t2.cnxenddatetime <= t1.cnxenddatetime))) AND t2.id != t1.id AND (((t3.cnxstartdatetime >= t2.cnxstartdatetime) and (t3.cnxstartdatetime >= t1.cnxstartdatetime)and (t3.cnxstartdatetime <= t1.cnxenddatetime) and (t3.cnxstartdatetime <= t2.cnxenddatetime)) or ((t3.cnxenddatetime >= t2.cnxstartdatetime) and (t3.cnxenddatetime >= t1.cnxstartdatetime)and (t3.cnxenddatetime <= t1.cnxenddatetime) and (t3.cnxenddatetime <= t2.cnxenddatetime))) AND t3.id != t2.id AND t3.id != t1.id AND (((t4.cnxstartdatetime >= t3.cnxstartdatetime) and (t4.cnxstartdatetime >= t1.cnxstartdatetime)and (t4.cnxstartdatetime >= t2.cnxstartdatetime) and (t4.cnxstartdatetime <= t1.cnxenddatetime) and (t4.cnxstartdatetime <= t3.cnxenddatetime)and (t4.cnxstartdatetime <= t2.cnxenddatetime)) or ((t4.cnxenddatetime >= t3.cnxstartdatetime) and (t4.cnxenddatetime >= t1.cnxstartdatetime)and (t4.cnxenddatetime >= t2.cnxstartdatetime) and (t4.cnxenddatetime <= t1.cnxenddatetime)and (t4.cnxenddatetime <= t3.cnxenddatetime)and (t4.cnxenddatetime <= t2.cnxenddatetime))) AND t4.id != t3.id AND t4.id != t2.id AND t4.id != t1.id ... *snip*

Change Many answers show that I am using cross join . This does not lead to the results that I am looking for. Here's an example of cross join results for one record "overlapping". This is the list that he gives me for ID 11787 As you can see, 11781 does not overlap 11774 This is just a list of any record whose time interval 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 also tried writing a CTE with recursion, but I cannot figure out how to ensure that the current ID does not match the previous ID in the current concurrency stack. The following is simply repeated on its own until it reaches the limit.

 WITH TransmissionConcurrency (StartTime, EndTime, ConcurrencyLevel) AS ( SELECT CnxStartDatetime AS StartTime, CnxEndDatetime AS EndTime, 1 AS ConcurrencyLevel FROM dbo.MyTable UNION ALL SELECT CASE WHEN d.CnxStartDatetime > tc.StartTime THEN d.CnxStartDatetime ELSE tc.StartTime END AS StartTime, CASE WHEN d.CnxEndDatetime < tc.EndTime THEN d.CnxEndDatetime ELSE tc.EndTime END AS EndDate, tc.ConcurrencyLevel + 1 as ConcurrencyLevel FROM dbo.MyTable d INNER JOIN TransmissionConcurrency tc ON ((d.CnxStartDatetime between tc.StartTime and tc.EndTime) or (d.CnxEndDatetime between tc.StartTime and tc.EndTime) or (d.CnxStartDatetime <= tc.StartTime and d.CnxEndDatetime >= tc.EndTime)) ) SELECT * FROM TransmissionConcurrency ORDER BY ConcurrencyLevel, StartTime, EndTime

I came up with the diagram below to better explain what I'm looking for.

 A [--------] B [-----] C [------] D [---] E [---] F [-]

In this case, the cross join methods will tell me that the maximum concurrency with A is 6 ( A with B, C, D, E and F ). What I'm looking for will be max concurrency of 3 ( A with B,F or A with C,E )

+6

sql datetime sql-server timespan

Jeff swensen Apr 29 '11 at 5:31

source share

5 answers

andr · Answer 1 · 2011-04-29T06:42:42+0000

Jeff. I wrote the same query once, but in Oracle - I'm not sure if this will work in SQL-Server, but it's worth a try: maybe this will give you some idea:

 select t.time as b, lead(t.time) over (order by t.time, t.weight desc) as e, sum(t.weight) over (order by t.time, t.weight desc) as cnt from ( select trunc(:aStartWith) as time, 0 as weight from dual union all select req_recieved as time, +1 as weight from log_tbl where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute union all select response_sent as time, -1 as weight from log_tbl where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute union all select trunc(:aEndWith) as time, 0 as weight from dual ) t

The general idea is that I look at all requests between :aStartWith date and :aEndWith date, assigning +1 weight part to each request starting in a given period, and -1 to every request that ends in the same period.

Here I assume that the requests are no more than 10 minutes ( where trunc(req_recieved, 'mi') between :aStartWith - interval '10' minute and :aEndWith + interval '10' minute ); and select ... from dual are boundary conditions.

Then, with analytic functions, I find the end time of the request ( lead(t.time) over (order by t.time, t.weight desc) as e ) and summarize the scales for the current request - this will give the number of requests starting at time b and ending during e ( sum(t.weight) over (order by t.time, t.weight desc) as cnt ).

To find the maximum number of queries, you can simply wrap this query with the desired ratings.

Could you try if this scenario works for you? Hope he does it :)

Mikael eriksson · Answer 2 · 2011-04-29T17:13:30+0000

 declare @T table (ID int, Starts datetime, Ends datetime) insert into @T (ID, Starts, Ends) values (1, '2000-12-30', '2000-12-31'), (2, '2001-01-01', '2001-01-10'), (3, '2001-01-02', '2001-01-05'), (4, '2001-01-03', '2001-01-04'), (5, '2001-01-05', '2001-01-10') select T1.ID, count(*) as Levels from @T as T1 cross join @T as T2 where T1.Starts < T2.Ends and T1.Starts > T2.Starts group by T1.ID select top 1 T1.ID, count(*) as Levels from @T as T1 cross join @T as T2 where T1.Starts < T2.Ends and T1.Starts > T2.Starts group by T1.ID order by count(*) desc

Result

 ID Levels ----------- ----------- 3 1 4 2 5 1 (3 row(s) affected) ID Levels ----------- ----------- 4 2

If you want the involved strings to be able to use this:

 select T2.* from (select top 1 T1.ID from @T as T1 cross join @T as T2 where T1.Starts < T2.Ends and T1.Starts > T2.Starts group by T1.ID order by count(*) desc) as C inner join @T as T1 on C.ID = T1.ID inner join @T as T2 on T1.Starts < T2.Ends and T1.Starts > T2.Starts or T2.ID = C.ID

Result:

 ID Starts Ends ----------- ----------------------- ----------------------- 2 2001-01-01 00:00:00.000 2001-01-10 00:00:00.000 3 2001-01-02 00:00:00.000 2001-01-05 00:00:00.000 4 2001-01-03 00:00:00.000 2001-01-04 00:00:00.000

Dalex · Answer 3 · 2011-04-29T07:04:16+0000

This is more of a reporting solution than a “standard” database query. The best option for this is to record the number of transactions at the beginning of each transaction). All other solutions will be slow. But if you really need this ...

The simplest solution is to divide the time period into small details (for example, days) and analyze the number of samples for each period. Here is an example:

 DECLARE @table TABLE ( starts DATETIME , ends DATETIME , trn INT ) INSERT INTO @table ( starts , ends , trn ) SELECT '2003-01-01' , '2003-01-03' , 1 UNION SELECT '2003-01-02' , '2003-01-04' , 2 UNION SELECT '2003-01-02' , '2005-06-06' , 3 ; WITH numbers AS ( SELECT Row_NUmber() OVER ( ORDER BY o.object_id, o2.object_id ) Number FROM sys.objects o CROSS JOIN sys.objects o2 ), Maxx AS ( SELECT MIN(starts) MaxStart , MAX(ends) MaxEnd FROM @table ), DDays AS ( SELECT MIN(starts) DDay FROM @table UNION ALL SELECT DDay + 1 FROM DDays WHERE dday + 1 <= ( SELECT MaxEnd FROM Maxx ) ) SELECT DDay , COUNT(*) Transactions FROM @Table T JOIN DDays D ON D.DDay >= T.starts AND D.DDay <= T.ends GROUP BY DDay HAVING COUNT(*)>1 ORDER BY COUNT(*) DESC OPTION ( MAXRECURSION 0 )

You can change the last statement to obtain the necessary information (transactions during the maximum load, etc.)

Andriy m · Answer 4 · 2011-04-30T14:31:25+0000

 /* prepare sample data (if needed) */ CREATE TABLE MyTable (ID int, CnxStartdatetime datetime, CnxEnddatetime datetime); INSERT INTO MyTable (ID, CnxStartdatetime, CnxEnddatetime) SELECT 11774, '2011-04-29 01:02:56.780', '2011-04-29 01:02:58.793' UNION ALL SELECT 11777, '2011-04-29 01:02:56.780', '2011-04-29 01:02:58.843' UNION ALL SELECT 11778, '2011-04-29 01:02:56.780', '2011-04-29 01:02:58.950' UNION ALL SELECT 11775, '2011-04-29 01:02:56.793', '2011-04-29 01:02:58.843' UNION ALL SELECT 11776, '2011-04-29 01:02:56.793', '2011-04-29 01:02:58.890' UNION ALL SELECT 11780, '2011-04-29 01:02:58.310', '2011-04-29 01:03:02.687' UNION ALL SELECT 11779, '2011-04-29 01:02:58.327', '2011-04-29 01:03:02.543' UNION ALL SELECT 11787, '2011-04-29 01:02:58.530', '2011-04-29 01:03:08.827' UNION ALL SELECT 11781, '2011-04-29 01:02:59.030', '2011-04-29 01:03:05.187' UNION ALL SELECT 11782, '2011-04-29 01:02:59.247', '2011-04-29 01:03:05.467' UNION ALL SELECT 11784, '2011-04-29 01:02:59.293', '2011-04-29 01:03:05.810' UNION ALL SELECT 11791, '2011-04-29 01:03:00.107', '2011-04-29 01:03:13.623' UNION ALL SELECT 11786, '2011-04-29 01:03:00.843', '2011-04-29 01:03:08.983' UNION ALL SELECT 11783, '2011-04-29 01:03:02.560', '2011-04-29 01:03:05.793' UNION ALL SELECT 11785, '2011-04-29 01:03:02.717', '2011-04-29 01:03:07.357' UNION ALL SELECT 11790, '2011-04-29 01:03:05.200', '2011-04-29 01:03:14.153' UNION ALL SELECT 11804, '2011-04-29 01:03:05.687', '2011-04-29 01:03:25.577' UNION ALL SELECT 11811, '2011-04-29 01:03:07.093', '2011-04-29 01:03:35.153' UNION ALL SELECT 11799, '2011-04-29 01:03:07.123', '2011-04-29 01:03:24.437' UNION ALL SELECT 11789, '2011-04-29 01:03:08.793', '2011-04-29 01:03:13.577';

 /* start the job: */ WITH columnified AS ( /* transform every row of (ID, CnxStartdatetime, CnxEnddatetime) into two rows as follows: (ID, CnxStartdatetime, CountChange = 1) (ID, CnxEnddatetime, CountChange = -1) */ SELECT t.ID, Time = CASE x.CountChange WHEN 1 THEN CnxStartdatetime ELSE CnxEnddatetime END, x.CountChange FROM dbo.MyTable t CROSS JOIN (SELECT 1 AS CountChange UNION ALL SELECT -1) x ), groupedandranked AS ( /* group and rank the timestamps */ SELECT Time, CountChange = SUM(CountChange), TimeRN = ROW_NUMBER() OVER (ORDER BY Time) FROM columnified GROUP BY time ), counted AS ( /* get the running counts by summing CountChange */ SELECT Time, TimeRN, RunningCount = CountChange FROM groupedandranked WHERE TimeRN = 1 UNION ALL SELECT t.Time, t.TimeRN, RunningCount = t.CountChange + c.RunningCount FROM groupedandranked t INNER JOIN counted c ON t.TimeRN = c.TimeRN + 1 ), countsranked AS ( /* rank the running counts */ SELECT *, CountRN = DENSE_RANK() OVER (ORDER BY RunningCount DESC) FROM counted ) /* get the top ranked rows and their corresponding subsequent rows (for the ending timestamps) */ SELECT MaxCount = s.RunningCount, MaxCountStart = s.Time, MaxCountEnd = e.Time FROM countsranked s LEFT JOIN countsranked e ON e.TimeRN = s.TimeRN + 1 WHERE s.CountRN = 1;

 /* remove the sample data (unless it your table) */ DROP TABLE MyTable

Hafthor · Answer 5 · 2013-10-11T23:33:05+0000

I know that cursors frowned, but it is also cross-connected. This returns 8 for the provided sample data.

 -- assuming times table with columns s and e declare @s datetime, @e datetime; declare @t table(d datetime); declare c cursor for select s,e from times order by s; open c while(1=1) begin fetch next from c into @s,@e if @@FETCH_STATUS<>0 break; update top(1) @t set d=@e where d< =@s ; if @@ROWCOUNT=0 insert @t(d) values(@e); end close c deallocate c select COUNT(*) as MaxConcurrentTimeSpans from @t

Request for the maximum number of parallel time intervals

More articles: