none
SQL Azure Spatial Index performance

    Question

  • 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) 

    Friday, November 18, 2011 4:49 PM

All replies

  • 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?

    Monday, November 21, 2011 8:01 AM
  • 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!

    Tuesday, November 22, 2011 3:14 PM
  • 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
    Friday, November 25, 2011 3:31 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

     


    Ed Katibah Spatial Ed Blog
    • Edited by Spatial Ed Saturday, November 26, 2011 12:31 AM
    Friday, November 25, 2011 8:55 PM
  • 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

    Ed Katibah Spatial Ed Blog
    Friday, November 25, 2011 9:06 PM
  • 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

    Saturday, November 26, 2011 3:24 AM
  • 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 take a peek?
    -Joseph
    Sunday, November 27, 2011 12:28 AM
  • 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

     


    Ed Katibah Spatial Ed Blog
    Sunday, November 27, 2011 3:34 AM
  • *** 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_OBJECT = 16


    none

    • Edited by GerryTsui Sunday, November 27, 2011 2:45 PM added sample query
    Sunday, November 27, 2011 2:29 PM
  • 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 this!



    • Edited by GerryTsui Friday, December 02, 2011 12:58 PM correct typos
    Friday, December 02, 2011 12:52 PM
  • 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


    mlamure
    Thursday, January 26, 2012 9:36 PM