# how to optimise overlap query

• rajemessage 14195

Hall of Fame

Points: 3125

-- in following  "#t" table one can enter b as 10 and e as 2 that means e can be smaller than b and b can be smaller than e

or both could be equal.

-- for that i have used following logic in where clause i want to known is there any short way to do.

DECLARE

@decBeginStation DECIMAL(13,4)=-2,

@decEndStation DECIMAL(13,4)=10

CREATE table #t ( tid int, b decimal(13,4) not null, E decimal(13,4) not null);

Insert into #T (tid,B,E)

VALUES (1, -12, -2),

(2, -10, 0),

(3, -2, 8),

(4, 10, 0),

(5, 8, 18),

(6, 10, 20),

(7, 21, 11),

(8, 0, 3),

(9, 2, 4),

(10, 3, 10),

(11, -1, 10),

(12, -1, 11),

(13, 0, 11)

;

select *  from #t AS t

WHERE  (

NOT (

@decBeginStation >= t.b

AND @decBeginStation >= t.E

AND @decEndStation >= t.b

AND @decEndStation >= t.E

)

AND NOT (

@decBeginStation <= t.b

AND @decBeginStation <= t.E

AND @decEndStation <= t.b

AND @decEndStation <= t.E

)

)

drop table #t

• John Mitchell-245523

SSC Guru

Points: 148652

If @EndStation is always greater than @BeginStation, then you can simplify like this.  Note that I've also removed the NOTs and changed ANDs to ORs and >= to < and <= to >, simply because I find it easier to read without the NOTs.

`WHERE (    @decBeginStation < t.bOR  @decBeginStation < t.E    )AND (    @decEndStation > t.bOR  @decEndStation > t.E    )`

John

• MVDBA (Mike Vessey)

SSC-Insane

Points: 21492

John Mitchell-245523 wrote:

If @EndStation is always greater than @BeginStation, then you can simplify like this.  Note that I've also removed the NOTs and changed ANDs to ORs and >= to < and <= to >, simply because I find it easier to read without the NOTs.

`WHERE (    @decBeginStation < t.bOR  @decBeginStation < t.E    )AND (    @decEndStation > t.bOR  @decEndStation > t.E    )`

John

back in 2005 (yes 15 years ago) itzik ben-gan showed us a trick on overlaps - mainly to try and get rid of that horrible "OR" bit in a query

if you have a table of "things that happened" and it includes a start and date date - the criteria for the search is "anything that was occurring  during @start and @end

so we have several scenarios

1. started in the period - might have ended after @end but still valid
2. ended in the period but might have started before @start
3. started before @start and ended after @end (it was occuring during the period)

Itzik's logic was so simple I still struggle - -

Select * from mytable where startdate<@end and enddate>@start

not sure if this helps the Original poster, but it's a lovely little trick when dealing with overlaps (be it milage, dates, or numbers)

MVDBA

• jcelko212 32090

SSCrazy Eights

Points: 8923

I would redo your DDL and follow ISO 11179 naming rules. You don't need a row identifier, so we can drop one of the columns. You also need to enforce the constraints on your data with a check clause. Also, did you know that "E" is used for defining floating-point constants, so it makes a lousy data element name.

CREATE TABLE Stations

(begin_station DECIMAL(13,4) NOT NULL,

end_station DECIMAL(13,4) NOT NULL,

CHECK (begin_station < end_station),

PRIMARY KEY (begin_station, end_station));

INSERT INTO Stations

VALUES

(-12, -2),(-10, 0),(-2, 8),(10, 0),

(8, 18),(10, 20),(21, 11),(0, 3),

(2, 4),(3, 10),(-1, 10),(-1, 11),( 0, 11);

CREATE PROCEDURE Overlaps (@in_begin_station DECIMAL(13,4), @in_end_station DECIMAL(13,4))

AS

SELECT begin_station, end_station

FROM Stations

WHERE (@in_begin_station < begin_station

OR @in_begin_station < end_station)

AND (@in_end_station > begin_station

OR @in_end_station > end_station)

There is also an OVERLAPS() function in ANSI/ISO  Standard SQL.

• rajemessage 14195

Hall of Fame

Points: 3125

the situation at my end is that begin can be less than equal to or greater than equal to. and by logic works for it , only thing is, it is a little bit big, so I thought someone might have a smaller version.

at most what I can restrict is that begin will not be equal to end.

yours sincerley

