I've recently started seeing this weird behavior whereby doing a spatial search results in a degredated experience as I *decrease* my radius. I dropped and rebuilt my spatial index last night but the same weird result.

Any help would be appreciated.

Note that I started seeing this last night.

Background:

Table size = ~360K rows

Table = {ID, Location (geography)}

E.g. Doing a query with Radius=20000 takes 1 sec but decreasing Radius=1000 is still executing after 3 minutes.

Query:

DECLARE @x geography
SET @x = geography::Point(47.5302778, -122.0313889, 4326)

SELECT TOP (25) L.RID, L.Location.STDistance(@x) AS DIST
FROM Location L
WHERE L.Location.STDistance(@x) <= @Radius

Index: 

Right-clicking index gives me the following (I realize PAD-iNDEX isn't supported in SQL Azure, etc. but just copying verbatim what SQL Management Studio gives me)

 CREATE SPATIAL INDEX [SPATIAL_Search_Location] ON [dbo].[Search]
(
 [Location]
)USING  GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 

Over the last few days I have noticed that my geography query has gone from sub 3 seconds to 1m30s

I think something must be going on in sql azure. 

I have tried droping and recreating the index but have not been able to get the performance back.

 

The same query over the same data on my local sql server is sub one second.

 

Is anyone else experiencing poor performance from geography queries?

Yes, I am having the same problems.  We had a webapp built for us utilizing spatial searches and Bing Maps -- now we can't even perform searches as we used to without the application timing out.

I am not sure what's happening, but it has totally went down the tubes lately!

I have exactly the same problem. the preformance was good and now its very slow.

The problem i see is with the STDistance when it been used in a "where" any solution for that?

• Edited by Noam Berda Friday, November 25, 2011 3:33 AM

I have exactly the same problem. the preformance was good and now its very slow.

The problem i see is with the STDistance when it been used in a "where" any solution for that?

• Edited by Noam Berda Friday, November 25, 2011 3:33 AM

I have exactly the same problem. the preformance was good and now its very slow.

The problem i see is with the STDistance when it been used in a "where" any solution for that?

• Edited by Noam Berda Friday, November 25, 2011 3:33 AM

I have exactly the same problem. the preformance was good and now its very slow.

The problem i see is with the STDistance when it been used in a "where" any solution for that?

• Edited by Noam Berda Friday, November 25, 2011 3:33 AM

Starting with the last two Service Releases, SQL Azure has begun to implement the new spatial features introduced in SQL Server 2012 (“Denali”). A post detailing these new features and their status can be found at:

http://social.technet.microsoft.com/wiki/contents/articles/new-spatial-features-in-the-sql-azure-q2-2011-service-release.aspx

A post discussing the new spatial features introduced in “Denali” can be found at:

http://social.technet.microsoft.com/wiki/contents/articles/4136.aspx

In particular please pay attention to the section on nearest neighbor query plans.

Towards this direction, I’ve taken the example introduced by @bmwlexusman in this thread and modified it, as appropriate, to point to some existing data that I already loaded in SQL Azure:

Background:

Table size: 1,892,290 rows

Table = Geonames

Index:

CREATE SPATIAL INDEX geog_hhhh_16_sidx

   ON Geonames(GEOG)USING  GEOGRAPHY_GRID

   WITH (

     GRIDS =(LEVEL_1 = MEDIUM,

             LEVEL_2 = MEDIUM,

             LEVEL_3 = MEDIUM,

             LEVEL_4 = MEDIUM),

       CELLS_PER_OBJECT = 16);

Query(s):

SELECT @@VERSION

GO

--Results: Microsoft SQL Azure (RTM) - 11.0.1756.35  

--           Nov  9 2011 15:50:14  

--           Copyright (c) Microsoft Corporation

 

SELECT COUNT(*) FROM Geonames;

GO

--Results: 1,892,290 rows

 

--==============================================================

-- Query Not Using Spatial Index

--==============================================================

DECLARE @x geography

  SET @x = geography::Point(47.5302778, -122.0313889, 4326)

DECLARE @Radius FLOAT = 20000

 

SELECT TOP (25) geonameid AS ID, L.Geog.STDistance(@x) AS DIST

  FROM Geonames L

    WHERE L.Geog.STDistance(@x) <= @Radius;

GO

--Returns: QUERY MANUALLY CANCELLED AFTER 6 MINUTES WITH NO RESULT

 

--==============================================================

-- Query Not Using Spatial Index

--==============================================================

DECLARE @x geography

  SET @x = geography::Point(47.5302778, -122.0313889, 4326)

DECLARE @Radius FLOAT = 1000

 

SELECT TOP (25) geonameid AS ID, L.Geog.STDistance(@x) AS DIST

  FROM Geonames L

    WHERE L.Geog.STDistance(@x) <= @Radius;

GO

--Returns: QUERY CANCELLED STOPPED AFTER 2 MINUTES WITH NO RESULT

 

--==============================================================

-- Query Using Denali Nearest Neighbor Query Pattern

--   Large Search Radius

--==============================================================

DECLARE @x geography

  SET @x = geography::Point(47.5302778, -122.0313889, 4326)

DECLARE @Radius FLOAT = 20000

 

SELECT TOP(25) geonameid AS ID, L.Geog.STDistance(@x) AS DIST

  FROM Geonames L

    WHERE L.Geog.STDistance(@x) < @Radius

  ORDER BY L.Geog.STDistance(@x);

GO

--Results: 25 rows in 4 seconds

 

--==============================================================

-- Query Using Denali Nearest Neighbor Query Pattern

--   Small Search Radius

--==============================================================

DECLARE @x geography

 SET @x = geography::Point(47.5302778, -122.0313889, 4326)

DECLARE @Radius FLOAT = 1000

 

SELECT TOP(25) geonameid AS ID, L.Geog.STDistance(@x) AS DIST

  FROM Geonames L

    WHERE L.Geog.STDistance(@x) < @Radius

  ORDER BY L.Geog.STDistance(@x);

GO

--Results: 7 rows in 0 seconds

 

--==============================================================

-- Query Using Denali Nearest Neighbor Query Pattern

--   Generic (no search radius)

--==============================================================

DECLARE @x geography

 SET @x = geography::Point(47.5302778, -122.0313889, 4326)

 

SELECT TOP(25) geonameid AS ID, L.Geog.STDistance(@x) AS DIST

  FROM Geonames L

    WHERE L.Geog.STDistance(@x) IS NOT NULL

  ORDER BY L.Geog.STDistance(@x);

GO

--Results: 25 rows in 4 seconds

 

 

I’ll be interested in how the information presented above is able to resolve (or not) the issues which the participants, in this thread, have presented.

Thanks,

-Ed

Ed Katibah
Spatial Program Manager
SQL Server/SQL Azure
Microsoft Corporation

 

I neglected to mention in my last post that I was able to get @bmwlexusman’s original queries to perform by specifying an index hint:

 

--==============================================================

-- Query Originally Not Using Spatial Index Using Index Hint

--==============================================================

DECLARE @x geography

  SET @x = geography::Point(47.5302778, -122.0313889, 4326)

DECLARE @Radius FLOAT = 20000

 

SELECT TOP (25) geonameid AS ID, L.Geog.STDistance(@x) AS DIST

  FROM Geonames L WITH(INDEX(geog_hhhh_16_sidx))

    WHERE L.Geog.STDistance(@x) <= @Radius;

GO

--Returns: 25 rows in 0 seconds

 

--==============================================================

-- Query Originally Not Using Spatial Index Using Index Hint

--==============================================================

DECLARE @x geography

  SET @x = geography::Point(47.5302778, -122.0313889, 4326)

DECLARE @Radius FLOAT = 1000

 

SELECT TOP (25) geonameid AS ID, L.Geog.STDistance(@x) AS DIST

  FROM Geonames L WITH(INDEX(geog_hhhh_16_sidx))

    WHERE L.Geog.STDistance(@x) <= @Radius;

GO

--Returns: 7 rows in 0 seconds

We will take a look at what underlying changes have caused the spatial index to not be used in the latest SQL Azure Service Release under these circumstances.

Thanks,

-Ed

Ed Katibah
Spatial Program Manager
SQL Server/SQL Azure
Microsoft Corporation

Hi Ed,

I have another related issue that i submitted on the following thread, could you please look at it as well.

http://social.msdn.microsoft.com/Forums/en-SG/sqlspatial/thread/3d8758ca-98e3-4b76-9b26-f1389aefa545

Thanks,

Noam

Ed, when creating a spatial index in SQL Azure on about 44k rows it takes a VERY long time (so long I've never not cancelled). I've created a separate thread for this (but my REAL problem is essentially the same thing seen on this thread). http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/7a30eee1-015c-4c71-b932-15bfb51ca43a Care to ta

Hi Joseph –

I responded with the following post in response to http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/7a30eee1-015c-4c71-b932-15bfb51ca43a but I'll repeat here for completeness.

 There is definitely an issue with spatial index creation time in SQL Azure.  There was a 20 minute difference in time to create the same index on the same data between SQL Azure and SQL Server 2008 R2.  And I used my laptop to create the spatial index on SQL Server 2008 R2 no less.

 

SELECT COUNT(*) FROM ZIPCodes

 --Results: 49,146 rows

 

 CREATE SPATIAL INDEX geog_llmh_64_sidx

   ON ZIPcodes(geog)

   USING GEOGRAPHY_GRID

   WITH (

     GRIDS = (LOW, LOW, MEDIUM, HIGH ),

     CELLS_PER_OBJECT = 64

   );

--Results: [SQL Azure 11.01.1756.35] 20:04 min:sec

--         [SQL Server 2008 R2 10:50:16170.0 (X64)] 00:04 min:sec

 

DECLARE @point GEOGRAPHY = 'Point(-122 38)'

SELECT TOP(1) z.Name

 FROM ZipCodes z

   WHERE z.Geog.STDistance(@point) IS NOT NULL

 ORDER BY z.Geog.STDistance(@point)
--Results: [SQL Azure 11.01.1756.35] Name=94521 in 00:00 min:sec

The good news is that distance queries perform well in SQL Azure when a spatial index is present and recognized.

While we are getting this issue sorted out, I’d recommend that you create a staging database in SQL Server 2008 R2 or SQL Server 2012 CTP3.  Create and test your spatial indexes in the staging database.  Then you can move the table and associated spatial indexes (etc.) to SQL Azure with a tool like the SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/).

Thanks,

-Ed

Ed Katibah
Spatial Program Manager
SQL Server/SQL Azure
Microsoft Corporation

 

*** I also cross posted to the other thread mentioned above, http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/7a30eee1-015c-4c71-b932-15bfb51ca43a

 because I see two issues 1) spatial indexing speed 2) spatial query speed

