SQL Server Geometry – Poor Cardinality Estimate on Intersection

In my tests, no, adding WITH SCHEMABINDING does not improve cardinality estimates. I created a simple table:

CREATE TABLE dbo.myobjects(id INT PRIMARY KEY);

INSERT dbo.myobjects SELECT [object_id] FROM sys.all_objects;

Then two functions:

CREATE FUNCTION dbo.noschemabinding(@UserID INT)
RETURNS @x TABLE (id INT)
AS
BEGIN
  INSERT @x SELECT id FROM dbo.myobjects;

  RETURN;
END
GO

CREATE FUNCTION dbo.withschemabinding(@UserID INT)
RETURNS @x TABLE (id INT)
WITH SCHEMABINDING
AS
BEGIN
  INSERT @x SELECT id FROM dbo.myobjects;

  RETURN;
END
GO

Comparing the actual plans, both show estimated rows = 1, actual rows = 2112 (this latter number may differ on your system depending on version/SP etc).

Comparing the speed:

SET NOCOUNT ON;
GO
SELECT SYSDATETIME();
GO
SELECT id INTO #x FROM dbo.noschemabinding(1);
DROP TABLE #x;
GO 1000
GO
SELECT SYSDATETIME();
GO
SELECT id INTO #x FROM dbo.withschemabinding(1);
DROP TABLE #x;
GO 1000
SELECT SYSDATETIME();

Results:

                    run 1               run 2
----------------    ------------------  ------------------
No schemabinding    14632 milliseconds  14079 milliseconds
Schemabinding       14251 milliseconds  13979 milliseconds

So, does it matter much? Nope.

SCHEMABINDING in this case is used for a more important goal: underlying schema stability. You will probably have much better optimization opportunities if you pursue converting your function to an inline TVF than to chase down obscure plan-affecting differences in a multi-statement TVF.

Firstly, check whether a spatial index is being used by looking at the query execution plan and see if there is a Clustered Index Seek (Spatial) item.

Assuming it is being used, you could try adding a secondary/simplified filter based on a bounding box with simplified polygons to check for first. Matches against these simplified polygons could then be run through the primary filter to get the final results.

1) Add a new geography and geometry column to the [dbo].[T_POLYGON] table:

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeom geometry;
ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog geography;

2) Create the bounding box polygons (this involves an initial conversion to geometry to take advantage of STEnvelope()):

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeom = geometry::STGeomFromWKB(
    COORD.STAsBinary(), COORD.STSrid).STEnvelope();

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeog = geography::STGeomFromWKB(
    SimplePolysGeom.STAsBinary(), SimplePolysGeom.STSrid);

3) Create a spatial index on the simplified geography column

4) Get the intersections against this simplified geography column, then filter again on the matching geography data types. Roughly, something like this:

;WITH cte AS
(
   SELECT pinID, polygonID FROM T_PIN INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.SimplePolysGeog ) = 1
)
SELECT COUNT(*)
FROM T_PIN 
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1
    AND T_PIN.pinID IN (SELECT pinID FROM cte)
    AND T_POLYGON.polygonID IN (SELECT polygonID FROM cte)

EDIT: you can replace (1) and (2) with this computed, persisted column. credit to Paul White for the suggestion.

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog AS  ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([COORD].[STAsBinary](),[COORD].[STSrid]).STEnvelope().STAsBinary(),(4326))) PERSISTED