SDE View poor performance

VishApte — Fri, 28 Sep 2012 01:27:09 GMT

Hi all,

I have a problem with showing an SDE View as a data layer in ArcMap. It is very slow to render when zooming and panning etc.

Here is my set-up;

ArcGIS for Desktop 10.1
ArcSDE 10.1 for Oracle 11g R2.
All geometries are stored as MDSYS.SDO_GEOMETRY
All feature classes has spatial indexes created using AddSpatialIndex_management tool.

Here is my problem.
I have two feature classes, HV_LINE and HV_CABLE. User wants to see them as a single map layer for reasons beyond my control.
I created SDE View called VW_HV_CONDUCTORS using 10.1 tool with following view creation statement.
Select ASSETID, VOLTAGE, MATERIAL, FEEDER_NAME,SHAPE From HV_LINE Where STATUS='LIVE'
Union All
Select ASSETID, VOLTAGE, MATERIAL, FEEDER_NAME,SHAPE From HV_CABLE Where STATUS='LIVE'

ASSETID is unique across the entire database, I confirmed it. STATUS field has attribute index on both feature classes. Both feature classes have up-to-date spatial index and have same coordinate system of WGS_84.

The view creates fine. Making query on the view in Oracle-SQL works reasonably well. Query in SQL on this view without where clause finishes under 10 seconds with SHAPE field ommitted from the query.

When I add this view as a layer in ArcMap document, ArcMap asks me to choose unique key and I selected ASSETID. I also selected WGS_84 as the coordinate system. The map data frame is also in WGS_84 coordinate system.

Once I add this as layer, zooming and panning becomes too slow... I checked with Oracle, it is using CPU continuously. The layer eventually renders but it is painfully slow. I tried removing this view layer and adding HV_LINE and HV_CABLE as individual layers. This works absolutlely fine when zooming etc.

Any idea on why the SDE view is performing slow in ArcMap? This map document will be shared as mapping service and will be used in a FlexViewer application. So any help resolving this issue will be greatly appreciated.

Regards,
Vish

Re: SDE View poor performance

VinceAngelo — Fri, 28 Sep 2012 10:01:33 GMT

Have you tried using SDO_GEOMETRY spatial constraints on this view From SQL*Plus? I expect
the title of this thread should be "Poor SDO_GEOMETRY view performance with UNION ALL join",
since all ArcSDE is doing is making the query.

- V

Re: Oracle View poor performance (solved)

VishApte — Mon, 01 Oct 2012 02:05:03 GMT

Thanks Vince,

I could fix the problem. Here is what I did;

1. Dropped the existing spatial indexes on HV_LINE and HV_CABLE.
2. Select Distinct a.SHAPE.SDO_GTYPE From <Tables> a; All values were 3002
3. Added a spatial check constraint on both tables as SHAPE.SDO_GTYPE=3002
4. Recreated rtree spatial indexes with PARAMETERS 'layer_gtype=LINE'
5. Dropped and recreated the view.
6. In MXD, removed and readded the view as layer. Spcified primary key field and SRID.

And it all works fine as expected.

Cheers,
Vish

topic SDE View poor performance in Data Management Questions

SDE View poor performance

Re: SDE View poor performance

Re: Oracle View poor performance (solved)