0

I have a B-level SQL Azure DB, querying mostly using STIntersect based on minimal polygons (5 points), and had horrible performance issues. Checking for intersection on a 50K rows table took nearly a minute (spatial indices being in place and hinted in the query).

Eventually, I set up a local test DB, ran the same query and had the result in less than a second.

I was using the lowest SQL Azure tier, so I upgraded to S1, and then to S2, with remarkably better results in S2, although it still takes over 3 secs). The problem is: When I run a single query, this already peaks my S2 instance to 60% DTU utilization - which makes me guess that this isn't fit for production at all - and that's a lot of money for very little performance.

https://stackoverflow.com/a/27974549 hinted a year ago that Azure SQL wasn't fit for spatial stuff because Geography as a CLR type kills the CPU. What is your experience / recommendation with the current V12 DBs? Do I really have to fall back to (worse performing) manual lat/long calculations when deploying to Azure?

Thanks, Philipp

--

Query:

DECLARE @g geography;
DECLARE @bfr geography;

set @g = geography::STPointFromText('POINT(8.7125158 47.5041406)', 4326)
set @bfr = @g.STBuffer(575000).Reduce(57500)

SELECT count(*)
FROM    Locations st with(index(SPATIAL_LocationArea))
WHERE @bfr.STIntersects(st.LocationArea) = 1

Table:

CREATE TABLE [dbo].[Locations] (
    [Id]           INT               IDENTITY (1, 1) NOT NULL,
    [Location]     [sys].[geography] NOT NULL,
    [LocationArea] [sys].[geography] NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
CREATE SPATIAL INDEX [SPATIAL_Location]
    ON [dbo].[Locations] ([Location]);

GO
CREATE SPATIAL INDEX [SPATIAL_LocationArea]
    ON [dbo].[Locations] ([LocationArea]);
Community
  • 1
  • 1
Philipp Sumi
  • 917
  • 8
  • 20

1 Answers1

1

Is local DB SQL 2016? In SQL2016 some parts of spatial are rewritten from CLR (that might be time consuming) to native code, but I'm not sure it the same improvement pushed in Azure SQL Db.

Another guess might be that you have better local server than S1/S2 performance tiers. S1/S2 are very close to basic tier, and the basic is more something that you can use to play with DB. Maybe you could you try to quickly change performance tiers and see what is minimal service tier for your spatial queries.

Jovan MSFT
  • 13,232
  • 4
  • 40
  • 55
  • Hi Jovan - I'm currently travelling, so I can't verify. Since my laptop broke down, I was working on my six year old workstation with VS 2015 installed, so I guess not. Also, when you say S1 or S2 are like the basic tier - S2 already costs serious money, and I'm getting performance issues with a _single_ query, so that shouldn't be the issue IMO. Thanks for your advice! – Philipp Sumi Sep 13 '16 at 08:44
  • 1
    I am using the latest of SQL on Azure, so yes. However, I ended up removing the spatial queries in favorite of just storing Lat/Lon coordinates and querying those. That got rid of all my performance issues. I'm not too happy about it, but whatever gets the job done. – Philipp Sumi Dec 22 '16 at 21:30