1

My company develops a spatial planning solution since 2009. Initially it was an ArcMap application to create and maintain spatial plans (competing with AutoCAD folks) but recently we developed "publish them to web" functionality.

Our SDE database design is based on "templating". There is a feature dataset that acts as a template for a new spatial plan. Once a new spatial plan is created we clone that feature dataset into a new feature dataset that will act as a new spatial plan. There are about 10 feature classes (polygons, lines, points, annotations) and 6 relations in the plan feature dataset.

Thus we end up with a separate feature dataset for each spatial plan. This design also helps us to solve juridical issues who can see a spatial plan and when: we set different privileges on plan feature dataset as plan goes through its status cycle (from being designed to official).

During last two years our solution became popular in target market. And here is the problem: customers with 1000 spatial plans say they want to enter 2000 more in one year. This is not a problem for our marketing and sales departments :)

But for development the problem is here: 1000 feature datasets in SDE database (SQL server or Oracle) are hell slow! Say, it takes 5 minutes to open them in ArcCatalog... Upgrading geodatabase to our next version (topologies, representations)? Leave it for the night...

People from my company did not get answer to this question during this year summit.

Do you have experience with such designs? How did you solve the performance issues?

  • 2
    Could you please edit this question to specify your defininion of "dataset"? For some, it means "user" and others it means "database" and still others it means "user-schema geodatabase". We could also better answer if you specified the number of tables associate with each "dataset" (and whether they are standalone feature classes or distributed in feature datasets, and if so, in how many FDS) – Vince Apr 29 '15 at 18:47
  • @Vince, I edited it. I meant feature dataset. Thanks for comment. – Remigijus Pankevičius Apr 29 '15 at 19:02
3

While it's true that you've implemented the worst possible case of feature dataset (mis)use, I've worked with customers with more feature datasets than that (2000), and Oracle upgrade took about five minutes, so I suspect you're looking in the wrong place for optimization.

Some of the things you can do:

  • Make sure your database is optimally configured, and running on a physical host (not VM) with local (fibre-attached) disk
  • Distribute your datasets among more users, which don't have reciprocal SELECT access (fewer tables to symbolize at connect == shorter connection time)
  • Eliminate suboptimal feature dataset use pattern (FDS should only be used for cooperative editing tasks, not for storage "folders")

In the end, having ten to twenty thousand tables is always going to be worst-case in terms of database management (especially with only one user), but to reduce the table count would require a scalability review of your application.

  • Thanks, I'm convinced that the current design will lead us to nowhere; DBMS with tens thousand tables will definitely lag. It's time for a fresh view. – Remigijus Pankevičius May 4 '15 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.