We have a simple SQL Server table with geospatial data that looks like this:
CREATE TABLE [dbo].[Factors]( [Id] [int] IDENTITY(1,1) NOT NULL, [StateCode] [nvarchar](2) NOT NULL, [GeoLocation] [geography] NULL, [Factor] [decimal](18, 6) NOT NULL, CONSTRAINT [PK_dbo.Factors] PRIMARY KEY CLUSTERED ( [Id] ASC )
We have something like 100k+ rows in it right now, but that's expected to grow to millions.
We run queries on it that look like this:
declare @state nvarchar(2) = 'AL' declare @point geography = geography::STGeomFromText('POINT(-86.19146040 32.38225770)', 4326) select top 3 Lat, Lon, Factor, GeoLocation.STDistance(@point) as Distance from dbo.Factors where StateCode = @state and GeoLocation.STDistance(@point) is not null order by Distance
Here's the bit that's weird though. The data in that table is spotty: we've got it for the southern parts of a state, for instance, but not for the whole state. If the point we search for is within a few hundred meters of points we've got data for (for instance, from the southern part of the state), the query returns subsecond. But if it's, say, 100 kilometers from the nearest data point (for instance, if the target point is from the northern part of the state), then the query will take up to 3 minutes or so to return. In both instances, query plans indicate that they're starting with a scan of the geospatial index, so it's not the problem that sometimes happens, that SQL Server can't figure out it's supposed to use the index in question.
My assumption is that it has something to do with how the geospatial index is laid out.
CREATE SPATIAL INDEX IX_Factors_Spatial ON [dbo].[Factors] (GeoLocation) USING GEOGRAPHY_AUTO_GRID WITH ( 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);
But I don't know that I grok the details well enough to put my finger on the problem.
Any suggestions for how to approach troubleshooting this?