Calculating the time difference between consecutive records in a RecordSet
-
-
Just out of interest, here's another test which suggests the opposite:
- IF
Object_ID('[test_dt]') IS NOT NULL
- DROP
TABLE [test_dt]
- CREATE
TABLE [test_dt](
- Task int identity,
- date datetime,
- detail varchar(2000)
- )
- insert
into [test_dt] (detail)
- SELECT
replicate(name,2000/(len(name)+1))
- from
master.dbo.syscolumns
- update
[test_dt] set date = 36524.0 + (task+97)/119.0 + ( (((task%7.0)+1)*4)+ task%3.0 +1.0 )/3745.0
- from
test_dt
- CREATE
CLUSTERED INDEX T_Date ON [test_dt](date)
- CREATE
INDEX T_Date_NC ON [test_dt](date)
- CREATE
INDEX T_Task ON [test_dt](Task)
- --correlated subquery version
- SELECT
convert(varchar(11),dateadd(day,0,datediff(day, 0, main.date)),6) [day]
- ,
[task]
- ,
convert(varchar(12),main.[date],14)
- ,convert(varchar(12),
- [date] - ( select top 1 [date] from [test_dt]
- where date >= dateadd(day,0,datediff(day, 0, main.date))
- and [date] < main.[date]
- order by [date] desc)
- ,14) as timegap
- FROM
[test_dt] as main
- order
by date asc
- --Left Join Solution
- select
convert(varchar(11),dateadd(day,0,datediff(day, 0, t1.date)),6) [day],
- T1.task,
- convert
(varchar(12),t1.[date],14),
- convert
(varchar(12),
- T1.date - max(T2.date)
- ,14) TimeGap
- from
test_dt T1
- left
join test_dt T2 on T2.date < T1.date
- and
T2.date >= dateadd(day,0,datediff(day,0,T1.date))
- group
by T1.task, T1.date
- order
by t1.date
- --DROP TABLE [test_dt]
Results:
Correlated subquery version:
Rows Execs Plan Text Subtree Cost ------- ------- ------------------------------------------------------------------------------------------------------------- ------------ 11160 1 SELECT convert(varchar(11),dateadd(day,0,datediff(day, 0, main.date)),6) [day] ,[task] ,convert(varchar(1 2.55217 0 0 |--Compute Scalar(DEFINE
[Expr1010]=CONVERT(varchar(12),[AdventureWorks].[dbo].[test_dt].[date] as [main 2.55217
11160 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES[main].[date], [Expr1011]) WITH ORDERED PREFETCH) 2.551054
11160 1 |--Sort(ORDER BY[main].[date] ASC)) 0.7345789
0 0 | |--Compute Scalar(DEFINE[Expr1003]=CONVERT(varchar(11),dateadd(day,(0),CONVERT_IMPLICIT(da 0.03445178
11160 1 | |--Index Scan(OBJECT[AdventureWorks].[dbo].[test_dt].[T_Task] AS [main])) 0.03333578
11065 11160 |--Top(TOP EXPRESSION(1))) 1.769826
0 0 |--Compute Scalar(DEFINE[AdventureWorks].[dbo].[test_dt].[date]=[AdventureWorks].[dbo].[te 1.76871
11065 11160 |--Index Seek(OBJECT[AdventureWorks].[dbo].[test_dt].[T_Date_NC]), SEEK[AdventureWo 1.767594SQL Server Execution Times:
CPU time = 300 ms, elapsed time = 715 ms.
--------------------------------------------------------------------------------------------------------------------------------------------Left join version:
Rows Execs Plan Text Subtree Cost ------- ------- ------------------------------------------------------------------------------------------------------------- ------------ 11160 1 select convert(varchar(11),dateadd(day,0,datediff(day, 0, t1.date)),6) [day], T1.task, convert(varchar(12 111.7878 0 0 |--Compute Scalar(DEFINE
[Expr1007]=CONVERT(varchar(11),dateadd(day,(0),CONVERT_IMPLICIT(datetime,datediff( 111.7878
11160 1 |--Sort(ORDER BY[T1].[date] ASC)) 111.7876
11160 1 |--Stream Aggregate(GROUP BY[T1].[Task], [T1].[date]) DEFINE[Expr1006]=MAX([AdventureWorks].[ 111.7483
655814 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES[T1].[date], [Expr1010], [Expr1012]) WITH 100.5336
0 0 |--Compute Scalar(DEFINE[Expr1010]=dateadd(day,(0),CONVERT_IMPLICIT(datetime,datediff 0.03445178
11160 1 | |--Index Scan(OBJECT[AdventureWorks].[dbo].[test_dt].[T_Task] AS [T1]), ORDERED 0.03333578
655719 11160 |--Index Seek(OBJECT[AdventureWorks].[dbo].[test_dt].[T_Date_NC] AS [T2]), SEEK[T2] 22.40909
SQL Server Execution Times:
CPU time = 741 ms, elapsed time = 21623 ms.
--------------------------------------------------------------------------------------------------------------------------------------------
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi All,
Here is another test that shows that the correlated subquery performs much better than the left join when dealing with a larger number of rows.
I've removed the "T2.date >= dateadd(day,0,datediff(day,0,T1.date))" clause from the left join solution because it returns weird results when the "Tasks" happened in different days.
Here is the script:
/* Drop Old Table */
IF
Object_ID('[test_dt]') IS NOT NULL DROP TABLE [test_dt]
/* Create Table */
CREATE
TABLE [test_dt](Task int identity, date datetime)
/* Insert Test Data (50k rows) */
DECLARE
@I int
SET
@I = 50000
WHILE
@I > 0
BEGIN
INSERT
INTO [test_dt] ( [date])
VALUES ( cast(RAND() * 100000 as datetime))
SET
@I = @I-1
END
/* Create Indexes */
CREATE
INDEX T_Date_NC ON [test_dt](date)
CREATE
INDEX T_Task ON [test_dt](Task)
/* Correlated SubQuery Solution */
SELECT
[task]
,
[date]
,
[date] - (select top 1 [date]
from
[test_dt]
where
[date] < main.[date]
order
by [date] desc) as timegap
FROM
[test_dt] as main
order
by [date]
/* Left Join Solution */
select
T1.task,
T1
.date,
T1
.date - max(T2.date) TimeGap
from
test_dt T1
left
join test_dt T2 on T2.date < T1.date
group
by T1.task, T1.date
Antonio Macedo
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply