Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have one table, T_PIN, with 300,000 pins and another, T_POLYGON, with 36,000 polygons.

T_PIN has this index :

CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN]
(
[Coord]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 128, 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]  

and this one on T_POLYGON:

CREATE SPATIAL INDEX [T_POLYGON_COORD] ON [dbo].[T_POLYGON]
(
[COORD]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 128, 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 to find the intersect of T_PIN and T_POLYGON is very long, more than 45 minutes:

SELECT COUNT(*)
from T_PIN inner join T_POLYGON on T_PIN.Coord.STIntersects(T_POLYGON.COORD)=1

The result is 4 438 318

How can I accelerate it?

share|improve this question

migrated from stackoverflow.com Dec 6 '13 at 10:27

This question came from our site for professional and enthusiast programmers.

    
Have you tried using `T_POLYGON.Coord.STIntersects(T_PIN.COORD)=1'? –  travis Jan 21 at 21:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.