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.

What is the purpose of this query that you need to compare 7000 different locations? Spatial comparisons are complex and resource intensive so can take a while and you are doing 7000 of them! Do you have a spatial index on your Geography columns? — iamdave
– iamdave, Commented Dec 21, 2016 at 11:47
I have a list of Geography on UI through which user can select one of then and I want to compare GeographyTable with matching location. LocationTable is present in another database so I cannot add GeographyTable ID column to it. Spatial index is not present in both the table. Can you guide to which table I need to add it and how? — Manprit Singh Sahota
– Manprit Singh Sahota, Commented Dec 21, 2016 at 12:16
Spatial indexes go on your spatial columns... If your users are selecting one Geography via your UI, why are you comparing all 7000? Just compare the one they select. — iamdave
– iamdave, Commented Dec 21, 2016 at 12:20
I only have Id of GeographyTable and I want to get records from LocationTable based on it. I don't have relation of GeographyTable and LocationTable. The only common field is Boundary from GeographyTable and GeographyValue from LocationTable. So is there any way to compare both of this? If i use STContain and StEquals then it takes 30 seconds. — Manprit Singh Sahota
– Manprit Singh Sahota, Commented Dec 21, 2016 at 13:07

Parag · Accepted Answer · 2017-02-23 12:56:04Z

3

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

answered Feb 23, 2017 at 12:56

Parag

463 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related