0

I have a table, dbo.ashfill_pointCloud, that consists of approximately 3 million geometry points. I then draw polygons (rectangles) and run a query to find the average of the z-values of all the points that lie inside that polygon.

The performance is extremely slow and non-consistent.

The query: 

`select @averageZ = NULLIF( AVG(NULLIF(Ogc_geometry.Z,NULL)),NULL) 
                            FROM dbo.ashfill_pointCloud WITH (INDEX(si_geom_points)) 
                            WHERE Ogc_geometry.STWithin(@deltablock)=1 `

I am using a spatial index (si_geom_points) on the dbo.ashfill_pointCloud table and it is set up as follows:

Bounding Box:
X-min: 12700
Y-min: -2940200
X-max: 13300
Y-max: -2938800

General:
Tesselation Scheme: Geometry grid
Cells per object: 16

Grids
Level1: Medium
Level2: Medium
Level3: Medium
Level4: Medium

The bounding box is specified to include all the points in the dbo.ashfill_pointClouds table. I have also tried many other index setups, for example different grid levels, more cells per object etc. with no luck.

It seems like the query will execute extremely fast for certain regions, but extremely slow (as in hours) for other regions. I have also noticed that if there are no points inside the polygon it also slows down performance drastically. I have also tried using other methods, like STIntersects(), with the same performance.

Any ideas on what I could be doing wrong will be appreciated.

Nico Bos
  • 185
  • 1
  • 10
  • why are you using an INDEX-Hint? – CPMunich Nov 19 '15 at 10:16
  • @CPMunich I wanted to make sure that the index is getting used. I have read up on the "estimated cost plan" that is automatically calculated, but I just tried with and without the hint to see the difference, but there was barely any. – Nico Bos Nov 19 '15 at 11:20
  • If there is no difference, you should consider removing the hint. If there are changes to the index or someone else decides to remove the index, the query can fail. Is the query inside a storec procedure? Are the statistics up to date? Maybe try a filtered statistic for a region where the query is slow. – CPMunich Nov 19 '15 at 14:12

0 Answers0