Slow Perfomance of ArcSDE

[PatrickOberli]

Hi

First some background info. I need to write a document about why our Arcgis environment has the performance which it has. I never worked with Arcgis so far...
At least I know how to open some data in Arcmap and I have full access to all involved servers.
First the setup:
- Intel Quadcore Xeon E5420
- 4GB Ram
- Gentoo installation
- SDE93 (I think without patches, sdeversion shows: ArcSDE 9.3  for PostgreSQL Build 508)
- Postgresql 8.3
- 26GB database/Gisdata

I have a sample data, which consists of around 40 "SDE Feature Class" (I guess layers). If I open them in Arcmap it takes some 5 Minutes to show them.

The server shows in this time 100% CPU Load on one core with the process gsrvr and some 10-20% with postgres.

If I export this same data in Arccatalog to files and store them local and open them local, it takes some 15 seconds to show them in Arcmap.

How can I now check why this gsrvr takes so much cpu load, or is this to be expected and fully normal?

Thanks
Pato

[VinceAngelo]

In my experience, when it's an issue, it's always an issue, but it isn't always an issue
(the same data in two different tables behaved differently). 

We deployed with a phantom table:

CREATE VIEW table AS SELECT /* +INDEX(table_alt table_spx) */ * FROM table_alt

even though the SDO_FILTERed query against the 'table_alt' performed a millisecond faster
(9ms to 10ms) in production; the original 'table' (much of the same data) was returning in
43000ms.

- V

[VaL]

Hi
I have a shp file with 300k lines in it - global extent.
It takes ~8 sec to draw in ArcMap.
The same data exists as a FC in SDE oracle (using sdo geometry storage) and it takes ~ 40 sec to draw.
I thought SDE should be faster.
Also I notices that the SDE loads and draws FC in a different pattern, in small rectangles of data whereas the shp files draws in 3 horizontal stripes, say 60 degrees each from north to south.
It seems SDE reads in in very small buffers or something.
Is there a parameter in SDE (dbtune table?) which affects how data is loaded/drawn?
Thanks

[VinceAngelo]

It would be very rare for a full table scan query in a database to outperform a local flat file. 
I've only seen that kind of performance from a DB2 database (which somehow returned 3.7M
point features [with ~1K of attributes] in under four seconds).

It is impossible to control order of presentation out of databases without providing an explicit
ORDER BY clause.  Generally, they will present the data in the order of the driving table or
index, but the optimizer and cache have free will without an ORDER BY (which usually slows
down performance, since the data is copied to TEMP and sorted before return).

Generally speaking, SDO_GEOMETRY will be slower than ST_GEOMETRY or SDELOB/SDEINARY
on a full table scan query, simply because the Esri types use a compression algorithim that
reduces storge significantly -- less data pages == faster transfer.

Keep in mind that ArcSDE only returns the rows the database provides (in the order it provides
them, subject to omission for failure to meet spatial filter criteria), so nothing in ArcSDE tuning
can change SDO_GEOMETRY return order.  You can only change return order of Esri storage
types by specifying an SM_ENVP_BY_GRID search filter (and even that has not been reliable
the last few releases).  ArcSDE also has an optimizer that determines whether to query the
table directly or use the spatial index (the threshold is based on comparison of the envelope
of the search filter to the envelope of the layer, so changing the layer envelope can impact
whether an explicit spatial constraint is applied [ArcSDE will filter geometries in the result
stream either way]).

I usually go out of my way to load data in an order which will permit the fastest possible spatial
search performance, which involves exporting all rows in spatial index order and reloading them
so that spatial fragmentation is kept to a minimum.

- V

[VaL]

It would be very rare for a full table scan query in a database to outperform a local flat file. 
I've only seen that kind of performance from a DB2 database (which somehow returned 3.7M
point features [with ~1K of attributes] in under four seconds).

It is impossible to control order of presentation out of databases without providing an explicit
ORDER BY clause.  Generally, they will present the data in the order of the driving table or
index, but the optimizer and cache have free will without an ORDER BY (which usually slows
down performance, since the data is copied to TEMP and sorted before return).

Generally speaking, SDO_GEOMETRY will be slower than ST_GEOMETRY or SDELOB/SDEINARY
on a full table scan query, simply because the Esri types use a compression algorithim that
reduces storge significantly -- less data pages == faster transfer.

