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.b
    OR @decBeginStation < t.E
    )
    AND (
    @decEndStation > t.b
    OR @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.b
    OR @decBeginStation < t.E
    )
    AND (
    @decEndStation > t.b
    OR @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.

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • 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 t
    CROSS APPLY (
    SELECT CASE
    WHEN @decBeginStation > @decEndStation THEN -1
    ELSE 1
    END AS n
    ) m
    WHERE (
    @decBeginStation * m.n < t.b * m.n
    OR @decBeginStation * m.n < t.E * m.n
    )
    AND (
    @decEndStation * m.n > t.b * m.n
    OR @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.E
    FROM cteData AS cte
    WHERE @decBeginStation < cte.nd
    AND @decEndStation > cte.st
    ORDER 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)

You must be logged in to reply to this topic. Login to reply