We have a table with 3.6 million rows, the geography are all points (lat/lon in epsg:4326), which cover the entire USA ( including AK,HI). The spatial index creation took over 16 HOURS.

- the spatial query is still slow, ever since the SQLAzure release a couple of weeks ago. ( I have tried both with and without the index hint.

- ST_Distance queries that used to take 1) <1 seconds, now take > 4 seconds [for a quarter mile radius search) 2) <3 seconds now takes over 20 seconds ( for a one mile radius search)

- I also tried SQLAzureMW migrate the entire database from a SQLserver 2012 RC0

  - if I leave the Index in place during the BCP, the migration took over 12 hours,

Something is seriously difference(wrong), and is affecting my expectation of the SQLAzure performance. The App that we built has been performing fine since March, is now unusablly SLOW. We will have to migrate this to on-permise.

Can we opt to run this in a older version of SQL Azure ?

GerryT

 

 

This took 5 seconds with or without the Hint: ( It used to take less than half a second)

DECLARE @pnt GEOGRAPHY
DECLARE @Radius FLOAT = 660  * 0.3048
SET @pnt = geography::Point(47.606374, -122.332219, 4326)
select
 t.Shape.Lat as _lat ,t.shape.Long as _Long,
 t.Shape.STDistance(@pnt) / 0.3048  as DIST,
 t.* 
FROM sites t  --WITH(INDEX(sdx_Spatial_Sites))
WHERE  
 (t.Shape.STDistance(@pnt) <= @Radius )

 

Changing the Radius from 660 to 5280 : 18 seconds (it used to take about 2 seconds)

 

The Spatial index parameter is :

USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJEC

What a surprise. Started last evening, (December 1) without doing anything ( I have given hopes after 5 days of looking for a workaround), the spatial index is back to the acceptable performance level of before the November release.

I hope Microsoft not going to give me more surprises after

Hi everyone, 

I got the same pb. I run a simple query using STDIstance:

SQL 2008 R2: <1s

SQL AZure > 45s

I followed spatial ED solution by adding USING GEOGRAPHY_GRID. Everything was resolved.

Since a few weeks, performances become less and less acceptable

SQL 2008 R2: <1s

SQL AZure > 4s

Do you have any idea of this behavior ?

Thanks

Is there any resolution on this?  I am having the same problem.  Using STDistance on premise returns results in less that 1s.  However, the same data on SQL Azure on a Standard S1 database takes 10s.

Any advice or work arounds?