September 19, 2014 at 11:08 am
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
September 19, 2014 at 4:44 pm
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply