topic Re: Slow Perfomance of ArcSDE in Data Management Questions

Slow Perfomance of ArcSDE

PatrickOberli — Wed, 16 Feb 2011 09:31:42 GMT

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

Re: Slow Perfomance of ArcSDE

VinceAngelo — Wed, 16 Feb 2011 12:14:22 GMT

"Draw all tables at maximum extent" isn't the fairest of benchmarks available -- It generates
the maximum possible load on the database server, which biases the test toward flat files.

How fast is the network between the client workstation and the Linux server? If it's slower
than gigabit speed (or is burdened with load), then this is another bias toward flat files.

I wouldn't ever expect local flat files to have a longer draw time than an ArcSDE server
in a single-user test, but if you had patched your server and client to at least 9.3sp1,
you'd probably see better ArcSDE performance. 9.3.1sp2 is closer to current, and
would likely give optimal 9.3 performance.

How many feature datasets do you have in the server? ArcGIS 9.x connect time is closely
bound to the number of feature datasets.

Has any tuning ever been performed on the ArcSDE server?

Was any tuning ever done during layer creation to optimize the coordinate referernces?

- V

Re: Slow Perfomance of ArcSDE

PatrickOberli — Wed, 16 Feb 2011 12:29:08 GMT

Thanks for your answer.
The network is 1Gbit/s with low load.

>Has any tuning ever been performed on the ArcSDE server?
I fear yes, it looks currently like it's "overtuned".

I just made my test again. Loading from Arcsde Server takes a little bit over 8 Minutes (and it actually reloads everything again as I zoom or move the map).
Then I exported all the data in Arccatalog to a local drive as a 'Shapefile'. Then I opened this folder in Arccatalog and dragged the same data to a fresh Arcmap map. The loading from the local disk taskes around 15 seconds (compared to over 8 Minutes taking the data from Arcsde).

The local client installation is 9.3.1SP2.

> How many feature datasets do you have in the server? ArcGIS 9.x connect time is closely
bound to the number of feature datasets.
Absolutely no idea, how can I find that out?

> Was any tuning ever done during layer creation to optimize the coordinate referernces?
Also no idea.
Sorry about the lack of information 😞

Re: Slow Perfomance of ArcSDE

VinceAngelo — Wed, 16 Feb 2011 23:48:45 GMT

I generally spend my time tuning data, not ArcSDE. The only parameter I've ever
tuned that improved performance was to increase the transmit buffer size when
working with a satellite networking solution (and it gave the appearance of slower
response to the local network folks).

If you want to see where ArcSDE is spending its time, you can enable the SDETRACE
environment, and run your connection test. Be warned: I did this with a 7500 table
instance with 1250 feature datasets with 9.2sp4 and it took an hour to generate
a 7Gb trace file. Parsing that trace was a major PITA. Be sure to disable the trace
variables as soon as the test completes -- I managed to generate a 64Gb trace once
(would have been larger, but the C: drive ran out of space).

- V

Re: Slow Perfomance of ArcSDE

ForrestJones — Thu, 17 Feb 2011 15:24:50 GMT

Hi Pato,

One more thing to check is if the data in ArcSDE has a spatial index or not. A slow draw time when zooming or querying can be caused by not having a spatial index. To check just right-click the featureclass in ArcCatalog and choose properties. Then go to the "Indexes" tab. Then check the section for the spatial index.

Re: Slow Perfomance of ArcSDE

WilcoLoth — Fri, 04 Mar 2011 13:29:37 GMT

Hi all!

Interesting topic! We're currently experiencing the same kind of problems when selecting data; selection is done quite fast, but the time to draw the actual selection takes an inexplicable long time!

Specs:

Intel Xeon X5450
4GB
MS Server 2008
Gigabit
ArcServer 9.3.1 SP1
Oracle Spatial (10G client)

Client machines running ArcGIS 9.3.1

