0

In a table with almost 50 million rows, there's a geography column.

I've already created a spatial index on that column and when I tried to execute the below query

SELECT
    table_name.column_name.STDistance(geography::STPointFromText (('POINT(latidude longitude)'), 4326)) AS dist
FROM       
    table_name (nolock)
INNER JOIN 
    table_name_2 (nolock) ON (some conditions)
INNER JOIN 
    table_name_3 (nolock) ON (some conditions)
WHERE   
    geography::STPolyFromText (('POLYGON((latitude longitude))'), 4326).STContains(table_name.column_name) = 1

This query is running for a very long time.

Apart from the spatial index, is there something else I can do to improve the performance of this query?

Improve this question
2
  • 1
    Why are you spamming your NOLOCK hint here? Do you "need" to use it? If you "must" use it, why are you using deprecated syntax or not just changing the isolation level of your query, as you're using it against every table? Bad habits : Putting NOLOCK everywhere Commented Jun 10, 2022 at 8:21
  • 1
    You'll notice that for geography, STContains isn't in the list of methods supported by spatial indexes. So why would you expect it to benefit from one? Commented Jun 10, 2022 at 8:25
Related questions
1
MS SQL Server 2008 Spatial Indexing - Does it work?
3
SQL Server 2008 Performance on nullable geography column with spatial index
1
how to get the index of a point from geography?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.