SQL Spatial - STIntersection performance problem

• keith.west-982594

  SSC Eights!

  Points: 965

  More actions

  September 19, 2014 at 11:08 am

  #290936

  I have been using SQL Spatial for since its inception in SQL 2005. My current environment is SQL Server 2012 SP1. I am now having an issue that I need to resolve. I have a spatial query (see below) the is optimized to use the spatial index with the STIntersects function. This query works fine, but as soon as I add a calculation for getting the Area of Intersect(STIntersection.STArea()), the performance goes down the tubes. I have recreated the indexes in multiple ways using all HIGH, MEDIUM, and LOW Levels and varying combinations of each and also several different values for CELLS_PER_OBJECT

  Table1: 1 million records

  CREATE TABLE TABLE1 (TABLE1_ID INT PRIMARY KEY CLUSTERED, TABLE1SHAPE GEOGRAPHY)

  CREATE SPATIAL INDEX SPATIAL_TABLE1 ON TABLE1 (TABLE1SHAPE) USING GEOGRAPHY_GRID

  WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),

  CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  TABLE2: 40 MILLION RECORDS

  CREATE TABLE TABLE2 (TABLE2_ID INT PRIMARY KEY CLUSTERED, TABLE2SHAPE GEOGRAPHY)

  CREATE SPATIAL INDEX SPATIAL_TABLE2 ON TABLE2 (TABLE2SHAPE) USING GEOGRAPHY_GRID

  WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),

  CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  This query returns 25583 records in 13 seconds (reasonable):

  SELECT X.TABLE1_ID,

  Y.TABLE2_ID

  FROM TABLE1 X JOIN TABLE2 Y ON X.TABLE1SHAPE.STINTERSECTS(Y.TABLE2SHAPE) = 1

  WHERE X.TABLE1_ID = 257314

  This query returns 25583 records in 3 minute 10 seconds (not reasonable):

  SELECT X.TABLE1_ID,

  Y.TABLE2_ID,

  X.TABLE1SHAPE.STINTERSECTION(X.TABLE2SHAPE)

  FROM TABLE1 X JOIN TABLE2 Y ON X.TABLE1SHAPE.STINTERSECTS(Y.TABLE2SHAPE) = 1

  WHERE X.TABLE1_ID = 257314

  This what i actually need. I need to be able to calculate the percentage of intersect of the shapes

  This query returns 25583 records in 1 minute 20 seconds (not reasonable):

  SELECT X.TABLE1_ID,

  Y.TABLE2_ID,

  X.TABLE1SHAPE.STINTERSECTION(X.TABLE2SHAPE).STAREA()/Y.TABLE2SHAPE.STAREA()

  FROM TABLE1 X JOIN TABLE2 Y ON X.TABLE1SHAPE.STINTERSECTS(Y.TABLE2SHAPE) = 1

  WHERE X.TABLE1_ID = 257314

  I have a couple of million TABLE1_IDs that I need to perform this calculation on. Any help is greatly appreciated.

  Regards,

  Keith

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 19, 2014 at 4:44 pm

  #1746840

  1) are the plans the same? even with 2012 I believe the optimizer and engine have limitations about index usage and good plan formulation.

  2) have you tried pre-computing the areas of each object and accessing that directly? That should remove part of the performance issue during query runtime if I understand the need correctly (the denominator looks to be the full area of table 2 object).

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service