SDE Spatial Views versus Definition Query on a Layer

[AdamZiegler]

Hello all,

I was hoping to open a discussion on SDE Spatial Views versus a SDE layer in a mxd with an applied definition query. I've searched around but haven't found any good information on best practices with spatial views and when might be appropriate and when might not be appropriate to use them.

Here is my scenario.  I work for a county and we recently acquired an impervious surface layer that was derived by a consultant from LiDAR and photo analysis.  The data is classified as either Building, Road or other. The data team requested that the data be loaded into the SDE database and be provided as two separate layers; impervious layer and building footprints. Additionally, manual edits will need to take place on the building footprints to make them more useable to the end users (i.e. splitting condos and row homes). Because of that I wanted to make sure that there was one layer that was being edited to maintain topological correctness.

Upon hearing the request my initial thought was to load the entire impervious surface as a feature class and then create a spatial view of the impervious surface using the where clause of type = Building. As we worked through some of our testing, what was found was that a Spatial View is actually slower to perform than the impervious layer added to ArcMap with a definition query applied to it. I ran traces from the DB side and found the same thing, it is more expensive to run the Spatial View than allowing ArcMap to use a definition query.

I had been under the assumption that using a literal from an application would be less efficient than compiling that into a db object. If anyone can weigh in on this that has a bit more experience I would really appreciate it.

Thanks,
Adam Z

[VinceAngelo]

You should test a third option - Load the building subset as a separate table.

You don't say what scale factor was involved, but I'd imagine the difference
was due to spatial-first vs. attribute-first processing.

- V

[AdamZiegler]

Thanks for the reply Vince.

We did test that as an option as well. I didn't mention it because I want the footprints 'stand-alone' to remain synchronized with the building footprints in the impervious layer. That's why I wanted to use a where clause in either the spatial view or within the definition query of an ArcMap layer.  Once separated into two physical tables they are forever separated, right? To keep them topologically correct then becomes editing on two layers versus one. And if I were to separate buildings as a stand-alone and roads and other as a stand-alone I then degraded my impervious layer into two separate layers taht need to be re-merged to do surface water modeling.

I was testing at scales that I thought were appropriate for building footprints, 1:15,000 max. The time difference in display is pretty minimal as well, say 1.5 seconds to draw for the spatial view and 1 second for the definition query.

I'm also using this as a test case for more complex spatial views that I have coming down the line. At the county we use variations of parcels very often and current methods of creating those variations involves truncating and appending geometries and then applying attributes from external systems (i.e. addressing and assessment). We have our base layer and 4 variations of them in our database. I would like to maintain one versioned parcel geometry and apply it to multiple variations of parcels so that I can then use geodatabase replication to a database that will be used by ArcGIS server (either internal or potentially on the cloud). The construction of our parcel variation layers don't play nice with GDB replication.

Thanks,
Adam Z

[AdamZiegler]

I also forgot to mention that the stand-alone buildings layer loads as fast as the layer with the definition query applied.

Adam Z

[VinceAngelo]

What's the fraction of the area in Buildings to non-buildings?  What are the row
counts of the table and view?

It sounds likely that the view is effectively applying an attribute-first search,
while definition query is applying a spatial-first search.  You could confirm this
by explicitly requesting attribute-first in the layer definition, and seeing if it
slows down to view performance.

You might also try using a tuning hint in the view definition to have it use the
spatial index first.  After that, you're down to reviewing optimizer query plans.

- V

[AdamZiegler]

The entire feature class contains 421,357 features, 234,713 of which are classified as buildings. The view and layer with definition query both return 234,713.  Although the spatial view also creates views of the S and F tables which are not filtered by the where clause (and how could they be...). I'm not sure what you were asking about the fraction of area in buildings to non-buildings. Do you mean total area of each?

Under you advice I changed the order of the definition query layer to be attribute first and it slowed it dramatically.  Much slower than the spatial view (by a factor of at least 5).  I checked the order of the spatial view layer in ArcMap and it is set to spatial first, but from what you said it seems like I can do more to ensure spatial search first in the the database as well.  How is this accomplished in the spatial view creation?

Thanks,
Adam Z