This query is very slow. I am new to spatial indexes and am not sure where to begin tuning this query.

I see it is using an index seek, but i am not sure what the filter is doing. Any help is a appreciated.

avatar image By jmalley84 16 asked May 14, 2013 at 01:26 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

3 answers: sort voted first

Hi Jason,

With just a short glance at the plan, I'd suspect the spatial index definition, either the definition of the bounding box or the density/cells per object parameters. What does the definition of the ...BlockGroupGeom spatial index look like? If, for example, its for census block groups (I have a DB of them too) intersecting a table of points (point-in-polygon query), density of high at every level is a good density for spatial index. The default is medium at every level.

A good spatial index will eliminate over 90% of the rows in the table, but the number of rows after the spatial index step is over 200,000. That's going to slow any spatial query down. How many total rows are in the block group table?

You can run the system spatial diagnostic procedures (sp_help_spatial_geometry_index_xml and friends) to see how good your spatial index is. I wrote a 6-7 article blog series of these starting here. http://www.sqlskills.com/blogs/bobb/spatial-index-diagnostic-procs-intro/.

I've run spatial queries against large tables on a relatively small VM with subsecond response much of the time. Although the small VM IS causing your Hash aggregate overflow warning, which is further slowing your query down, the >200,000 rows that are input to that step is the biggest culprit. Especially when the query only returns 1 row.

Because the definition of the spatial index parameters can greatly affect performance, this is one place where a query plan by itself is not enough. More information is needed to analyze the problem, because the shape of all plans of queries that use the spatial index are mostly the same, at least the spatial intersection part is.

Feel free to write back (or mail me directly) if you still have problems or need help in deciphering the output of spatial analysis procs.

Cheers, Bob

avatar image By Bob Beauchemin 51 answered May 18, 2013 at 06:09 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

Hi there,

I got a bit of input on this type of plan from Bob Beauchemin of SQLskills. I noticed there are three warnings in the plan:

  1. spatial guess

  2. missing column stats

  3. tempdb spill

The first two are not anything you should worry about, since there's nothing you can do that will fix anything:

  1. The spatial guess warning always happens (bug reported here).

  2. The missing column stats warning is "by design" - it always happens on the internal table for the spatial index, and isn't considered a bug.

Outside of improvements to the actual spatial index or the logic that requires the query to use it, that leaves the tempdb spill, which may indicate a shortcoming in memory to handle the query. I am out of my depth here as I have not used spatial indexes, but I hope to get more feedback from Bob on your specific plan (or maybe even coerce him to post a reply :-)).

In the meantime, I could probably make a case that Plan Explorer should suppress the missing column stats warning (the spatial guess warning, I presume, will be fixed in the near future).

avatar image By Aaron Bertrand ♦ 1.7k answered May 16, 2013 at 12:44 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

Aaron,

Thanks for your reply. The server I am running this query on is a very small VM. I will run that same query on a beefier machine and see what happens. It sounds like to me that I may not be able to improve the performance much. We ran this same query on Postgres database and it ran substantially faster. This upset me greatly so I had to see if I could improve performance, but quickly realized I was in over my head.

thanks again

Jason

avatar image By jmalley84 16 answered May 16, 2013 at 03:07 PM
more ▼
(comments are locked)
avatar image Bob Beauchemin May 21, 2013 at 02:00 AM

Hi Jason,

You absolutely SHOULD be able to improve the performance. See answer above. ;-)

Cheers, Bob

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x445
x1
x1
x1

asked: May 14, 2013 at 01:26 PM

Seen: 1668 times

Last Updated: May 21, 2013 at 02:00 AM