I'm working on moving some spatial searching capabilities from Postgres with PostGIS to SQL Server and I'm seeing some pretty terrible performance, even with indexes.
My data is around a million points, and I want to find out which of those points are within given shapes, so the query looks something like this:
DECLARE @Shape GEOMETRY = ...
SELECT * FROM PointsTable WHERE Point.STWithin(@Shape) = 1
If I select a fairly small shape, I can sometimes get sub-second times, but if my shape is fairly large (which they sometimes are), I can get times over 5 minutes. If I run the same searches in Postgres, they're always under a second (in fact, almost all are under 200 ms).
I've tried several different grid sizes on my indexes (all high, all medium, all low), different cells per object (16, 64, 256), and no matter what I do the times stay fairly constant. I'd like to try more combinations but I don't even know what direction to go. More cells per object? Less? Some strange combination of grid sizes?
I've looked at my query plans and they're always using the index, it's just not helping at all. I've even tried without the index, and it's not much worse.
Is there any advice anyone can give on this? Everything I can find suggests "we can't give you any advice on indexes, just try everything and maybe one will work", but with it taking 10 minutes to create an index, doing this blindly is a massive waste of time.
EDIT: I also posted this on a Microsoft forum. Here's some information they asked for on there:
The best working index I could get was this one:
CREATE SPATIAL INDEX MapTesting_Location_Medium_Medium_Medium_Medium_16_NDX
ON MapTesting (Location)
USING GEOMETRY_GRID
WITH (
BOUNDING_BOX = ( -- The extent of our data, data is cluste