ArcSDE Performance Large Databases

[JustinShepard]

I'm looking for stories from anyone that has implemented ArcSDE to handle large amounts of data (especially relating spatial features to non-spatial tables), the larger the better. ArcSDE sits in an enterprise database so I'm not expecting there to be too many issues that arise from using ArcSDE as opposed to the straight database (Oracle or SQL Server).

Please let me know what you've done and how it worked out (how big is the database you're working with, any performance issues, did you synch it with a backup database, use ArcGIS Server with it, run ArcGIS Mobile for editing/updating data, etc.). I'm not putting what we're using in the post because it's pretty much a blank slate at this point.

If anyone from ESRI can point me towards a success stories section about ArcSDE that would be great too.

[VinceAngelo]

There are just too many variables to get a useful response from this query. I've had customers
with hundreds of millions of rows of data with both great and terrible performance. It's probably
not even safe to assume that an enterprise class database server can handle the load, because
the way you organize the data and the way you query it can have a huge impact on the actual
load and query performance of large and very large databases.

If you can provide details on what you're trying to accomplish, we can provide guidance on
what issues you might run into, and how to manage them.

- V

[JustinShepard]

Basically we're looking at setting up an ArcSDE for national data (I don't think I can give more specifics than that) and some people are questioning if ArcSDE can handle it... they are more accustomed to using straight Oracle and SQL Server than loading ArcSDE on top of it; so the database structure should be solid. They are just looking to see if ArcSDE has a performace limit. If we can find any examples of those instances were someone was managing 'hundreds of millions of records' it would help our justification.

Thanks.

[VinceAngelo]

ArcSDE isn't really loaded "on top of" any database -- it's just the wrapper that standardizes
geometry delivery to clients.   At least, that's the way it should be -- most of the failed projects
I've worked were detemined to partition away the spatial component so it wouldn't impact
their design, and failed because they couldn't commit to using a spatial-enabled database.

There is no performance limit to ArcSDE, but there are ugly performance limits to databases
when they are used to store spatial data inefficiently.  Ironically, the folks who know the most
about large databases usually fare the worst when they add a spatial component without letting
the reality of spatial fragmentation impact their design.

All my large database customers have had non-disclosure clauses in their contacts, so I can't
provide details.  I can say that when I refer to hundreds of millions of rows, I'm referring to
inidividual tables -- Ten thousand tables with tens of thousands of rows is a very different problem.

- V

[JustinShepard]

Thanks for the feedback.
If I have more questions can I contact you outside of the forum somehow?

[VinceAngelo]

With database support for ST_GEOMETRY and/or native spatial objects, geometries are just
another attribute type. It is a potentially disastrous design flaw to split your business table
into spatial and non-spatial components and join the tables during every query.

The biggest performance risk for large spatial tables is spatial fragmentation. This occurs when
features are unorganized (or organized by something other than geo-location, like timestamp),
producing what essentially becomes a full table scan to access all the features in a small
geographic region. The hallmark of spatially fragmented data is when rendering produces a
random-seeming draw order, vice filling from one side of the field of view to another. There
is no silver bullet to solve spatial fragmentation, but there are techniques available to moderate
its influence.

The best way to get non-Forums access to my time is through your marketing representitive.
I am fully allocated to project work at the moment, but there are others within Esri who also
have practical experience with large databases, and a marketing rep might be able to organize
a virtual consultant from varied resources.

- V