Re: Get the smallest timespan between two entries

From: Steve Kass (skass_at_drew.edu)
Date: 10/29/04

  • Next message: Hugo Kornelis: "Re: Get the smallest timespan between two entries" 
    Date: Thu, 28 Oct 2004 20:31:00 -0400

    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 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
    >
    >

  • Next message: Hugo Kornelis: "Re: Get the smallest timespan between two entries"

    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
      ... >We want to get the smallest timespan between two entries. ... Try changing the query 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)