none
Creating spatial indexes is slow

    Question

  • Background:

    I'm having a very similar issue to what is being talked about at http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/77618325-ba1a-4af8-b5cb-171f13e09794, basically STDistance performance all of a sudden just fell off a cliff (from <1 seconds to 30+ seconds)

    I have a pretty simple table that has ZipCodes. Defined as: ZipCodes( ZipCode[varchar(5)], Point[geography] )

    The table has a little under 44,000 records, so not large at all.

     

    Problem:

    When trying to create a spatial index "CREATE SPATIAL INDEX IX_ZipCodes_Point  ON ZipCodes(Point)" it just never appears to work. I've waited 8+ minutes in some cases and no luck.

    Long story short, I'm trying to do something like the following:

    DECLARE @point geography
    SET @point = (this is a value essentially passed in)

     

    SELECT TOP(1) z.PostalCode
    FROM ZipCodes z
    WHERE z.Point.STDistance(@point) IS NOT NULL
    ORDER BY z.Point.STDistance(@point)

    But this is taking WAAAY longer now and I was hoping a SPATIAL INDEX would help but I can't even create one to try :(

     

     


    -Joseph

    Sunday, November 27, 2011 12:23 AM

Answers

  • Hi Joseph –

     

    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
    • Marked as answer by JosephSeattle Sunday, November 27, 2011 5:38 AM
    Sunday, November 27, 2011 3:30 AM

All replies

  • Hi Joseph –

     

    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
    • Marked as answer by JosephSeattle Sunday, November 27, 2011 5:38 AM
    Sunday, November 27, 2011 3:30 AM
  • Mine came in at 11:37, but yea, the answer is 'wait longer' :) thanks a lot for the help Ed, especially this late on a Saturday.

     

    I guess now I have a quick followup. Did I miss something here  related to this type of functionality (I'm assuming there was some sort of announcement).

    This is one of those few times where I'm like 'I wished I ran this on-premise so I didn't have to update things', though fortunately in this case my app isn't quite live yet....


    -Joseph
    Sunday, November 27, 2011 5:42 AM
  • *** I also cross posted to spatial performance thread, http://social.msdn.microsoft.com/Forums/en-CA/ssdsgetstarted/thread/77618325-ba1a-4af8-b5cb-171f13e09794

    because I am seeing  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). It took over 16 HOURS.

    - the spatial query is still slow, ever since the SQLAzure release a couple of weeks ago.

       - 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 40 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

     

     


    none
    • Edited by GerryTsui Sunday, November 27, 2011 2:31 PM
    Sunday, November 27, 2011 2:27 PM
  • As I mentioned in the other post. The spatial search performance is back to what it was before the Nov service release.

    I recreate the index with HHHH successfully and it took only 17 minute and 15 seconds. ( In the last 5 days, it consistently took 16 hours for HHHH, HHMM).

     

    I hope Microsoft not going to give me more performance surprises.

     


    none
    Friday, December 02, 2011 1:25 PM