Azure SQL - Spatial Peformance Issues

Question

I've recently set up an Azure SQL Database with intentions to build high-performance spatial applications.

Unfortunately when comparing Azure SQL to an on-prem server I'm getting very poor performance when executing geospatial queries like intersections of polygon boundaries.

Server Config:

On-Prem

• SQL Server 2022
• Xeon E5-1630 CPU // 64GB 2133mhz DDR4 RAM // Samsung 870 EVO SSD)

Azure SQL

• General Purpose - Serverless: Standard-series (Gen5) 80 vCore max, 80 vCore min
• Also tested the S3 100 DTU model. This not only didn't perform, but isn't feasible financially.

Dataset:

• [dbo].[AddressGeocodes] -> Lat, Long Points, stored as geometry.
• [dbo].[SA1_GDA2020] -> Multi-polygon geo-spatial boundaries, also in geometry

Replicated across systems (incl. clustered PK + spatial indexes with bounding boxes and auto-grid)

Query:

SELECT *
FROM [dbo].[AddressGeocodes] GEO
  INNER JOIN [dbo].[SA1_GDA2020] SA1 ON GEO.[geom].STIntersects(SA1.[geom]) = 1

The estimated and actual execution plans within SSMS are identical, recognising the two clustered PKs and the spatial index.

Results (after 60 seconds):

• On-prem -> 68,000 Records
• Azure 80 vCores -> 17,000 Records

Conclusion:

What I don't understand is within azure portal, the CPU usage is only 2% for the query.

Could anyone please help me understand how there is such a dramatic difference?

There are very limited resources for spatial performance in Azure.

Thanks heaps!

Answer 1

With S3 you only have two vCores available and max degree of parallelism is one (1), all your query plans are serialized. I guess your local SQL Server has more computing power.

If you already think S3 is expensive and you cannot afford to scale up and try what service tier better adjust to the performance you are expecting, then you can try saving Lat and Long Points as varchar and convert them to geography when needed.

SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] 
AS VARCHAR(20)) + ' ' + 
                CAST([Latitude] AS VARCHAR(20)) + ')', 4326)