topic Re: Spatial View very slow in Data Management Questions

Spatial View very slow

ScottSugden — Sat, 17 Nov 2012 21:19:04 GMT

Hi,

Environment: Windows Server 2008 R2 Standard, Oracle 11g, ArcGisServer 10.1 SP1

Using an existing Feature class, CADASTRE, I created an Oracle view as follows:

CREATE OR REPLACE FORCE VIEW "CADASTRE_TEST" (
"OBJECTID",
"CADID",
"LOTNUMBER",
"SECTIONNUMBER",
"PLANNUMBER",
"PLANLABEL",
"SHAPE")
AS
select /*+FIRST_ROWS*/ c.objectid, c.cadid, c.lotnumber, c.sectionnumber, c.plannumber, c.planlabel, c.shape
from CADASTRE c;

As you can see this is about as basic as it gets. There is no join etc. The only thing it does it select a subset of columns. In theory this should be faster than selecting * on CADASTRE. I tried it with and without the /*+FIRST_ROWS*/ hint.

I then described the feature class CADASTRE using sde as follows:
sdelayer -o describe_long -l CADASTRE,SHAPE -i sde:oracle11g:xxx -u GIS -p xxx (xxx is dumy)

From that I worked out the shape data type, the SRID and the feature types. I then registered CADASTRE_TEST with sde as follows:
sdelayer -o register -l CADASTRE_TEST,SHAPE -e nac+ -t ST_GEOMETRY -C OBJECTID,USER -R 7 -i sde:oracle11g:xxx -u GIS -p xxx

The spatial view is then visible, selectable, identifiable etc in ArcMap/ArcCatalog. However, it is very slow. It is almost as if the spatial view is not using the spatial index from the feature class?

Any ideas? Thank you in advance.

Re: Spatial View very slow

VinceAngelo — Sat, 17 Nov 2012 22:56:25 GMT

What happens if you reference the shape column without the "c" alias prefix?

- V

Re: Spatial View very slow

ScottSugden — Sun, 18 Nov 2012 02:01:53 GMT

Hi Vince,

Thanks for responding to this thread also.

There is no improvement to performance when I don't use an alias.

I forgot to mention that when using SQL Developer 'Select * From CADASTRE' is about the same speed as 'Select * From CADASTRE_TEST'.

Re: Spatial View very slow

VinceAngelo — Sun, 18 Nov 2012 21:58:12 GMT

An unbounded SELECT * doesn't really have anything to do with spatial query
performance. You need to fashion a spatial query to test spatial search.
Then you can use Oracle to figure out the query plan.

I don't ever add hints, so I can't help but wonder if that's the problem.

- V

Re: Spatial View very slow

ScottSugden — Sun, 18 Nov 2012 23:19:07 GMT

I have already tried it with and without the /*+FIRST_ROWS*/ hint - no change.

I agree with what you are saying about "An unbounded SELECT * doesn't really have anything to do with spatial query performance.", but I suppose that is what I was trying to say. That the issue would appear to be either a spatial query performance problem (and not an attribute query problem) or Arc is having some sort of issue with the view? Oracle does not appear to be having any issue with the view?

Thanks.

Re: Spatial View very slow

VinceAngelo — Mon, 19 Nov 2012 01:13:41 GMT

You don't know if Oracle has a problem with the view until you try a pair
of spatial queries (on table and view) and look at the query plans.

- V

Re: Spatial View very slow

ScottSugden — Mon, 19 Nov 2012 05:34:35 GMT

True. However, I cannot get any ST_ functions to work. I am getting 'ORA-28595: Extproc agent : Invalid DLL Path'. I have tried a number of suggestions from online but none of them seem to help. So unfortunately I cannot run a spatial query in Oracle.

Re: Spatial View very slow

VinceAngelo — Mon, 19 Nov 2012 10:13:06 GMT

You'll need to fix the Listener/LIBRARY configuration issue first then. Tech Support
can help with both.

- V

Re: Spatial View very slow

ScottSugden — Tue, 20 Nov 2012 05:21:37 GMT

Ok, finally got ST_ functions working. Turns out I needed to change 'SET EXTPROC_DLLS=' to 'SET EXTPROC_DLLS=ANY' in the extproc.ora file.

I tested the following query on both the CADASTRE table and the CADASTRE_TEST view:

Select *
From cadastre_test c
Where sde.st_intersects(sde.st_geometry ('polygon ((333733 6404057, 345653 6404057, 345653 6394105, 333733 6394105, 333733 6404057))', 28356), c.Shape) = 1;

Returning the first 500 rows over a WAN the CADASTRE table took 4.1 seconds and the CADASTRE_TEST view took 3.7 seconds. This is what I would expect, that is the view is slightly faster.

I think this also shows that Oracle does not have a performance issue with a spatial query on the view. So does this suggest and issue with ArcGIS or perhaps the way the view has been registered in the geodatabase?

Thanks,

Re: Spatial View very slow

VinceAngelo — Tue, 20 Nov 2012 10:20:13 GMT