Keep in mind that ArcSDE only returns the rows the database provides (in the order it provides
them, subject to omission for failure to meet spatial filter criteria), so nothing in ArcSDE tuning
can change SDO_GEOMETRY return order.  You can only change return order of Esri storage
types by specifying an SM_ENVP_BY_GRID search filter (and even that has not been reliable
the last few releases).  ArcSDE also has an optimizer that determines whether to query the
table directly or use the spatial index (the threshold is based on comparison of the envelope
of the search filter to the envelope of the layer, so changing the layer envelope can impact
whether an explicit spatial constraint is applied [ArcSDE will filter geometries in the result
stream either way]).

I usually go out of my way to load data in an order which will permit the fastest possible spatial
search performance, which involves exporting all rows in spatial index order and reloading them
so that spatial fragmentation is kept to a minimum.

- V

Thanks Vince,
I just checked and it is even less than 300 000 record in the table. It has 219 000.
I am really puzzled that SDE is not capable to return that number as quick as a shp file. By the way the shp file was on the network, so it wasnt local, but still outperformed SDE by much.
Since it is a global file used for background mainly, waiting for 40 seconds every time you refresh the map is annoying.
Is there any way at all I can remedy this? What is the deal with the spatial query views?

[VinceAngelo]

How large is the shapefile (storage of .shp/.shx/.dbf combined)?

How large is the table (incuding the related LOB table for SDO_GEOMETRY storage)?

Databases have a *lot* more overhead to implement ACID (atomicity, consistency, isolation,
durability) on each query, while a flat file has none.  This is why flat files are very nearly
always faster on a full table scan.  Note that this is not an "ArcSDE performance" issue --
it's a *database* performance issue.

If you want to improve database performance, you can follow this simple rule:
Never draw all objects in large tables

There are many ways to implement this:  You can make the table thinner by generalizing
the geometries of objects with many vertices.  You can make the table shorter by unioning
rows by attribute. You can avoid querying the table by setting a scale dependency in the
client application.  Or some combination of two or three.

Since the data is basemap information you have additional options, including storing the
data in a different storage format, and using a map cache to avoid repeated rendering.

- V

[VaL]

How large is the shapefile (storage of .shp/.shx/.dbf combined)?

How large is the table (incuding the related LOB table for SDO_GEOMETRY storage)?

Databases have a *lot* more overhead to implement ACID (atomicity, consistency, isolation,
durability) on each query, while a flat file has none.  This is why flat files are very nearly
always faster on a full table scan.  Note that this is not an "ArcSDE performance" issue --
it's a *database* performance issue.

If you want to improve database performance, you can follow this simple rule:
Never draw all objects in large tables

There are many ways to implement this:  You can make the table thinner by generalizing
the geometries of objects with many vertices.  You can make the table shorter by unioning
rows by attribute. You can avoid querying the table by setting a scale dependency in the
client application.  Or some combination of two or three.

Since the data is basemap information you have additional options, including storing the
data in a different storage format, and using a map cache to avoid repeated rendering.

- V

Thanks a lot.
The combined size of shpfile is 190 MB.
Size of the table in SDE 130MB. I can see the table for the FC - only one table as SHAPE field is SDO_Geometry. Is there suppossed to abe another LOB table for SDO_geom? If yes how can I find it?

This data represents a very detailed world coasline so I guess all your suggestions for making the table lighter will work. In fact I have set scale dependancies last Friday 🙂

[SamuelAbati]

Same Problem. And I did tests in PostGis with same table, here are my results:

ArcSDE: 25 seconds

Shapefile: 5 seconds

PostGIS: 5 seconds

File Geodatabase: 25 seconds

Seems like a "problem" with Geodabase.

[George_Thompson]

I would create a new thread question in https://community.esri.com/groups/geodatabase?sr=search&searchId=051cc68d-7216-4718-a424-42a740fe3d2...‌ space for this as this original thread is about 6 years old.

I would provide all the information related to client, geodatabase, RDBMS versions and how large is the feature class you are testing, what is the geometry being used, is the data versioned, etc.

[VinceAngelo]

I've never figured out Oracle's naming convention for LOB meta-tables, but anything too
large to fit inline will be stored in an exta table.  Coastlines are difficult because the shapes
can wander quite a distance, making life difficult for the renderer.  I've found that overlaying
a 5 degree grid on the globe and using it to clip all shapes that exceed 5x5 dimensions
significantly improves both query and render performance in most cases.

- V