This morning I installed the latest SQL Server 2008 beta on my laptop, just to have a quick look at some of the spatial support. One of the sites I work on has a lot of GPS data, growing all of the time, and performance is something we are continuously working on. So, on an underpowered laptop, with slow discs, I did the following:
- Created a new column (Location) of type geography, alongside the existing Longitude and Latitude (both float) columns.
- Update the new Location column, creating a new Point object from the existing coordinates
- Ran queries to compare our existing distance between points function with the STDistance method of the geography type.
The results were interesting. Over a rowset of 372471 rows:
| | Rows returned | Time |
| Existing code, custom function | 205 | 28 seconds |
STDistance,
no spatial index | 204 | 1 minute |
STDistance,
spatial index | 204 | 1 second |
Really interesting. STDistance is twice as slow as our custom function without the index, but way faster once the spatial index is available. There are many other spatial methods that are going to open up huge possibilities for our application as well as improving performance. I can't wait to have more of a play.