ArcSDE feature class vs File geodatabase feature class on Performance

[JohnMojapelo]

Hello Everyone

Has anyone came  across a situation whereby a maximum of 2000 records - on attributes table can be shown at the time; zoom, pan, select, etc. in the same ArcMap session? Well this happened mostly when using personal geodatabase (usually from local C drive) and ArcSDE (usually from the network) feature classes - (I hardly use shapefiles anymore). Scrawling beyond that current 2000 records is as slow as doing zoom and the likes on such feature class. However, the same feature class under file geodatabase would out play the rest.
The above scenario compelled me to follow documentation and various sources to check if I can get a better route to avoid slower response factor of either  personal geodatabase (usually from local C drive) and ArcSDE (usually from the network) feature class.
Speaking under correction, it seems to me that ESRI ArcSDE technology is heavily if not entirely dependent on third party Relational Database Management System (RDMS) for performance - be it Oracle, Microsoft SQL, etc. While considering the effort and expertise provided by the RDBMS engineers/ vendors, I would like to recommend that ESRI do something more on the aspect of ArcSDE Performance as the scenario outlined. For example, it has been dwelling on my imagination, a situation whereby the whole file geodatabase concept is incorporated in ArcSDE environment - moreover to enhance feature class performance and improve data structure. In that situation, RDBMS would be providing their security capabilities and other DBA tools.

I tried to put this post under ArcGIS Ideas but could not find ways...it happens when I am burning from an idea.

I hope someone will find this clear.

Regards

Johnny

[TrevorWeiland]

Talking with the server guys as the UC last year, they harped on the performance gain by using a FGDB as the database backend for AGS web services- apparently, the xml/folder structure is really fast. so what you're finding doesn't surprise me.  The FGDB can also hold a ton of data (max is 256 TB / FC ).

However, with a FGDB instead of a RDBMS you lose a bunch of capabilities: multi-user editing, versioning, materialized views, triggers, etc in addition to the security you mentioned.  My solution is to update most of my FCs to a reprojected FGDB stored locally on the AGS server for FCs that don't update very often and that I don't need to edit. 

I use python to delete all rows in the local FGDB FC and then append all rows from source SDE FC to th FGDB FC.  I schedule the python scripts based on the FC update frequency.  I would love to do true replication but the setup for that is more trouble than its worth.

[MichaelVolz]

Does the performance gain from a file geodatabase as the source for a ags mapservice, over an SDE source, that you have experienced occur for both dynamic and cached services?

[TrevorWeiland]

yes, however with a cached service you only see the performance gain during the caching sequence.

[MichaelVolz]

Trevor:

My organization recently upgraded from ArcGIS v9.3.1 to v10.0, where the servers went from Windows Server 2003 R2 to Windows Server 2008 R2.  The cache creation times for the county wide datasets that I work on went from about 16 hours to 1.5 hours. 

At ArcGIS v9.3.1 I had the best cache creation performance with only 1 instance (Extensive testing that disagreed with ESRI's suggestion of n+1 instances where n = number of server processors).

At ArcGIS v10.0 I went with the n+1 instances and have this great performance, but I'm using file geodatabases as the source of the data.  How much more time do you think it would take to create the cache if I went with SDE as the data source (I currently update the file geodatabase weekly from SDE where the data is edited in a multi-user environment)?

I ask this because it would be good if I could possibly eliminate the step of having to update the file geodatabase and go straight from SDE.  The only drawback would be that I would be hitting the SDE database from the AGS mapservices if I perform queries that need to go back to the original data source instead of just viewing the cache tiles.

[TrevorWeiland]

I don't remember the AGS Team's breakdown of performance improvement except that a local FGDB was the fastest and that SDE on Oracle was slow.  I do remember that there were big differences between SDE implementations (different RDBMS, tuning, etc).  They're suggesting was to use a replicated FGDB and sync the changes regularly but for my purposes, I decided to delete all the feature in the FGDB FC and append all the features from the SDE FC so that I could avoid setting up replication (more cumbersome than I'd like) and so that I could more easily change projections (web mercator).  I'm not caching now, just not worth it for my current needs but in my last job I was and I fought with the n+1 issue on 9.3.1 for a long time but ultimately found that the configuration of the MXD had far more impact on cache creation time than any other factor- a over complex service was my bain and took a couple of months to cache.