topic Re: Spatial Indexes in Geoprocessing Questions

Spatial Indexes

JenniferDick — Tue, 04 Nov 2014 16:35:20 GMT

I'm applying spatial indexes to a few layers that reside in a geodatabase. We are testing the concept of spatial indexing for the purposes of performance improvement.

A few of the layers we would like to put indexing on have for example, (on a point line) over 730,000 points. I've calculated the default spatial grid index using geoprocessing tools and have then used 'Add Spatial Index' tool to apply the grids calculated prior.

Now I'm trying to review the grid sizes and confirm that they were apply correctly using the layer properties. Unfortunately, they aren't listed under the index tab in the properties. Reading about this, it seems like the grid values should be listed here, and I should also have the ability to create, delete, recalculate. Though my only 2 options are 'Create' and 'Delete' and one of those are grayed out.

I'm hoping someone can provide some guidance in this area. I would like to confirm that the values Arc calculated are in fact there.

I've attached a screen shot of the later properties for your reference.

Please help,

Thank You

Jennifer

Re: Spatial Indexes

VinceAngelo — Tue, 04 Nov 2014 16:43:06 GMT

What kind of geodatabase are you using? If Enterprise or Workgroup, what RDBMS and which geometry storage type are you using? Not all storage types support grid levels.

- V

Re: Spatial Indexes

JenniferDick — Tue, 04 Nov 2014 16:56:34 GMT

I've created a file geodatabase off our main network so that I'm not testing or running processes off our 'Hub' that customers access.

On our Hub, the geometry properties are Esri Binary Spatial Type.

But once we move to SDE 10.1 it will be ST_Geometry spatial type.

We have Oracle running in the back as well.

Re: Spatial Indexes

VinceAngelo — Tue, 04 Nov 2014 22:31:19 GMT

Basically, the grid sizes have been de-emphasized in the Desktop UI, mostly because there isn't much you can really do to improve performance (or break it, for that matter). You can use ArcObjects to access the generated grid levels (or 'sdelayer' if 1) the layer is enterprise and 2) you still have admin commands installed, and 3) the storage type supports multiple grids), but I've nearly given up on trying to out-guess the indexing code, so access to the values is somewhat moot.

- V

Re: Spatial Indexes

JenniferDick — Wed, 05 Nov 2014 14:22:19 GMT

In your opinion Vince, would the grid levels generated by Arc be sufficient enough to increase drawing and querying performance?

Re: Spatial Indexes

VinceAngelo — Wed, 05 Nov 2014 15:03:24 GMT

"Increase" in comparison to what? Not having an index at all?

There are a number of factors that play into spatial query performance. Having a spatial index is certainly one, but not even a spatial index can overwhelm the impact of extreme spatial fragmentation on queries which return a large number of features. Use of local disk is generally good for a 2x performance boost (vice networked access), as is use of solid state disk (SSD) drives (<1ms seek time).

- V

Re: Spatial Indexes

JenniferDick — Wed, 05 Nov 2014 15:15:03 GMT

Yes not having an index at all.

I agree, performance is definitely dependent on a number of variables. Our layers are accessed via a network and customers go directly to that network to obtain the information they want. Some of the drawing and querying times when they are accessing right off our network is slow and I'm testing to see if applying a spatial and/or attribute index would help at all. Even just previewing the layer in ArcCatalog is painfully slow.

Re: Spatial Indexes

VinceAngelo — Wed, 05 Nov 2014 19:04:08 GMT

Full table scans are always going to be the least-performant types of queries. Using a spatial index on a full table query will generally hurt performance by 2x to as much as 10x.

The purpose of an index is to reduce the I/O necessary to locate a small set of features. If you have a large number of features or the features themselves are slow to access, then the index isn't going to help.

Working on a FGDB copy to improve Enterprise Geodatabase performance is not likely to be productive.

- V

Re: Spatial Indexes

JenniferDick — Wed, 05 Nov 2014 20:01:52 GMT

What about attribute indexes instead of spatial?

Re: Spatial Indexes

JenniferDick — Wed, 05 Nov 2014 20:02:58 GMT

I agree with you thought in your previous statement Vince. Just inquiring on attribute indexes in replace of spatial.

Re: Spatial Indexes

VinceAngelo — Wed, 05 Nov 2014 20:12:27 GMT

I didn't make a distinction for a reason -- Forcing index use when the constraint does not significantly reduce the result set is not likely to improve performance, especially if the table is fragmented with respect to the indexed column(s). This is why optimizers collect statistics -- to get a sense of whether the index will help or hurt, and which index will help most.

- C

Re: Spatial Indexes

JenniferDick — Wed, 05 Nov 2014 20:15:20 GMT

Thanks for your thoughts Vince

Re: Spatial Indexes

JenniferDick — Wed, 05 Nov 2014 22:34:33 GMT

Another reason that it may be problematic to apply a spatial or attribute index is when the layer is consistently updated. The layers I speak up are often updated and what I've read is that each time you update a layer that is spatially index, you have to update the index.

Re: Spatial Indexes

VinceAngelo — Wed, 05 Nov 2014 22:59:43 GMT

No, once an index is built, additional inserts/updates/deletes are manifested as they should. It's probably a good idea to periodically refresh the indexes, and you want to be careful on the number of unnecessary indexes with large, frequently updated tables, but it's certainly not necessary with each delta.

- V

Re: Spatial Indexes

ThomasColson — Tue, 07 Jul 2015 18:15:18 GMT

Witch Magic, Snake Oil Medicine, and Spatial Index Tuning