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.
--[SQL Server 2008 R2 10:50:16170.0 (X64)] 00:04 min:sec
FROM ZipCodes z
ORDERBY 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/).
Spatial Program Manager
SQL Server/SQL Azure
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 ?
Edited byGerryTsuiSunday, November 27, 2011 2:31 PM