Spatial Index slows down query with STContains

Question

I'm testing if I can use spatial indices on SQL Server 2012.

So, I've got a table

CREATE TABLE [dbo].[Records]
(
 [ID] [uniqueidentifier] PRIMARY KEY NOT NULL,
 [Value] [nvarchar](256) NOT NULL,
 [Padding] [nvarchar](max) NOT NULL,
 [Bounds] [geometry] NOT NULL
)

and an index

CREATE SPATIAL INDEX [RecordsSpatialIndex]
ON [Records]([Bounds])
USING GEOMETRY_GRID
WITH
(
    BOUNDING_BOX = (0, 0, 2000, 2000) -- all coordinates are within this range
);

[Bounds] column contains 5-point polygons, actually rectangles (x1 y1, x1 y2, x2 y2, x2 y1, x1 y1).

@bounds variable contains the same type of rectangle. Strange thing is that the following query

SELECT
    [ID], [Value], [Padding]
FROM
    [Records]
WHERE
    ([Bounds].STContains(@Bounds) = 1)

runs more than three times faster without the spatial index.

With the index 65% of time is Clustered Index Seek over Records table and 29% is Filter. Totally 65 seconds.

Without the index 92% of time is Filter and 8% is Clustered Index Scan over Records table. Totally 19 seconds.

So, what am I doing wrong here?

Check out the query plan. There are times when the query optimizer is not optimal. But it collects statistics and get smarter. — paparazzo
– paparazzo, Commented Jul 7, 2012 at 20:10

Lynn Langit · Accepted Answer · 2012-08-31 19:26:06Z

2

Also this MSDN article was helpful to me in understand how to create indexes which are useful for particular queries

answered Aug 31, 2012 at 19:26

Lynn Langit

4,0601 gold badge25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spatial Index slows down query with STContains

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related