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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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