Get the smallest timespan between two entries

Hi all,
we have a table with a column of type "datetime"
We want to get the smallest timespan between two entries.
Now we get this timespan with the following query (it works but it's to
slow, it runs 5 secs with 250000 entries):
select min(Datediff(minute,a.rectime,b.rectime))
from dbo.value a, dbo.value b
where b.rectime = ( select min(rectime) from dbo.value
where rectime > a.rectime )
Any idea? Thanks in advance,
Mike
On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:

>Hi all,
>we have a table with a column of type "datetime"
>We want to get the smallest timespan between two entries.
>Now we get this timespan with the following query (it works but it's to
>slow, it runs 5 secs with 250000 entries):
>select min(Datediff(minute,a.rectime,b.rectime))
>from dbo.value a, dbo.value b
>where b.rectime = ( select min(rectime) from dbo.value
> where rectime > a.rectime )
>Any idea? Thanks in advance,
>Mike
Hi Mike,
Try changing the query to
SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
FROM dbo.value AS a, dbo.value b
WHERE b.rectime > a.rectime
You might also add something like
AND b.rectime < DATEADD(minute, a.rectime, 200)
where you change the 200 to a value that you know to be higher that the
timespan you are looking for, but low enough to greatly reduce the number
of matches between the a and b version of the value table.
If that doesn't work, look at your indexes. This query would greatly
benefit from an index on rectime (or rectime plus extra columns). If the
rate of change of this table is not too high and a small performance hit
on inserts, updates and deletes is acceptable, create a nonclustered index
on only rectime - that should yield the best possible performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||This might be more efficient:
select top 1
-- add WITH TIES if you select additional columns and want duplicates
datediff (minute, T2.rectime, min(T1.rectime)) as timeDiffMinutes
from yourTable T1 join yourTable T2
on T1.rectime > T2.rectime
group by T2.rectime
order by min(T1.rectime) - T2.rectime
Steve Kass
Drew University
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>Hi Mike,
>Try changing the query to
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>You might also add something like
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>Best, Hugo
>
|||Oops - the suggestion I gave doesn't give a good query plan. This is
probably much better:
select top 1
datediff(minute,rectime, Nextrectime) as TimeDiff
from (
select
T1.rectime,
(select top 1 T2.rectime
from yourTable T2
where T2.rectime> T1.rectime
order by T2.rectime) as Nextrectime
from yourTable T1
) T
where Nextrectime is not null
order by Nextrectime - rectime
[and I shouldn't have replied to your post specifically - sorry]
SK
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>Hi Mike,
>Try changing the query to
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>You might also add something like
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>Best, Hugo
>
|||On Thu, 28 Oct 2004 20:31:00 -0400, Steve Kass wrote:

>and I shouldn't have replied to your post specifically - sorry
Hi Steve,
De nada. As long as the original poster sees it, all's well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)