Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query and without using SET FORCEPLAN
Solution 1
From here:
The following requirements must be met for a Nearest Neighbor query to use a spatial index:
- A spatial index must be present on one of the spatial columns and the STDistance() method must use that column in the WHERE and ORDER BY clauses.
- The TOP clause cannot contain a PERCENT statement.
- The WHERE clause must contain a STDistance() method.
- If there are multiple predicates in the WHERE clause then the predicate containing STDistance() method must be connected by an AND conjunction to the other predicates. The STDistance() method cannot be in an optional part of the WHERE clause.
- The first expression in the ORDER BY clause must use the STDistance() method.
- Sort order for the first STDistance() expression in the ORDER BY clause must be ASC.
- All the rows for which STDistance returns NULL must be filtered out.
So, this should work:
DECLARE @g geography;
declare @point nvarchar(50) =''
declare @i int =0,
@lat decimal(8,6) =0.0,
@long decimal(8,6) =0.0,
@start datetime = getdate()
set @lat =(select (0.9 -Rand()*1.8)*100)
set @long =(select (0.9 -Rand()*1.8)*100)
set @point = (select 'POINT('+CONVERT(varchar(10), @lat)+ ' '
+CONVERT(varchar(10), @long)+')')
SET @g = geography::STGeomFromText(@point, 4326);
SELECT TOP 1000
@lat,
@long,
@g.STDistance(st.[coord]) AS [DistanceFromPoint (in meters)]
, st.[coord]
, st.id
FROM Temp st with(index([SpatialIndex_1]))
WHERE @g.STDistance(st.[coord]) IS NOT NULL
ORDER BY @g.STDistance(st.[coord]) asc
You can check that it is using the spacial index even the WITH INDEX
hint is removed.
Solution 2
In my case, i moved the database from one db to another and i forgot about the db Indexes migration.
create the indexes in the new db and solved the problem
Matas Vaitkevicius
In Javascript Let me take a selfie... var self = this; #SOreadytohelp Like my answer, here's link for donations. https://www.paypal.me/Vaitkevicius
Updated on July 09, 2022Comments
-
Matas Vaitkevicius almost 2 years
I am running following:
DECLARE @g geography; declare @point nvarchar(50) ='' declare @i int =0, @lat decimal(8,6) =0.0, @long decimal(8,6) =0.0, @start datetime = getdate() set @lat =(select (0.9 -Rand()*1.8)*100) set @long =(select (0.9 -Rand()*1.8)*100) set @point = (select 'POINT('+CONVERT(varchar(10), @lat)+ ' ' +CONVERT(varchar(10), @long)+')') SET @g = geography::STGeomFromText(@point, 4326); SELECT TOP 1000 @lat, @long, @g.STDistance(st.[coord]) AS [DistanceFromPoint (in meters)] , st.[coord] , st.id FROM Temp st with(index([SpatialIndex_1]))
this query performed poorly because it does not use spacial index, so I added
with(index([SpatialIndex_1]))
to force it.geography index looks following:
CREATE SPATIAL INDEX [SpatialIndex_1] ON [dbo].Temp ( [coord] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = LOW,LEVEL_2 = MEDIUM,LEVEL_3 = LOW,LEVEL_4 = LOW), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
Now it gives me error message
Msg 8622, Level 16, State 1, Line 15 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
I can read and understand that it tells me to remove hint, question is why does it succeed at compilation but fails on run-time? Is there something wrong with my index?
What do I need to change for SQL to start using Spatial index?
to generate some data you could use following script.
CREATE TABLE dbo.Temp ( Id int NOT NULL IDENTITY (1, 1), Coord geography NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE dbo.Temp ADD CONSTRAINT PK_Temp PRIMARY KEY CLUSTERED ( Id ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO declare @i int =0 declare @lat decimal(8,6) =0.0 declare @long decimal(8,6) =0.0 while (@i < 47000) begin set @lat =(select (0.9 -Rand()*1.8)*100) set @long =(select (0.9 -Rand()*1.8)*100) insert into Temp select geography::Point(@lat, @long,4326) set @i =@i+1 end go CREATE SPATIAL INDEX [SpatialIndex_1] ON [dbo].Temp ( [coord] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = LOW,LEVEL_2 = MEDIUM,LEVEL_3 = LOW,LEVEL_4 = LOW), CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY] GO