ArcCatlog needs 2 miniutes to connect to sde and list all the featuredatasets

[JeffYang]

Hi:

    We have a sde 10.0 running on DB2.  There are about 4000 featureclasses in the sde, including point,polyline,polygon,annotation and mosaic dataset.  Some annotation featureclass has 2 or 3 million records. We use direct connection to DB2. Now we have a problem.

    When we double click the database connection in ArcCatlog, it needs more than 2 minutes to list all the featuredatasets. During these two minutes, ArcCatlog has no response. All the employee using the ArcCatlog are complaining. Besides, when we need to add data in ArcMap, it will take the same time which is several minutes to add the selected featureclass to the ArcMap. After we add the featureclass to the arcmap, the drawing speed is OK.

    What is ArcCatlog doing when it make the connection the database? Does anyone have experience such kind of situation before?  Is it possible to store thousands of featureclass in the ArcSDE? Should we modify some configuration parameter in the DB2?

     Any comments would be appreciated. Thank you .

[VinceAngelo]

Are the 4000 tables owned by the same user?  How many of them are in feature
datasets?  How many total feature datasets do you have?  Are all the feature
classes which are in feature datasets edited as if they are one composite feature
class?

- V

[JeffYang]

Hi,Angelo:

   Thank you very much for your attention to this thread.

   All of the 4000 tables are owned by the default user "sde". There are about 60 featuredatasets, each datasets contains about 5 to 50 featureclasses. There are about 150 tables, they do not belong to any datasets.

  Most of the featureclass are simple features: point, polyline and polygon. There are about 100 annotations.

  We will edit these featureclasses, but we chose to merge the edits into the base table.There are only one version "default" in the sde.

  Currently, it's very very slow  in connectting and listing all the featuredatasets. But after the data added to the Arcmap, view the data is not slow.

  I am wondering when the ArcCatlong connect to the DB2, it seems try to get a lot information from DB2. We did not do anything to the SDE's system table. Should we add some kind of index to some system table like "Layers"?

[VinceAngelo · Accepted solution]

You've got a number of things wrong here, the first of which is that the SDE user
should NEVER, EVER own spatial data -- Best practice it to create at least one,
usually several, owners, and additional accounts for data users.   The SDE user
password should be closely held to prevent geodatabase corruption.

Feature datasets exist for cooperative editing.  If you're not editing these feature
classes together (as in pipes and values), then placing them inside feature datasets
slows down access (at connect time) and causes locking issues that will haunt you.
You can use owners and table naming to organize feature classes without falling
into the trap of using feature datasets as "folders".

You should not tamper with the indexes on SDE metadata tables.  They already
have all the indexes they need -- adding others will just cause difficulty at upgrade.

- V

[JeffYang]

Hi,Angelo:

    Very preciated for your fast response.

    It seems that we do have felled into the trap: use the featuredatasets as folders to group data. We do have got "the data has been locked"   as you described when we try to do some modification.

    We will try to so some research and use the owners and table naming to organize feature classes as you suggested. A lot work to be done.

    We are not fimilar with the different owners for spatial data, but will let you know the results.

    I think your reply if very very useful to me and other users in the forum. I guess a lot of people may use the featuredatasets as folders.

   Thanks again.

[MarcoBoeringa]

I guess a lot of people may use the featuredatasets as folders.

Unfortunately, very true. This issue is partly caused by the fact that ESRI never implemented some kind of "virtual folder" structure for geodatabases, that could serve as a replacement for Feature Datasets being used as surrogate "folders". Using Feature Datasets as "folders" is not a problem in all cases, but it can cause issues.

[LeoDonahue]

his issue is partly caused by the fact that ESRI never implemented some  kind of "virtual folder" structure for geodatabases, that could serve as  a replacement for Feature Datasets being used as surrogate "folders"

I wouldn't say it's ESRI's fault.  We never had this problem with shapefiles or coverages.  Grouping related data took place on the file system in those days.

If I recall, there was a time when the general understanding of the term feature dataset was understood as a way to simply group related datasets... with the intention of building a topology, network dataset, terrain, etc.  The last part was always forgotten.

In fact, I remember interview questions from years ago that asked me about ways to group data in a geodatabase.

And.. even today, you see it almost everywhere when people create one directory and shove dozens of file geodatabases into that single directory.  As soon as a user hits that top level directory, you wait and wait and wait.. for the dozens of FGDBS to load before you can even click on one of them to see what's in it.  And that is most likely related to bad habits that carried over from ArcView 3.x.  Remember when you would browse to a network share and there were hundreds of shapefiles in that one location.  The blue progress line... have we forgotten?