Re: Get the smallest timespan between two entries

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 10/27/04 

Date: Wed, 27 Oct 2004 23:28:56 +0200

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)

Relevant Pages

  • Re: Get the smallest timespan between two entries
    ... >>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 ... >benefit from an index on rectime. ... >rate of change of this table is not too high and a small performance hit ...
    (microsoft.public.sqlserver.mseq)
  • Re: Get the smallest timespan between two entries
    ... Oops - the suggestion I gave doesn't give a good query plan. ... datediff(minute,rectime, Nextrectime) as TimeDiff ... >>We want to get the smallest timespan between two entries. ... >benefit from an index on rectime. ...
    (microsoft.public.sqlserver.mseq)