March 24, 2015 at 7:51 am
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.
March 24, 2015 at 10:33 am
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
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
March 24, 2015 at 11:02 am
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