We're connecting throught the Direct Connect-connection and we've also tweaked the register of a client machine (followed the steps mentioned in this post: http://resources.arcgis.com/content/kbase?fa=articleShow&d=22668).

We're really in the dark why the perfomance is sluggish; we're not using feature datasets at all since they tend to have anegative effect on overall database performance.

Any suggestions?

Re: Slow Perfomance of ArcSDE

VinceAngelo — Fri, 04 Mar 2011 13:49:11 GMT

Which Oracle release are you using? (10.2.0.?) What geometry storage are you using?

I was recently introduced to an optimizer issue that affects SDO_GEOMETRY in
Oracle 10.2.0.5-11.2.0.2. There are drastic and less drastic solutions avaliable.

- V

Re: Slow Perfomance of ArcSDE

WilcoLoth — Mon, 07 Mar 2011 05:21:11 GMT

Which Oracle release are you using? (10.2.0.?) What geometry storage are you using?

I was recently introduced to an optimizer issue that affects SDO_GEOMETRY in
Oracle 10.2.0.5-11.2.0.2. There are drastic and less drastic solutions avaliable.

- V

Dear vangelo,

Thanks for your reply!

We are indeed running Oracle 10.2.0 and all the feature classes are stored in SDO_GEOMETRY.

I'm curious to your drastic & less drastic solutions...

Greetings from The Netherlands,

Wilco

Re: Slow Perfomance of ArcSDE

VinceAngelo — Mon, 07 Mar 2011 13:40:49 GMT

The issue I ran into is described in Metalink note 1268383.1. We're still working toward
a solution but I've found several interesting features:
1) It seems to be intermittent
2) I couldn't reproduce the issue in an instance where Oracle Spatial was not installed
(Esri only uses the Locator capabilities available in Intermedia)
3) We were able to use a view on the original table with an optimizer hint to get spatial-
first queries that didn't result in full table scans.

In light of the above, the option to disassociate statistics with spatial queries seems
quite drastic indeed.

- V

Re: Slow Perfomance of ArcSDE

WilcoLoth — Fri, 11 Mar 2011 14:13:06 GMT

@ Vangelo,

Thanks again for your reply!

The problem doesn't seem to be intermittent: it's actually always an issue. This afternoon I've loaded the 2 feature classes on which we've first encountered the problem in our test-DB and stored them in SDEBINARY.

The issue of extremely slow performance when displaying\visualizing a selection doesn't exist at all, which rules out any problems with the database. The problem is we're actually "stuck" with the SDO_GEOMETRY-storage option...

I'm gonna make a support call, though I'm a fraid there won't be an easy solution.

I'm gonna try and locate your Metalink note 1268383.1.

Thanks again & have a great weekend,

Wilco

Re: Slow Perfomance of ArcSDE

VinceAngelo — Fri, 11 Mar 2011 15:27:32 GMT

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

Re: Slow Perfomance of ArcSDE

VaL — Fri, 01 Jul 2011 13:50:10 GMT

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

Re: Slow Perfomance of ArcSDE

VinceAngelo — Fri, 01 Jul 2011 14:32:52 GMT

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

Re: Slow Perfomance of ArcSDE

VaL — Mon, 04 Jul 2011 09:27:46 GMT

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?

Re: Slow Perfomance of ArcSDE

VinceAngelo — Mon, 04 Jul 2011 12:40:36 GMT

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

Re: Slow Perfomance of ArcSDE

VaL — Mon, 04 Jul 2011 13:41:42 GMT

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 🙂

Re: Slow Perfomance of ArcSDE

VinceAngelo — Mon, 04 Jul 2011 14:26:16 GMT

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

Re: Slow Perfomance of ArcSDE

SamuelAbati — Mon, 14 Aug 2017 14:38:59 GMT

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.

Re: Slow Perfomance of ArcSDE

George_Thompson — Mon, 14 Aug 2017 14:44:43 GMT

I would create a new thread question in https://community.esri.com/groups/geodatabase?sr=search&searchId=051cc68d-7216-4718-a424-42a740fe3d25&searchIndex=0‌ 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.