Fastest way to locate overlapping time blocks

Hello, I am searching for an absolutely fastest SQL to locate
overlapping time blocks. I've tried various things, but i am a c# guy,
and sql is not my strongest suit.
create table TimeBlocks
(
TimeBlockStartsAt datetime
TimeBlockEndsAt datetime
)
I want to find a condition where one row overlaps another one
(time-wise). I've tried looping through rows and comparing one by one,
but the table contains millions of rows and the procedure is slow.
Thanks.Try:
select
*
from
TimeBlocks t1
join
TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frank Rizzo" <none@.none.com> wrote in message
news:OQbUlSuyGHA.3464@.TK2MSFTNGP03.phx.gbl...
Hello, I am searching for an absolutely fastest SQL to locate
overlapping time blocks. I've tried various things, but i am a c# guy,
and sql is not my strongest suit.
create table TimeBlocks
(
TimeBlockStartsAt datetime
TimeBlockEndsAt datetime
)
I want to find a condition where one row overlaps another one
(time-wise). I've tried looping through rows and comparing one by one,
but the table contains millions of rows and the procedure is slow.
Thanks.|||Tom Moreau wrote:
> Try:
> select
> *
> from
> TimeBlocks t1
> join
> TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>
Thanks, Tom. I've tried this (with a clustered index), but it is too
slow for the amount of records I have. Not sure whether SQL can come up
with something faster. I think I am gonna have to come with a
completely alternative solution for this problem.|||What might help is to have a primary key, perhaps an identity. Then you can
use nonclustered indexes on the time columns. Also, experiment with adding
a nonclustered on each time column, as well as on both columns together.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frank Rizzo" <none@.none.com> wrote in message
news:%234q$mxuyGHA.4932@.TK2MSFTNGP02.phx.gbl...
Tom Moreau wrote:
> Try:
> select
> *
> from
> TimeBlocks t1
> join
> TimeBlocks t2 on (t1.TimeBlockStartsAt <=> t2.TimeBlockStartsAt
> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>
Thanks, Tom. I've tried this (with a clustered index), but it is too
slow for the amount of records I have. Not sure whether SQL can come up
with something faster. I think I am gonna have to come with a
completely alternative solution for this problem.|||On Mon, 28 Aug 2006 14:53:29 -0700, Frank Rizzo <none@.none.com> wrote:
>> select
>> *
>> from
>> TimeBlocks t1
>> join
>> TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
>> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
>> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>Thanks, Tom. I've tried this (with a clustered index), but it is too
>slow for the amount of records I have. Not sure whether SQL can come up
>with something faster. I think I am gonna have to come with a
>completely alternative solution for this problem.
Is that what you want, a list out of your zillion rows of which have
overlaps, or will you have a fixed interval you want to search for?
I've done a little of this (more second case), and it is likely to be
slow (esp the first case), even when you're all indexed.
Might do a little better if the first test is t1.timeblockstartsat is
between x and y, if your intervals are typically short you can compute
reasonable x and y as start-i and start+i.
J.|||JXStern wrote:
> On Mon, 28 Aug 2006 14:53:29 -0700, Frank Rizzo <none@.none.com> wrote:
>> select
>> *
>> from
>> TimeBlocks t1
>> join
>> TimeBlocks t2 on (t1.TimeBlockStartsAt <= t2.TimeBlockStartsAt
>> and t1.TimeBlockEndsAt >= t2.TimeBlockStartsAt)
>> Also, create a clustered index on (TimeBlockStartsAt, TimeBlockStartsAt).
>> Thanks, Tom. I've tried this (with a clustered index), but it is too
>> slow for the amount of records I have. Not sure whether SQL can come up
>> with something faster. I think I am gonna have to come with a
>> completely alternative solution for this problem.
> Is that what you want, a list out of your zillion rows of which have
> overlaps, or will you have a fixed interval you want to search for?
> I've done a little of this (more second case), and it is likely to be
> slow (esp the first case), even when you're all indexed.
> Might do a little better if the first test is t1.timeblockstartsat is
> between x and y, if your intervals are typically short you can compute
> reasonable x and y as start-i and start+i.
Thanks, that's what I found. I think I'll have to change gears and
record more information when I enter the row (e.g. check whether it
overlaps then) and mark a field or something to that effect.