May 1, 2012 at 3:16 am
Hi All,
i'm currently playing around a bit more with spatial data and spatial indexing but with a more representative sample of data.
The question i have is, why is the "nearest neighbour" example using spatial data (geography type) far far far slower (both reads and duration) than using the "old skool" sqrt/power function. Here are the examples i'm comparing:
Old Skool:
DECLARE @RadMiles float
, @Lat_in float
, @Log_in float;
SET @RadMiles = 50; -- radius of area searched
-- get coordinates of entered postcode
SELECT @Lat_in = latitude,
@Log_in = longitude
FROM Postcode
where value = 'xx88 8xx'; --postcode goes here
SELECT TOP(3) [Value]
SQRT(POWER(Latitude - @Lat_in, 2) + POWER(Longitude - @Log_in, 2)) as Distance
FROM dbo.Postcode
WHERE (SQRT(POWER(Latitude - @Lat_in, 2) + POWER(Longitude - @Log_in, 2)) < @RadMiles)
ORDER BY (SQRT(POWER(Latitude - @Lat_in, 2) + POWER(Longitude - @Log_in, 2)));
results of statistics io:
Table 'Postcode'. Scan count 1, logical reads 15337, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Spatial Data:
DECLARE @g geography
, @RadMiles int
, @MaxMeters float(53); --CT:- float(53) to remove the implicit_conversion
SET @RadMiles = 50; -- radius of area searched
SET @MaxMeters = @RadMiles *1609.344;
SELECT @g = GEO
FROM [Postcode]
WHERE value = 'xx88 8xx'; --postcode goes here
SELECT TOP(3) value
FROM dbo.postcode
WHERE GEO.STDistance(@g) < @MaxMeters
--and GEO.STDistance(@g) > 0
ORDER BY GEO.STDistance(@g);
results of statistics io:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'extended_index_149575571_384005'. Scan count 1037, logical reads 4767, physical reads 0, read-ahead reads 23, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Postcode'. Scan count 0, logical reads 318043, physical reads 0, read-ahead reads 109, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I've tried various spatial indexes using various Grid levels and cells per object but it doesn't seem to make any difference. Am i missing something simple here?
I've also attached the two sqlplans........
thanks in advance.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
May 1, 2012 at 4:55 am
You just posted this in the 7, 2000 forum. Most people reading here are unlikely to be doing spatial queries since they don't exist until 2008.
The query looks ok, so I'd focus on the data and your index density. Here's a blog post I wrote about it. I found that determining if you're looking at primarily inclusive or exclusive filtering changed the useful density settings of the index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2012 at 4:59 am
I don't have an example query handy, but you can also try using the BUFFER and determining if points are within the buffer. That works well for some types of tests.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2012 at 5:51 am
thanks Grant, i'll take a look at the links! 😀
thanks
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
May 1, 2012 at 8:40 am
I've replied to your post at http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/28729874-1199-4dcb-a39b-bd63e504d93d
May 2, 2012 at 8:35 am
Nearest stuff just plain SUCKS with sql server spatial. Search web for isaac kunen spatial. He has a slick power-of-2 query for finding stuff nearest to you. I have done some stuff to make that stuff even faster, but it goes way beyond a forum thread.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 2, 2012 at 9:04 am
Hi Kevin,
thanks for the reply. I did indeed find the stuff isaac has done and played around with it a bit last night and currently i've got the spatial query down from ~3500ms to ~1700ms but I know i can reduce it further. The sqrt example query i have is around ~3500ms as well so i've definitely got an improvement so far.
Isaac's idea is very clever and glad i found it as I'd never of thought of going down the route he did.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply