Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Questions regarding Spatial Data performance Expand / Collapse
Author
Message
Posted Sunday, November 3, 2013 1:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:01 PM
Points: 7, Visits: 91
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.
Post #1510877
Posted Sunday, November 3, 2013 2:48 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:47 PM
Points: 99, Visits: 2,621
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.
Post #1510880
Posted Sunday, November 3, 2013 4:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 6,133, Visits: 8,396
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
Post #1510883
Posted Sunday, November 3, 2013 6:37 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:47 PM
Points: 99, Visits: 2,621
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.
Post #1510888
Posted Sunday, November 3, 2013 11:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:01 PM
Points: 7, Visits: 91
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.
Post #1510908
Posted Sunday, November 3, 2013 11:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
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

Post #1510910
Posted Sunday, November 3, 2013 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:01 PM
Points: 7, Visits: 91
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..
Post #1510911
Posted Sunday, November 3, 2013 11:46 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:47 PM
Points: 99, Visits: 2,621
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.
Post #1510916
Posted Sunday, November 3, 2013 11:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
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?


Post #1510918
Posted Sunday, November 3, 2013 12:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:01 PM
Points: 7, Visits: 91
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?

Post #1510920
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse