I am trying to split polylines by points which have a small buffer around them. Currently I have over 370,000 lines and 320,000 nodes and the query is running really slowly (I've left it for 3 days and it still hasn't completed). I have tried forcing a spatial index using
with (Index(SI_tempPD)) but I get the following error:
"The query processor could not produce a query plan for a query with a spatial index hint. Reason: Could not find required binary spatial method in a condition. Try removing the index hints or removing SET FORCEPLAN."
Below is the snippet of code that I'm trying to run when I get the error:
BEGIN INSERT INTO TempLines ( [linenum] ,[ogr_geometry] ) SELECT lines.[linenum] ,lines.[ogr_geometry].STDifference(points.[ogr_geometry].STBuffer(0.005)) AS ogr_geometry FROM dbo.TemplineData AS lines with(Index(SI_tempPD)) INNER JOIN dbo.[TemplineNodes] AS points ON lines.[ogr_geometry].STIntersection(points.[ogr_geometry]).STDistance(points.[ogr_geometry]) < 1 WHERE (lines.[linenum] <> points.[linenum]) END
Is there anyway i can speed up the query?
I also have a clustered primary key. The execution plan shows that a filter takes up 36% of the cost and the insert takes up 64%
I'm using SQL Server 2008 (SQL server Management studio 10.50.1600.1)