Selecting geometry column data taking more time in SQL server.

Question

1.00/5 (1 vote)

See more:

, +

Hi,

SQL

 declare @points nvarchar(500)='POINT(-76.5921662 39.2674632)'

SELECT *
  FROM [NeighborhoodsTable] 
  WHERE [NbhoodPolygon].MakeValid().STIntersects(geometry::STGeomFromText(@points, 4326)) =1

This query taking 20 seconds.
NbhoodPolygon column datatype is geometry.
In my database 2 lakhs records is there.
Help me Plz...

What I have tried:

I need to optimize the query, ~~I want results in quickly.~~

Posted 20-Apr-16 1:29am

NagaRaju Pesarlanka

Updated 20-Apr-16 14:16pm

Patrice T

v3

Add a Solution

Comments

John C Rayan 20-Apr-16 8:50am

I have not used geometry in my projects but you have to use spatial indexes using Bounding Box I guess.

NagaRaju Pesarlanka 20-Apr-16 9:00am

yes

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

an0ther1 · Answer 1 · 2016-04-20T14:16:00

Firstly do not use SELECT *. Specify the columns you want to return in your query or SQL needs to perform another query to get the column names.

Secondly, use the Database Engine Tuning Adviser to determine what indexes are required for the query - it is on the Windows Start Menu under SQL >> Performance Tools
This can be also normally be done by selecting your query & then right-clicking or can also be done by using SQL Profiler to capture a trace of the query.
You can also use the option in SQL Management Studio to "Include actual execution plan". This will often advise of missing indexes but it is not as effective as the Tuning Wizard.

Neither method is foolproof nor do they take into account your typical workload. Read up on Indexing for additional information on what you need to take into account before blindly applying recommendations & test against real workloads - indexes need to be updated when the table data is changed & this may negatively affect performance.

Kind Regards