All About that (Move to) Base…

… ‘bout that base, ‘bout that base. OK, yes we know in the song it’s “.. about that bass”. But today, with apologies to Meghan Trainor (who by the way my 8 year old granddaughter thinks is just the bomb) we’re going to talk about using the “Move to Base” option when registering a Geodatabase dataset as versioned.

We’ve heard from Esri that using this option can, in some cases improve display performance for layers in a versioned Geodatabase – and we’re definitely all about improving display performance. We also know that this option isn’t available for all classes, only the simple ones. For example, you can’t register with “move to base” classes in a geometric network. Here’s the full low down on what’s in and what’s out.

Thing is, we’ve heard of all kinds of approaches that “improve display performance.” Call us skeptical, but we thought we’d try this out with some tests. To be sure these were done in a “clinical” (non-production) environment. But putting some number on the thing might be helpful.

So, here’s what we did, starting with the background.

The Background

What does “move to base” mean anyway, and why would it help my display performance? Well, we need to back to the basics of how Geodatabase versioning works… but try to do it briefly. (If this is old hat please skip ahead.)

When we create our first version in a newly versioned Geodatabase that version is exactly the same as its parent, DEFAULT. And as illustrated below, any table registered as versioned in this Geodatabase has a “base” table and associated “a(dds)” and “d(eletes)” tables that hold differences between itself and its parent. Though of course when first created these “delta” tables have no records. Since they have no records an ArcGIS query on the table can be very simple, something like “SELECT * FROM MyTable”.

However, there’s no point in creating a version and not performing some edits. To illustrate we’ll add 10 records to the MyTable class in a single edit operation and save it. Results in our version table are shown below. The new records appear in our Adds table and the version “State” is progressed from 0 to 1. Now an ArcGIS query on MyTable requires a more complex query to account for records in the delta tables, something like “Select everything in the base table plus what’s in the adds table that matches the version state minus everything in the deletes table that matches the version state.” This query can get quite complex. And in general the more complex it becomes the slower our performance gets.

Now, say we’re done with edits and we reconcile/post our version to its parent. In this case the operation is pretty simple. The reconcile moved records from the parent into the target MyVersion and the post moved records from MyVersion in the parent – so now our version is again the same as DEFAULT . But the way this was accomplished was by setting the state of DEFAULT to the state of the parent. We still have rows in our delta table and the query on the table is the same as before we did our reconcile/post. And will stay this way until our database is compressed. Its only on compress that records are actually moved from the delta tables to the base table.

Finally, let’s compare the above results to the same reconcile/post done if our class was registered with the “Move to Base” option. In this case, just as the name implies, our edits were moved to the base table on reconcile/post – not waiting till a database compress is performed. And after completion the ArcGIS query again becomes a simple one. Since no records need be retrieved from the delta tables a query on the table can basically be “SELECT * from MyTable”.

Our Hypothesis

Given two classes identical in data structure, one registered as versioned with the “Move To Base” option and the other not and given the same number of edits in the same number of versions, after reconciling/posting all versions to DEFAULT class registered with “Move To Base” will display faster than the one not.

Our Tests

To test out hypothesis we created two polygon feature classes, both at the Geodatabase level (not in a feature dataset). One, named MOVETOBASE, was registered as versioned with the “Move To Base” option and the other, named NOTMOVETOBASE was registered as versioned without this option.

Scripted steps from there were as follows:

1. Create a set of versions, each a child of DEFAULT
2. Within each version create the same number rectangular features at random locations in each feature class
3. Reconcile/post all versions to DEFAULT
4. Use Esri’s PerfQAnalzer to evaluate display performance of both classes when pointed to DEFAULT

In the first iteration of tests we created 50 versions with 100 features per version – thus a total of 5,000 features. Below are the initial PerfQAnalyser results. MOVETOBASE display was about 30 times faster than NOTMOVETOBASE. and note that this was not a fluke, repeated display tests showed similar results. While this scenario may not be entirely representative of a typical work flow, it’s clear that there can be an advantage to using the “Move to Base” option.

To possibly flatten things a bit we then performed an “Analyze” on both datasets to recalculate database statistics. Creating 5,000 features in a table could certainly change the way the Oracle optimizer chooses to scan these tables. Results are below, and not much different.

Finally (or so we thought), we compressed the Geodatabase thinking that at this point all edits from the NOTMOVETOBASE table would as part of the compress operation be moved out of the delta tables and into the base table and our display performance would be roughly equivalent. But not quite yet. We were a little surprised the results below that show performance after the compress was still in the same ballpark as after the compress.

Finally (this time for real), we reconciled an posted all versions with DEFAULT and then performed a compress. Now we get roughly equivalent display and fetch times. Took a little while to get there though.

Results and Summary

At least in our clinical environment we’ve proved our hypothesis – using “Move To Base” certainly can improve display performance. Granted that our test case was probably somewhat extreme. However, in other tests with smaller parameters we saw proportional results.

Whether you can apply this configuration will depend details of your work flow, though features registered with “Move To Base” can be operated on in the same version as other versioned classes. And the configuration can be applied to classes that are ArcFM-ified as well as simple Esri classes. So, making the change may be no trouble… or as Meghan Trainor would say “no treble!”.