ArcGIS does an ST_EnvIntersects query. Since there is nothing special about
that query, if the return results are slow, then there is an Oracle optimizer issue.
Tech Support can help you diagnose the problem. Make sure you evaluate the
view without any hints.

- V

Re: Spatial View very slow

ScottSugden — Tue, 20 Nov 2012 17:16:31 GMT

Same result.

I recreated the view to make sure it was not using any hints. I then ran the following query on both CADASTRE and CADASTRE_TEST:

Select *
From cadastre_test c
Where sde.st_envintersects (c.Shape, 143, -31.9, 144.5, -31.5) = 1;

Returning the first 500 rows over a WAN the CADASTRE table took 1.3 seconds and the CADASTRE_TEST view took 1.2 seconds.

So it would appear that there is no Oracle optimiser issue?

Thanks,

Re: Spatial View very slow

ScottSugden — Tue, 20 Nov 2012 18:04:50 GMT

Ok, the plot thickens. It appears to be an issue with registering the spatial view. I have been registering the spatial view from an Oracle view as follows:

sdelayer -o register -l CADASTRE_TEST,SHAPE -e nac+ -t ST_GEOMETRY -C OBJECTID,USER -R 7 -i sde:oracle11g:xxx -u xxx -p xxx

If I use sdetable to create a view as follows the performance of the spatial view is just fine:
sdetable -o create_view -T cadastre_test1 -t cadastre -c objectid,cadid,lotnumber,sectionnumber,plannumber,planlabel,shape -i sde:oracle11g:xxx -u xxx -p xxx

I have looked through the various SDE tables and found the following differences:
Table COLUMN_REGISTRY (Column OBJECT_ID different)
CADASTRE_TEST1 SDE SHAPE 8 32772 17
CADASTRE_TEST SDE SHAPE 8 32772 0

Table LAYERS (Columns EFLAGS, GSIZE1, BASELAYERID different)
17 SDE CADASTRE_TEST1 SHAPE 608436369 0 0.029 0 0 140.9995956528 -37.4407374802425 159.084596196734 -28.1570205549076 1353439530 DEFAULTS 1 6 7 0 0
16 SDE CADASTRE_TEST SHAPE 1682178193 0 0 0 0 140.9995956528 -37.4407374802425 159.084596196734 -28.1570205549076 1353210279 DEFAULTS 1 6 0 0 0

Table ST_GEOMETRY_COLUMNS (No entry for CADASTRE_TEST1)
SDE CADASTRE_TEST SHAPE ST_GEOMETRY

Table TABLE_REGISTRY (Columns OBJECT_FLAGS, REGISTRATION_DATE (expected), MINIUM_ID different)
22 CADASTRE_TEST1 SDE OBJECTID 131207 1353439389 DEFAULTS 1
21 CADASTRE_TEST SDE OBJECTID 16517 1353210142 DEFAULTS

Obviously the two methods of registration produce different results.

Is there something wrong with my sdelayer -o register?
Is this by design?
Is this a bug?

Thanks

Re: Spatial View very slow

ScottSugden — Tue, 20 Nov 2012 18:17:48 GMT

More info. I just did a sdelayer -o describe_long on both CADASTRE_TEST and CADASTRE_TEST1 and the only difference is in the Spatial Index.

For CADASTRE_TEST - Spatial Index: SPIDX_AUTOMATIC, exist: No, array form: 0,0,0
For CADASTRE_TEST1 - Spatial Index: SPIDX_GRID, GRID0=0.029, FULL, exist: Yes, array form: 0.029,0,0

I have some complex spatial views to create and would much prefer to use the 'sdelayer -o register' method. How can I use 'sdelayer -o register' and get the view to use the spatial index?

Thanks,

Re: Spatial View very slow

VinceAngelo — Tue, 20 Nov 2012 18:19:21 GMT

Please contact Tech Support.

- V

Re: Spatial View very slow

ScottSugden — Tue, 20 Nov 2012 20:34:14 GMT

Many thanks for you help.

Re: Spatial View very slow

ScottSugden — Wed, 21 Nov 2012 17:56:46 GMT

For those of you interested the following sde command creates a spatial view with good performance:

sdetable -o create_view -T cadastre_test -t cadastre -c objectid,cadid,lotnumber,sectionnumber,plannumber,planlabel,shape -i sde:oracle11g:xxx -u xxx -p xxx

It is not as nice as 'sdelayer -o register' as that provides more flexibility with complex views, but it is an option.

Re: Spatial View very slow

VinceAngelo — Wed, 21 Nov 2012 18:09:35 GMT

This goes against "best practice" guidelines.

The 'sdetable -o create_view' command should only be used with SDEBINARY or
SDELOB storage layers, where SQL views are not possible. The preferred mechanism
for ST_GEOMETRY or native geometry types is to generate the view with SQL and use
'sdelayer -o register' (or the geodatabase toolbox equivalent).

I haven't seen the issue you describe, but I'll try to reproduce it.

- V