Query Taking too long to execute when filter on geography data (STContains,STGeomFromText)???

Question

Query Taking too long to execute when filter on geography data (STContains,STGeomFromText)???

vineet_dubey1975

SSCrazy

Points: 2306
More actions
June 27, 2017 at 6:40 am

#338407

hi I am hitting a SQL Query job on my production database with the billions rows in the Address d table and AddressGeoLocation ad
Where clause like for e.g.
Select * from Address d where d.field1 is null
and d.Location.STContains(geography::STGeomFromText('POINT(' + cast(ad.Longitude as varchar(20)) + ' ' + cast(ad.Latitude as varchar(20)) + ')', 4326)) = 1
How can i optimize the query or apply any best practice or Index to have quick result set.
Thanks
VD

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

id want to see the execution plan and know what indexes are on the table.

i don't know if the STGeoFromText is slowing things down, if i have the latitude and longitude, i just create a point, and not convert to varchar and use the other function to get the point:

Select * from Address d where d.field1 is null and d.Location.STContains([geography]:: Point(ad.Longitude,ad.Latitude,4326)) = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 2

Lowell - Tuesday, June 27, 2017 6:51 AM
id want to see the execution plan and know what indexes are on the table.
i don't know if the STGeoFromText is slowing things down, if i have the latitude and longitude, i just create a point, and not convert to varchar and use the other function to get the point:
Select * from Address d where d.field1 is null and d.Location.STContains([geography]:: Point(ad.Longitude,ad.Latitude,4326)) = 1

Msg 6522, Level 16, State 1, Line 8
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
System.FormatException:
at Microsoft.SqlServer.Types.SqlGeography.Point(Double latitude, Double longitude, Int32 srid)

Already put the condition for the -90 and 90
when i apply the method as suggested.
VD

sgmunson SSC Guru Points: 110640 More actions · Answer 3

vineet_dubey1975 - Tuesday, June 27, 2017 7:24 AM
Lowell - Tuesday, June 27, 2017 6:51 AM
id want to see the execution plan and know what indexes are on the table.
i don't know if the STGeoFromText is slowing things down, if i have the latitude and longitude, i just create a point, and not convert to varchar and use the other function to get the point:
Select * from Address d where d.field1 is null and d.Location.STContains([geography]:: Point(ad.Longitude,ad.Latitude,4326)) = 1
Msg 6522, Level 16, State 1, Line 8
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
System.FormatException:
at Microsoft.SqlServer.Types.SqlGeography.Point(Double latitude, Double longitude, Int32 srid)
Already put the condition for the -90 and 90
when i apply the method as suggested.
VD

I'm not sure what's going on here, but it's pretty clear that the function Point is being called with values that are outside of the valid range. The value being supplied is coming from "ad.Latitude". Thus if you want to fix that problem, you probably need to correct the data in that field. It only takes one record to have an invalid value to muck things up.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

vineet_dubey1975 - Tuesday, June 27, 2017 6:40 AM
hi I am hitting a SQL Query job on my production database with the billions rows in the Address d table and AddressGeoLocation ad
Where clause like for e.g.
Select * from Address d where d.field1 is null
and d.Location.STContains(geography::STGeomFromText('POINT(' + cast(ad.Longitude as varchar(20)) + ' ' + cast(ad.Latitude as varchar(20)) + ')', 4326)) = 1
How can i optimize the query or apply any best practice or Index to have quick result set.
Thanks
VD

Your query references alias ad, which is not defined in the query.
You probably need to join Address and AddressGeoLocation in the FROM list.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden