Improving the performance of STEquals

Question

We have a table containing all polygons (geometry/geography) of the objects in our application. This is because most polygons need to have a 'geometry' (RDNew) and a 'geography' (WGS84) version of the same polygon AND need to be reused (most polygons are used in multiple objects).

To make sure the correct reference is added to an object (based on the polygon which needs to be associated with that object), we first seek the polygon in the polygon table using the query:

declare @__geometry_0 sys.geometry -- filled by a polygon value

SELECT TOP(1) [g].[Id], [g].[AangemaaktOp], [g].[OorspronkelijkeCoordinaatSysteem], [g].[RDNewGeo], [g].[RDNewOppervlakte], [g].[WGS84Geo], [g].[WGS84Oppervlakte]
FROM [GeoData] AS [g]
WHERE [g].[RDNewGeo].STEquals(@__geometry_0) = CAST(1 AS bit)
ORDER BY [g].[Id]

We are running MS SQL Server as an Azure 'service'. The problem we are running into is that these search actions take about 2 seconds each (at this point there are 250.000+ polygon records in the table).

So we want to reduce the time it takes to locate a (one) specific polygon.

At first, we noticed there was no spatial index. so we added one to see how much it would speed up the search.

CREATE SPATIAL INDEX IX_SPATIAL_GeoData_RDNewGeo ON dbo.GeoData(RDNewGeo)
WITH( BOUNDING_BOX  = ( xmin  = 0.0, ymin  = 300000.0, xmax  = 280000.0, ymax  = 625000.0) )

However, when running the above query, the index is still not used (when viewing the actual execution plan).

So second, we modified the query NOT to use the top (1) statement, and now the spatial index is used (according to the execution plan). However, now the search is even slower! EDIT: after retesting, we didn't notice the slowdown anymore, but on the contrary a small performance gain (locally) => see the answer to this question for the complete explanation.

Any ideas on how to speed up the execution of a query that searches for one specific polygon (or are we just going the wrong way about this)?

Additional info:

SQL-server: Microsoft SQL Azure (RTM) - 12.0.2000.8

Create table statement

CREATE TABLE [dbo].[GeoData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AangemaaktOp] [datetime2](7) NOT NULL,
    [OorspronkelijkeCoordinaatSysteem] [smallint] NOT NULL,
    [RDNewGeo] [geometry] NOT NULL,
    [RDNewOppervlakte] [float] NOT NULL,
    [WGS84Geo] [geography] NOT NULL,
    [WGS84Oppervlakte] [float] NULL,
 CONSTRAINT [PK_GeoData] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sample polygon

POLYGON ((128864.429 449604.427, 128863.868 449605.748, 128863.48 449605.51, 128862.98 449605.33, 128862.47 449605.24, 128861.94 449605.25, 128861.43 449605.37, 128861.16 449605.49, 128860.11 449605.46, 128858.84 449605.43, 128856.44 449604.98, 128777.65 449582.27, 128692.42 449557.94, 128628.68 449540.22, 128547.49 449517.35, 128487.491 449500, 128443.71 449487.34, 128443.43 449487.36, 128443.22 449487.42, 128443.03 449487.51, 128442.85 449487.65, 128442.71 449487.81, 128442.6 449488, 128442.598 449488.006, 128433.38 449485.18, 128433.41 449484.9, 128433.39 449484.62, 128433.32 449484.35, 128433.19 449484.1, 128433.02 449483.87, 128432.81 449483.68, 128432.58 449483.54, 128427.179 449481.36, 128425.795 449480.801, 128427.467 449475.951, 128427.11 449473.74, 128427.51 449473.46, 128427.84 449473.11, 128428.12 449472.7, 128438.58 449441.28, 128438.98 449440.73, 128439.48 449440.25, 128440.07 449439.88, 128440.71 449439.62, 128441.4 449439.48, 128442.09 449439.48, 128442.79 449439.61, 128473.49 449448.58, 128474.623 449448.963, 128474.775 449448.997, 128474.931 449449.016, 128475.087 449449.018, 128475.243 449449.005, 128475.396 449448.975, 128475.546 449448.93, 128475.69 449448.87, 128475.828 449448.795, 128475.957 449448.707, 128476.076 449448.606, 128476.185 449448.494, 128476.281 449448.371, 128476.365 449448.239, 128476.434 449448.099, 128476.489 449447.952, 128476.507 449447.884, 128482.689 449449.572, 128482.612 449450.178, 128482.773 449450.367, 128483.043 449450.519, 128483.6 449450.72, 128483.72 449450.76, 128538 449466.25, 128587.57 449480.08, 128634.44 449494.08, 128655.143 449500, 128670.6 449504.42, 128708.1 449515.01, 128748.61 449526.58, 128778.5 449535.25, 128831.15 449550.08, 128866.91 449560.22, 128867.21 449560.24, 128867.51 449560.2, 128867.79 449560.09, 128868.05 449559.94, 128868.27 449559.74, 128868.45 449559.5, 128868.58 449559.229, 128875.66 449561.255, 128875.65 449561.67, 128875.72 449562.08, 128875.87 449562.47, 128876.03 449562.73, 128876.35 449563.92, 128876.42 449565.18, 128876.22 449566.43, 128865.22 449597.56, 128865.18 449597.95, 128865.21 449598.34, 128865.32 449598.71, 128865.5 449599.05, 128865.74 449599.35, 128866.03 449599.6, 128866.37 449599.79, 128866.395 449599.797, 128865.961 449600.819, 128864.429 449604.427))

Answer 1

I don't know what was wrong with the previous tests we did, but we reran the test several times again (with no top(1) and with the 'spatial index').

This time, we did not see the performance degradation (locally) anymore and even saw a slight performance gain!

So we made the adjustments to our code, to make sure the spatial index would be used and added the spatial index to our pre-production environment (which is hosted on Azure too) and saw some real improvements regarding the performance. Some tasks had an overall performed gain of 60% and we noticed the database usage (DTU/CPU) dropped significantly during querying.

Conclusion:

Add a spatial index and make sure NOT to use any top x statements, which will make MS-SQL NOT use your spatial index!

Addition regarding tests:

After noticing de performance did increase after adding the index, I sat back and evaluate the situation regarding the test. My conclusion was that used the wrong test from the start!

I wanted to test how much time it would take to find a (random) polygon in the table. But as the 'random' polygon I used the first polygon (in order of the clustered index) I could get from the table. So, when searching without the index, SQL-server used the clustered index to find the first occurrence of polygon and so find this first record!! And, well, that can't get any faster... ;)