locked
Need help on spatial index not being used RRS feed

  • Question

  • I'm having problems with the spatial index.  My query doesn't use it.  I've read a number of posts on this but haven't found an answer that fits my situation.

    I believe I've provided all the info below.

    One of the problems is that the query cannot even use the 'hint'.

    Here's the query:

     

    DECLARE @trade_area geometry;

    SET @trade_area = geometry::STPolyFromText(' POLYGON ((-112.089 33.437, -112.054 33.437, -112.03874588012698 

    33.468823945841649, -112.09968566894534 33.46574514445566, -112.089 33.437, -112.089 33.437))',4326)

    select addr_id, subscriber_fl , location_geom

    from if_carrier_points_test points WITH(INDEX(sdx_location_geom)) 

    where dos_id = 5

    and carrier_id > 0

    and points.location_geom.STIntersects(@trade_area) = 1

     

    The error when using hint:

    The query processor could not produce a query plan for a query with a spatial index hint.

    Reason: Could not find required binary spatial method in a condition.

    Here's the table and index creation:

     

    CREATE TABLE [dbo].[if_carrier_points_test](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [dos_id] [tinyint] NOT NULL,

    [zone] [nvarchar](255) NOT NULL,

    [carrier_id] [int] NOT NULL,

    [addr_id] [int] NOT NULL,

    [subscriber_fl] [tinyint] NOT NULL,

    [latitude] [float] NULL,

    [longitude] [float] NULL,

    [location_geom] [geometry] NULL,

    [change_user] [nvarchar](255) NOT NULL,

    [change_dt] [datetime] NOT NULL,

     CONSTRAINT [PK_if_carrier_points_test] PRIMARY KEY CLUSTERED 

    (

    [id] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

     

    GO

     

    CREATE NONCLUSTERED INDEX [indx_carrier_id] ON [dbo].[if_carrier_points_test] 

    ( [carrier_id] ASC)

    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]

    GO

     

    CREATE NONCLUSTERED INDEX [indx_dos_id] ON [dbo].[if_carrier_points_test] 

    ( [dos_id] ASC)

    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    GO

     

    CREATE NONCLUSTERED INDEX [indx_dos_id-carrier_id] ON [dbo].[if_carrier_points_test] 

    (

    [dos_id] ASC,

    [carrier_id] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    GO

     

    -- Creating a geometry index

    CREATE SPATIAL INDEX sdx_location_geom ON if_carrier_points_test ( location_geom )

    USING GEOMETRY_GRID 

    WITH (

      BOUNDING_BOX = (-112, 33, -111, 33.87),   -- (X-min,Y-min) and (X-max,Y-max)

      GRIDS = (

        LEVEL_1 = MEDIUM,

        LEVEL_2 = MEDIUM,

        LEVEL_3 = MEDIUM,

        LEVEL_4 = MEDIUM), 

      CELLS_PER_OBJECT = 16

    )

    GO

    Updating the geometry column:

     

    Update location_geom

    UPDATE if_carrier_points_test

    SET [location_geom] = geometry::STPointFromText('POINT(' + CAST([longitude] AS VARCHAR(20)) + ' ' + 

                        CAST([latitude] AS VARCHAR(20)) + ')', 4326)

    The SQL build:

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   
    Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) 

    The row count in if_carrier_points_test:
    1,662,618

    My bounding box area is the city of Phoenix in building the spatial index.

    Here's the execution plan:

    <?xml version="1.0" encoding="utf-16"?>
    <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.2531.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
      <BatchSequence>
        <Batch>
          <Statements>
            <StmtSimple StatementCompId="2" StatementEstRows="46301.4" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="863.881" StatementText="select addr_id, subscriber_fl , location_geom&#xD;&#xA;from if_carrier_points_test points&#xD;&#xA;where dos_id = 5&#xD;&#xA;and carrier_id &gt; 0&#xD;&#xA;and points.location_geom.STIntersects(@trade_area) = 1&#xD;&#xA;" StatementType="SELECT" QueryHash="0x6950225CE49EA8D6" QueryPlanHash="0xADA2DF6350E5FEDF">
              <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
              <QueryPlan DegreeOfParallelism="2" MemoryGrant="0" CachedPlanSize="16" CompileTime="1351" CompileCPU="729" CompileMemory="152">
                <RelOp AvgRowSize="4028" EstimateCPU="6.51099" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="46301.4" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="863.881">
                  <OutputList>
                    <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="addr_id" />
                    <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="subscriber_fl" />
                    <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="location_geom" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="10795" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Parallelism>
                    <RelOp AvgRowSize="4028" EstimateCPU="832.539" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="46301.4" LogicalOp="Filter" NodeId="1" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="857.37">
                      <OutputList>
                        <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="addr_id" />
                        <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="subscriber_fl" />
                        <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="location_geom" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="1" ActualRows="4598" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="2" ActualRows="6197" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <Filter StartupExpression="false">
                        <RelOp AvgRowSize="4033" EstimateCPU="0.914518" EstimateIO="23.9165" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="46301.4" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="24.831" TableCardinality="1662620">
                          <OutputList>
                            <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="addr_id" />
                            <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="subscriber_fl" />
                            <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="location_geom" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="1" ActualRows="808371" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="2" ActualRows="853454" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="addr_id" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="subscriber_fl" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="location_geom" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Index="[PK_if_carrier_points_test]" Alias="[points]" IndexKind="Clustered" />
                            <Predicate>
                              <ScalarOperator ScalarString="[iFocus].[dbo].[if_carrier_points_test].[dos_id] as [points].[dos_id]=(5) AND [iFocus].[dbo].[if_carrier_points_test].[carrier_id] as [points].[carrier_id]&gt;(0)">
                                <Logical Operation="AND">
                                  <ScalarOperator>
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="dos_id" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(5)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Compare CompareOp="GT">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="carrier_id" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="(0)" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Logical>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                        <Predicate>
                          <ScalarOperator ScalarString="CONVERT_IMPLICIT(tinyint,[iFocus].[dbo].[if_carrier_points_test].[location_geom] as [points].[location_geom].STIntersects([@trade_area]),0)=(1)">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Convert DataType="tinyint" Style="0" Implicit="true">
                                  <ScalarOperator>
                                    <UDTMethod>
                                      <CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlGeometry" Method="STIntersects" />
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[iFocus]" Schema="[dbo]" Table="[if_carrier_points_test]" Alias="[points]" Column="location_geom" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="@trade_area" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </UDTMethod>
                                  </ScalarOperator>
                                </Convert>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </Filter>
                    </RelOp>
                  </Parallelism>
                </RelOp>
                <ParameterList>
                  <ColumnReference Column="@trade_area" ParameterRuntimeValue="POLYGON ((-112.089 33.437, -112.054 33.437, -112.03874588012698 33.468823945841649, -112.09968566894534 33.46574514445566, -112.089 33.437, -112.089 33.437))" />
                </ParameterList>
              </QueryPlan>
            </StmtSimple>
          </Statements>
        </Batch>
      </BatchSequence>
    </ShowPlanXML>


    Any help sure would be appreciated.

    Thxs.

    Jay

     

     

    Thursday, August 12, 2010 10:54 PM

Answers

  • The only time i've seen the "Could not find required binary spatial method" query plan error message was on a database that had been set to SQL Server 2000 compatibility.

    Not sure if that's the problem in your case, but it wouldn't do any harm to check - you can find the DB compatibility level in SSMS under Properties -> Options


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by JayViz Friday, August 13, 2010 3:42 PM
    Friday, August 13, 2010 8:04 AM
    Answerer

All replies

  • The only time i've seen the "Could not find required binary spatial method" query plan error message was on a database that had been set to SQL Server 2000 compatibility.

    Not sure if that's the problem in your case, but it wouldn't do any harm to check - you can find the DB compatibility level in SSMS under Properties -> Options


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by JayViz Friday, August 13, 2010 3:42 PM
    Friday, August 13, 2010 8:04 AM
    Answerer
  • YES ! That was it.  I did come across that option in my searches but couldn't find the setting in SSMS.  But looking again per your suggestion, there it was, in 2000 compatibility.  I was looking down in "Other options".

    Now I can start a little tuning.

    Thanks again.

    Jay

    Friday, August 13, 2010 3:42 PM