Correct index strategy for BETWEEN

  • Statistics updated with fullscan, no change - still estimated 1 rows, actual 87k. I don't know why sql server chooses this plan, really, But, since the intervals do not overlap, the query could be actually rewritten like this:

    DECLARE @nIpFrom bigint = (SELECT MAX(IpFrom) FROM GeoIPBlock WHERE IpFrom <= @nIpNumber)

    SELECTTOP 1

    GeoIpLocation.Country,

    GeoIpLocation.City

    FROMGeoIPLocation

    INNER JOIN GeoIPBlock

    ON GeoIPBlock.GeoIpLocation_Id = GeoIPLocation.Id

    WHEREGeoIPBlock.nIpFrom = @nIpFrom AND

    GeoIPBlock.nIpTo >= @nIpNumber

    First query selects maximum from available intervals for IpFrom, without looking at IpTo. Then, the second query can use the PK index in al its glory. The result - 0 ms πŸ™‚

    Thanks everyone who did participate in this problem.

  • Jakub.Janda (3/24/2015)


    Statistics updated with fullscan, no change - still estimated 1 rows, actual 87k. I don't know why sql server chooses this plan, really, But, since the intervals do not overlap, the query could be actually rewritten like this:

    DECLARE @nIpFrom bigint = (SELECT MAX(IpFrom) FROM GeoIPBlock WHERE IpFrom <= @nIpNumber)

    SELECTTOP 1

    GeoIpLocation.Country,

    GeoIpLocation.City

    FROMGeoIPLocation

    INNER JOIN GeoIPBlock

    ON GeoIPBlock.GeoIpLocation_Id = GeoIPLocation.Id

    WHEREGeoIPBlock.nIpFrom = @nIpFrom AND

    GeoIPBlock.nIpTo >= @nIpNumber

    First query selects maximum from available intervals for IpFrom, without looking at IpTo. Then, the second query can use the PK index in al its glory. The result - 0 ms πŸ™‚

    Thanks everyone who did participate in this problem.

    Similar to this approach:

    -- Test data

    DROP TABLE #Temp

    CREATE TABLE #Temp (

    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

    Startval INT NOT NULL,

    Endval INT NOT NULL,

    CONSTRAINT pp_ck1 CHECK (Endval >= Startval) )

    INSERT INTO #Temp (Startval, Endval)

    SELECT TOP 1000000 Startval, EndVal = Startval + ABS(CHECKSUM(NEWID()))%100

    FROM (

    SELECT Startval = ABS(CHECKSUM(NEWID()))%1000000

    FROM SYS.COLUMNS a, SYS.COLUMNS b, SYS.COLUMNS c

    ) d

    update statistics #Temp with fullscan

    CREATE INDEX ix_Range1 ON #Temp (Startval,Endval)

    CREATE INDEX ix_Range2 ON #Temp (Endval,Startval)

    -----------------------------------------------------------------------------

    -- Test queries

    DECLARE @integer INT

    SET @integer = 20760

    -- Query 1 - original

    SELECT n=1 FROM #Temp WHERE @integer BETWEEN Startval AND Endval

    -- Query 2, 1 x MIN

    SELECT n=1

    FROM #Temp t

    CROSS APPLY (SELECT MIN_Startval = MIN(Startval) FROM #Temp WHERE Endval >= @integer) x

    WHERE t.Startval BETWEEN MIN_Startval AND @integer

    AND @integer BETWEEN Startval AND Endval

    -- Query 3, 1 x TOP

    SELECT n=1

    FROM #Temp t

    CROSS APPLY (SELECT TOP 1 Startval FROM #Temp WHERE Endval >= @integer ORDER BY Startval) x

    WHERE t.Startval BETWEEN x.Startval AND @integer

    AND @integer BETWEEN t.Startval AND Endval

    -- Query 4, 2 x MIN

    SELECT n=1

    FROM #Temp t

    CROSS APPLY (SELECT MIN_Startval = MIN(Startval) FROM #Temp WHERE Endval >= @integer) x

    CROSS APPLY (SELECT MAX_Endval = MAX(Endval) FROM #Temp WHERE Startval <= @integer) y

    WHERE t.Startval BETWEEN MIN_Startval AND @integer

    AND t.EndVal BETWEEN @integer and MAX_Endval

    AND @integer BETWEEN Startval AND Endval

    -- Query 5, 2 x TOP

    SELECT n=1

    FROM #Temp t

    CROSS APPLY (SELECT TOP 1 Startval FROM #Temp WHERE Endval >= @integer ORDER BY Startval) x

    CROSS APPLY (SELECT TOP 1 Endval FROM #Temp WHERE Startval <= @integer ORDER BY Endval DESC) y

    WHERE t.Startval BETWEEN x.Startval AND @integer

    AND t.EndVal BETWEEN @integer and y.Endval

    AND @integer BETWEEN t.Startval AND t.Endval

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • This is pretty clever and will work even on overlapping intervals pretty fast - at least until someone inserts interval (minint, maxint) πŸ™‚

Viewing 3 posts - 16 through 17 (of 17 total)

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