0

I have an interesting problem. I have a select statement generated by GeoServer on a de-normalized table. The structure of the query is this

select "the_geom".STAsBinary() as "the_geom"
from TABLE
where [a bunch of ands and ors condition]
AND
"the_geom".Filter(geometry::STGeomFromText('') = 1;

This question takes around 6 minutes. If I skip the spatial condition, the question takes around 1 second. The database is around 40GB, SQL Server 2014, 26GB of RAM. I have a spatial index and other indexes for the non-spatial parts. The spatial index has grid-size around 600.

So my question is if there is any way I can make this question faster? Any tweeks I can do in SQL Server?

Perfa
  • 81
  • 4
  • Are you sure this the query that's being sent? `STGeomFromText()` takes two parameters by the looks of it. Does geoserver really send an empty string to that call? You probably want to fiddle with the values in the index for that control the grid levels and cells per object. Can you post the DDL for the spatial index? – Jason Koopmans Apr 13 '17 at 01:46
  • ddl: CREATE SPATIAL INDEX [idx_name] ON [dbo].[tabler] ( [GEOM] )USING GEOMETRY_GRID WITH (BOUNDING_BOX =(380656, 6836190, 905384, 7533950), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 900, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO Jason you are right, the STGeomFromText takes two arguments, I left the EPSG code which is 3006. The main problem is that grid is too big. I will assembly some metrics – Perfa Apr 13 '17 at 07:56
  • 1
    If you mean the bounding box, maybe it's too big -- but the items I've normally tuned are the different levels in the `GRID=` portion of what you've shown as well as cells per object as I mentioned before. In particular, cells per object look high for the workloads I usually see. These two links provide a fairly concise explaination of what they control and as one might expect, sliding these values results in time/space tradeoffs. https://www.simple-talk.com/sql/t-sql-programming/sql-server-spatial-indexes/ and http://boomphisto.blogspot.com/2011/04/black-art-of-spatial-index-tuning-in.html – Jason Koopmans Apr 13 '17 at 11:04
  • Yeah, the problem is the CELLS_PER_OBJECT value is too high. I will get back with metrics and workaround for this case. Thanks @JasonKoopmans for the articles. – Perfa Apr 13 '17 at 19:03
  • Let me know what you end up settling on. Does it make sense to write up an answer that draws attention to that part of the index? I can summarize the comments as an answer. – Jason Koopmans Apr 14 '17 at 04:03

1 Answers1

1

Problem is that spacial index connot include any columns or seek by another columns. Therefore if your condition the_geom".Filter(geometry::STGeomFromText('') = 1 returns many rows perfomance can be bad.

In my practise sql server always use spatial index and you need to apply hint if you want to use another index

Mikhail Lobanov
  • 2,976
  • 9
  • 24