Comparing geography column using STContains slow down the query in SQL Server

Question

I want to compare two geography column using StContains function but it slow down the query and output is obtained after 30-40 seconds which is a huge performance issue.

I am using below query:

SELECT  DISTINCT
            T.[LocationID], G.[Boundary].STContains(ESP.GeographyValue)
    FROM    [#TempTable] T
    CROSS JOIN [dbo].[GeographyTable] G
    INNER JOIN [dbo].LocationTable ESP ON T.LocationID = ESP.LocationID
    WHERE   G.[ID] = 1

Here both Boundary and GeographyValue are geography data type.

If i remove G.[Boundary].STContains(ESP.GeographyValue) then query is executed in 0 second so the main performance issue is due to StContains.

The result set contains 7000 records which may not be the issue.

Update:

I have added spatial index to both the columns but still the execution is slow. By running a simple query it takes 10 secs.I used below query:

select ES.* from LocationTable ES INNER JOIN GeographyTable G ON ES.GeographyValue.STEquals(G.Boundary) = 1 Where G.Id = 1

I also tried STContains but still no luck.

Answer 1

Force your code to use spatial index using below code [dbo].[GeographyTable] G WITH (INDEX(SI_Geofence_Boundary))

Your revised query will look like,

SELECT  DISTINCT
            T.[LocationID], G.[Boundary].STContains(ESP.GeographyValue)
    FROM    [#TempTable] T
    CROSS JOIN [dbo].[GeographyTable] G WITH (INDEX(SI_Geofence_Boundary))
    INNER JOIN [dbo].LocationTable ESP ON T.LocationID = ESP.LocationID
    WHERE   G.[ID] = 1

It would reduce execution time from 30-40 seconds to 2-3 seconds.

For more information review the link