How to optimize speed of a select query

shaniquarayford92

The issue I am having is that when I run a query using the Esri ST_Geometry package it is

taking up to 5 minutes to give a result. It is possible that it may be because

of the large number of records in the view.

Below is an example of the query I am running:

 

select sectionnumber from land.fisgridsection_vw

where sde.st_overlaps(sde.st_buffer(

(SELECT shape FROM land.fisgridsection_vw

WHERE sde.st_intersects(sde.st_point(792026,335093,2274),shape) = 1),1),shape)= 1;

 

My question is "how can I optimize the speed of this query?" Are there any other options

I could use?

JoshuaBixby

Honestly, it is really difficult to troubleshoot performance issues like this in the forums unless you can provide a whole lot more information about the data, SQL execution plans, etc....

Since this involves multiple spatial operations, have you broken the problem down to see at which step the execution time increases the most?  For example, how long does it take for:

<SPAN class="keyword token">SELECT</SPAN> shape <SPAN class="keyword token">FROM</SPAN> land<SPAN class="punctuation token">.</SPAN>fisgridsection_vw
<SPAN class="keyword token">WHERE</SPAN> sde<SPAN class="punctuation token">.</SPAN>st_intersects<SPAN class="punctuation token">(</SPAN>sde<SPAN class="punctuation token">.</SPAN>st_point<SPAN class="punctuation token">(</SPAN><SPAN class="number token">792026</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="number token">335093</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="number token">2274</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN>shape<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

a