I want to query spatial data for nearest neighbor. I am using this article and the following query works perfectly:
SELECT TOP 1 [Location].STDistance(@Location) FROM [DS1] WHERE [Location].STDistance(@Location) IS NOT NULL ORDER BY [Location].STDistance(@Location);
The issue is I need to calculate this distance for each
MSPID. I have try several things (using cross apply, creating separate function, etc) but nothing worked. So, I decided to create a loop which will calculate the distance for each
SELECT TOP 1 [Location].STDistance(@Location) FROM [DS1] WHERE [Location].STDistance(@Location) IS NOT NULL AND @CurrentMSPID = [MSPID] ORDER BY [Location].STDistance(@Location);
The problem is that the above statement does not use the index. If I replace the
@CurrentMSPID with a number the index is used, but when a variable is used, the clustered index is used instead the spatial one.
I have try a lot of options and the only one that works is:
OPTION (OPTIMIZE FOR ( @CurrentMSPID = 1001 ))
or when the value is hard-coded. I cannot leave it that way because in the future this
id could even not exists and it is sure that the number of rows which it matches will be changed.
[DS1] table has primary key on
[MSPID] column and spatial index on the
How can I help the engine to generate better execution plan and use the spatial index without using the
OPTIMIZE FOR option?
It seems when other data is extracted from the table (we are filtering by
MSPID now) the engine is not able to use the spatial index and performs clustered index seek instead.