Post reply

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

  • 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

  • June 27, 2017 at 6:51 am

    #1948348

    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!

  • June 27, 2017 at 7:24 am

    #1948361

    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

  • June 27, 2017 at 7:38 am

    #1948365

    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)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • June 27, 2017 at 7:53 am

    #1948369

    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

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

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