Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Questions regarding Spatial Data performance


Questions regarding Spatial Data performance

Author
Message
razzgor
razzgor
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 92
Hi all, I was wondering if anyone could chime in with random tips or thoughts on what I'm doing.

So I've implemented a GEO/IP table that includes geographic data mapped to IP ranges and those range bounds converted into an integer. In addition, I've created a geometry column that converts each IP range into a LINESTRING whose bounds are determined by the converted IP start and end points.

There is a clustered PK on an auto inc ID column and a spatial index on the geometry column.

My query to match fact table data to geo data uses a LEFT JOIN on the geo table using:
<geo_column>.STcontains( <fact IP converted to a geometric point> ) = 1

Initially I tested a TOP 100,000 rows of the fact table to benchmark speed and I got back about 6.2 minutes for the query to come back.

Integrated into the actual report it was intended for however the query simply never comes back, even when the # of fact rows being used as the base is only ~29,000 rows.

Anyone have any thoughts or tips on optimizing the performance of STContains/Spatial Indexes?

I had high hopes for this method of IP range matching but it seems like I am stuck in a rut now.
Ville-Pekka Vahteala
Ville-Pekka Vahteala
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 3037
Actual execution plan would have helped a lot, but here is my shot in the dark.
Spatial indexes work automatically only in enterprise and development editions, so if you have standard you need to use hint.
Second guess is that bounding box of spatial index is incorrect.

If those two are fine. Please post execution plan and some sample data.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8339 Visits: 11583
I am not an expert on Spatial data, but I do know a few things:

* The reply by Ville-Pekka is incorrect. I think he is confusing two features. Indexed views is the only feature that works automatically in enterprise but needs hints in lower editions; all other features either work automatically on all editions, or don't work at all on lower features.

* Are you running on SQL Server 2008? If so, check SELECT @@VERSION to see what, if any, service pack you have installed. If you are on RTM, that might be the cause - there were some huge imprevements to the costing of spatial queries that solved a lot of issues with spatial indexes not being used in SP1. See http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/.

* Other things that might help are simplfying the query (break it down in subqueries if needed), and executing it in such a way that the optimizer "knows" the parameter value. You may also need to add a recompile hint, to prevent parameter sniffing issues.

I do agree with Ville-Pekka that posting the execution plan would help. And the full query or stored procedure.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Ville-Pekka Vahteala
Ville-Pekka Vahteala
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 3037
Hugo Kornelis (11/3/2013)


* The reply by Ville-Pekka is incorrect. I think he is confusing two features. Indexed views is the only feature that works automatically in enterprise but needs hints in lower editions; all other features either work automatically on all editions, or don't work at all on lower features.


No I did not confuse. I just did not know this and made a false assumption. My empirical studies has just shown that standard and express editions rather choose some other index than spatial if there is an option. My test can be bad because I know for sure that those queries are not near optimal where this has happened.

Thank you Hugo for correction.
razzgor
razzgor
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 92
OK so the query looks as follows:

 
SELECT
<fact table attributes>
,Tr.Country
,tr.city
,tr.Region
,Tr.dmacode

FROM #fact_table ip
LEFT JOIN GEO2IP tr ON geo_range.STWithin(geometry::STGeomFromText('POINT('+CONVERT(varchar(20),ip.IP_Converted)+' 0)',0)) = 1




Here's the relatively simple DEP:

I admit DEP analysis is not a strong point of mine yet-- I know Table Scans are usually bad, but if the cost of it is 0% doesn't that mean it wouldn't be causing much of a slow down? Looking at this DEP, it seems to me that STContains really just isn't a very good function performance wise.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47376 Visits: 44395
A picture of the plan is fairly useless. Can you save the plan and attach the resultant file to your post?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


razzgor
razzgor
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 92
I'm hesitant to since this is my company's data. Any advice to offer without me having to upload specific details of the underlying structure? Things to look out for etc..
Ville-Pekka Vahteala
Ville-Pekka Vahteala
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 3037
razzgor (11/3/2013)
I'm hesitant to since this is my company's data. Any advice to offer without me having to upload specific details of the underlying structure? Things to look out for etc..


You can anonymous company data with
http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp#features.

In picture optimizer is not using spatial index even though it could.
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
Hugo Kornelis (11/3/2013)

* Are you running on SQL Server 2008? If so, check SELECT @@VERSION to see what, if any, service pack you have installed. If you are on RTM, that might be the cause - there were some huge imprevements to the costing of spatial queries that solved a lot of issues with spatial indexes not being used in SP1. See http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/.

Definitely make sure you are not running 2008 RTM, otherwise hinting is really the only sure way to go and that can also cause issues.

Ville-Pekka Vahteala
Second guess is that bounding box of spatial index is incorrect.

Agreed, when creating the index make your bounding box as tight as possible.

Are you using the same SRIDs between you Geometries? In your query you build the point using SRID 0. Is the SRID of the geometries in GEO2IP also 0?
Assuming you having polygons in GEO2IP, you will want to use STContains or STIntersects.
With the LEFT OUTER JOIN on the query, the spatial comparison is rather redundant. Try an INNER JOIN.

If you could post the create script for your spatial index and the DDL for the GEO2IP table that would be helpful. Approximately how many records does that have?

Are the coordinates Lat/Lon's or projected?
razzgor
razzgor
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 92
mickyT (11/3/2013)
Hugo Kornelis (11/3/2013)

* Are you running on SQL Server 2008? If so, check SELECT @@VERSION to see what, if any, service pack you have installed. If you are on RTM, that might be the cause - there were some huge imprevements to the costing of spatial queries that solved a lot of issues with spatial indexes not being used in SP1. See http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/.

Definitely make sure you are not running 2008 RTM, otherwise hinting is really the only sure way to go and that can also cause issues.

Ville-Pekka Vahteala
Second guess is that bounding box of spatial index is incorrect.

Agreed, when creating the index make your bounding box as tight as possible.

Are you using the same SRIDs between you Geometries? In your query you build the point using SRID 0. Is the SRID of the geometries in GEO2IP also 0?
Assuming you having polygons in GEO2IP, you will want to use STContains or STIntersects.
With the LEFT OUTER JOIN on the query, the spatial comparison is rather redundant. Try an INNER JOIN.

If you could post the create script for your spatial index and the DDL for the GEO2IP table that would be helpful. Approximately how many records does that have?

Are the coordinates Lat/Lon's or projected?




1) SQL Server 08 R2 SP2

2) The bounding box is set as the MIN() and MAX() of the integer conversion of the start and end IP addresses. I don't know how you could improve this.

3)The SRIDs match because in my unit testing expected results were indeed brought back correctly.

4)The LEFT JOIN is necessary because GEO data mapped to a single IP address is not housed in the same table as "true" ranges.

5)The GEO 2 IP table features somewhere in the neighborhood of 3 million rows

6)I'm not sure I understand the last question about coordinates, could you please clarify?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search