• MVDBA (Mike Vessey)

SSC-Insane

Points: 21492

rajemessage 14195 wrote:

the situation at my end is that begin can be less than equal to or greater than equal to. and by logic works for it , only thing is, it is a little bit big, so I thought someone might have a smaller version.

at most what I can restrict is that begin will not be equal to end.

yours sincerley

again - I pretty much think this covers you

Select * from mytable where startdate<@end and enddate>@start

MVDBA

• rajemessage 14195

Hall of Fame

Points: 3125

no it does not, if @BeginStation is greater than @EndStation.

if  b =0   and e =10 and  @BeginStation = 18 and  @EndStation = 8

then it will no fall in first block @BeginStation is never less than 'b' nor 'e'

WHERE (

@decBeginStation < t.b

OR @decBeginStation < t.E

)

AND (

@decEndStation > t.b

OR @decEndStation > t.E

)

it will only work if  @BeginStation is less than @EndStation.

• MVDBA (Mike Vessey)

SSC-Insane

Points: 21492

rajemessage 14195 wrote:

no it does not, if @BeginStation is greater than @EndStation.

if  b =0   and e =10 and  @BeginStation = 18 and  @EndStation = 8

then it will no fall in first block @BeginStation is never less than 'b' nor 'e'

WHERE (

@decBeginStation < t.b

OR @decBeginStation < t.E

)

AND (

@decEndStation > t.b

OR @decEndStation > t.E

)

it will only work if  @BeginStation is less than @EndStation.

so , easy solution - check the variables and swap them over before you run the query

declare @Tmp int

if @start>@end

begin

set @Tmp=@end

set @end=@start

set start=@tmp

end

MVDBA

• John Mitchell-245523

SSC Guru

Points: 148652

Indeed, or multiplying everything by -1 may also work, although I haven't actually tested it.

`SELECT t.*FROM #t AS tCROSS APPLY (	SELECT	CASE			WHEN @decBeginStation > @decEndStation THEN -1			ELSE 1		END AS n	) mWHERE (    @decBeginStation * m.n < t.b * m.nOR  @decBeginStation * m.n < t.E * m.n    )AND (    @decEndStation * m.n > t.b * m.nOR  @decEndStation * m.n > t.E * m.n    )`

John

• DesNorton

SSC-Insane

Points: 22933

Based on your sample data, this returns the same result.

`WITH cteData AS (  SELECT      t.tid, t.b, t.E, seq.st, seq.nd  FROM        #t AS t  CROSS APPLY ( SELECT  st = MIN( ord.v )                      , nd = MAX( ord.v )                FROM    ( VALUES ( t.b ), ( t.E ) ) AS ord ( v )              )  AS seq  -- This swaps the b/e to ensure that b is alway <= e)SELECT    cte.tid, cte.b, cte.EFROM      cteData AS cteWHERE     @decBeginStation < cte.nd  AND     @decEndStation   > cte.stORDER BY  cte.tid;`

The trick is to re-order the b/e to ensure that b is always <= e.

However, you will need to check this against your data to see whether there is a performance gain or loss.

• rajemessage 14195

Hall of Fame

Points: 3125

agreed,

but situation is such. we want to take this challenge , and I have posted that solution at the top, it works in all conditions, the only thing I wanted was if it could be optimized.

• John Mitchell-245523

SSC Guru

Points: 148652

Only you can know that.  You have your solution and several others provided by contributors to this thread.  Have you compared the execution plans or done some load testing to see whether there's any improvement?

John

• rajemessage 14195

Hall of Fame

Points: 3125

ya, I have checked, I will update with an example why they do not fit into.

primarily they do not give result in one go,

first they find the small and big then they do the overlap check.

I want it to be done in one go because that could be faster than two passes

and my solution, gives result in one go, but it has more predicates(clauses). which I wanted to reduce if possible.

• rajemessage 14195

Hall of Fame

Points: 3125

both are working, the only thing is first they multiply by using cross apply, that takes one pass, then where clause is applied to fillter, that makes the second pass.

in my case, if u see it does in one pass only. if there is any way to do it in one pass with fewer predicates then pls share.

• John Mitchell-245523

SSC Guru

Points: 148652

What do you mean by "pass"?  All the solutions posted so far read the table only once.  Why is it so important to reduce the number of lines of code - is this a homework question?

John

Viewing 15 posts - 1 through 15 (of 16 total)