Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Query Taking too long to execute when filter on geography data (STContains,STGeomFromText)??? RE: Query Taking too long to execute when filter on geography data (STContains,STGeomFromText)???

  • 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