• 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;