Hi
Sorry for the delay getting back ... I see what you are doing now. While you can use geometry to do this, it probably isn't the quickest way to resolve the query. You would probably be better with left outer join geo2ip on ip_convert between ip2startinteger and ip2endinteger
Also STIntersects is probably the best method to use for this (point on line), if you decide to carry on with geometries.
I did a bit of testing on this using some made up data. The one thing that did kill the query was when I used the following spatial index definition across my range.
create spatial index g2i_sdx on #geo2ip (geo_range)
using geometry_grid
with (bounding_box = (0, -2, 2001000, 0), grids=(HIGH,MEDIUM,LOW,LOW));
So avoid using 0 in either the miny or maxy bounding box parameters.
This is the test script that I ran. I was suprised to see that the geometry query perform as well if not better than the int between query. So I've had to eat my words above.
-- fact table with 10000 points some overlapping some not
create table #fact_table (
id int identity primary key,
ip_converted int
);
with tally as (
select row_number() OVER (order by (select null)) N
from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1 (N),
(values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2 (N)
),
createFact as (
select CAST(RAND(CAST(NEWID() AS VARBINARY)) * 2000000.0 AS INT) ip_converted
from tally x, tally y
)
insert into #fact_table ( ip_converted)
select ip_converted
from createFact;
-- Create a grid table with 1,000,000 cells
create table #geo2ip (
id int identity primary key
,geo_range geometry
,ipstartint int
,ipendint int
);
with tallyPlus as (
select row_number() OVER (order by (select null)) N,
CAST(RAND(CAST(NEWID() AS VARBINARY)) * 1000000.0 AS INT) R
from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1 (N),
(values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2 (N),
(values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E3 (N)
),
createLine as (
select Geometry::STGeomFromText('LINESTRING ('
+ CAST(cast(x.R + y.R as int) AS VARCHAR(10)) + ' 0, ' +
+ CAST(cast(x.R + y.R + 2 as int) AS VARCHAR(10)) + ' 0)',0) geo_range,
cast(x.R + y.R as int) ipstartint,
cast(x.R + y.R + 2 as int) ipendint
from tallyPlus x, tallyPlus y
)
insert into #geo2ip (geo_range, ipstartint, ipendint)
select geo_range, ipstartint, ipendint
from createLine;
create spatial index g2i_sdx on #geo2ip (geo_range)
using geometry_grid
with (bounding_box = (0, -2, 2001000, 1), grids=(HIGH,MEDIUM,LOW,LOW));
create index g2i_ip_idx on #geo2ip (ipstartint, ipendint);
select f.id,
g.id,
ip_converted
from #fact_table f
left outer join #geo2ip g on geo_range.STIntersects(
geometry::STGeomFromText('POINT('+CONVERT(varchar(20),f.IP_Converted)+' 0)',0)
) = 1;
select f.id,
g.id,
ip_converted
from #fact_table f
left outer join #geo2ip g on ip_converted between ipstartint and ipendint;
drop table #fact_table;
drop table #geo2ip;