Multiple GDBs vs Single GDB

[mpboyle]

Our production enterprise system is currently 1 sde gdb (Sql Server 2014) with several (45 to be exact) datasets and 9 editable versions (based on department). The sheer number of datasets makes it a little cumbersome for users to find what they may be searching for.

I was wondering if anyone has tried breaking down a large sde gdb into several smaller gdbs, perhaps 1 sde gdb per department?  If so, is there is a performance advantage of having 1 large sde gdb versus several smaller sde gdbs?  Is response time better for smaller gdb versus larger gdbs?  Would reducing the number of versions have a benefit on performance?

I'm hoping to get some pros / cons of having 1 large sde gdb which is like a one-stop-shop for any GIS needs or several smaller sde gdbs meant for departmental needs.

Thanks!

[mpboyle]

Vince Angelo​,

That's interesting about the datasets.  Would you recommend not using datasets unless complex functions (i.e. topology, geometric networks) are needed?

[VinceAngelo]

Yes.  It would be difficult to find a geodatabase SME who would recommend otherwise.

- V

[MasaoMatsuoka]

Vince,

I am a bit confused about the use of datasets as folders. The ESRI's local government data model seems to use datasets as folders. Our database was modeled after the local government model, so we do the same. Is there a reason why the local government model is using datasets this way?

Masao

[VinceAngelo]

You'd have to ask the folks that designed that model their reasoning.

[Anonymous User]

Hi Vince, Most shops I've seen definitely use datasets as folders. Dumb question, but is there a reason there is no concept of a 'folder' in SDE? In fact I wish there was infinite subfolder hierarchy possible, like with a normal file system. I am our DBA but not really a DBA so I wonder, why not??? If there are engineering hurdles, re-factor. Is it a MS SQL  et al limitation??  What performance penalty does a FDS incur under the hood and why, in a nutshell?

[DougBrowning]

We used to but moved away form it mostly since tables cannot be in datasets so it all falls apart.  Also Esri advised not to use datasets anymore.  When we asked how to organize they said why would you have users digging in your raw SDE.  Decent point.  They said use layer files....but layer files also do not work with tables.  So around it goes.....  Ended up using something like Dept_ at the beginning.  BTW same issue with Hosted Feature services... no way to add tables in to ArcMap.

[VinceAngelo]

The Enterprise geodatabase model (there is no SDE, only Zool) was originally based on the ODBC model, which only supports tables. No RDBMS supports "folders," it's just database.schema.tablename, so any folder organization would have to be implemented in metadata, which may not be standardized across clients (non-geodatabase, JDBC, ODBC,...), and then you have nightmares like dataset corruption because SQL was used to DROP a table and the metadata was not scrubbed (this is the reason that command-line ArcSDE tools were eliminated after 10.2 -- too much data corruption "using Esri tools").

99.9% of the time that customers complain about locks interfering with schema change operations, it's because they didn't realize that placing 40 feature classes in a feature dataset meant that any use of any of the FCs generates READ locks on all of the FCs in the FDS.  So yes, there's a performance penalty, but more importantly there's a functional complexity penalty.  You can't get simpler than a list of table names out of the system catalog.  The price is paid for a topology because it's worth what you gain, but I find one level of folder organization to be a paltry gain for what it costs.

- V

[Anonymous User]

RDBMS itself does not have folders, true... but I'm wondering if Arc Catalog could evolve to present 'folders' to the user in the GUI?  That would be nice.  That is interesting about the locks and good to know. So, that aside, are you saying that FDS also incur performance penalty in day-to-day use, like for reading, editing etc CRUD operations? Thank you Vince Angelo‌ for your thorough answers as always!

[VinceAngelo]

There's certainly room for an enhancement request along those lines, though I'd do a thorough search for an existing Idea first (Update: And here it is -- I suspect Eric's point about unique names across folders dooms this as an enhancement).

I don't have metrics on the performance costs of transactions on Feature Dataset members, but I do know there is more overhead, at least in the initialization and tear-down phases.  The impact is leavened by whether you do one operation or 80 thousand.

- V

[Anonymous User]

Hi Vince, that's quite helpful. Hm, we do connect to the read-only SDE with FDSs for services, and there could be perhaps multiple reads per second at most. Thank you again for the detail.  I will say, despite the mechanics, the ability to organize with folders and subfolders would be extremely useful for a GUI for end users from Desktop, Pro, Catalog etc. We have nearly one hundred full ArcProduct users in our county connecting to various SDEs.  As in the Idea, the relation between what layers are in what folder could be tracked either in database system tables and/or locally, in a config file in Catalog. (best option, track in the database; interesting option of alternatively 'grouping' files into folders on the fly locally in a config file in Catalog; I suppose to let users make their own 'folders' or 'views' so to speak, for only them. Kind of like letting users group their own layers in a Layer List on the fly in a web map viewer.)  This is one of those things, like Locators, where the mechanics are perhaps complicated although the concept is not.  End Users want folders. So we end up using FDS at least sometimes when the pros outweigh the cons.  We also use 'prefixes' like WATER_Manhole; WATER_lateral and usually subprefixes as well, such as by jurisdiction like SAV_WATER_Manhole; SAV_WATER_Lateral.  Thank you again for the background detail and I will keep this in mind, for FDS where there might be a very high operation load.  I'll let this thread get on track and thank you for delving in to this!