7

In our SDE database, the SDE_state_lineages table has around 200,000 records, the SDE_states has around 900, and our SDE_versions table has around 1,300 records.

We've been having performance issues lately. The layers are very slow to draw. We compress daily, but we aren't able to reconcile the existing versions.

Is this high number of records in the SDE_state_lineages causing poor performance?

  • 1
    Short answer is Yes. Each time someone requests data each of the states needs to be polled as well to see if changes have been made. I think it's time to compress your database! Are you editing a lot on this data? – Michael Stimson Aug 4 '14 at 1:57
  • What do you mean each time someone requests data each of the states needs to be polled? – geogeogeo Aug 4 '14 at 6:44
  • The data in the table is the base, when requests for data are made the base is read and then each of the states in turn is interrogated (polled) to see if it has an update of any of the rows in the adds & deletes tables, the more states the longer requests for data take; it is fractions of a second for each state but obviously the more states the longer it takes. That is my understanding of the database side of SDE. Why is it that you can't reconcile the versions? – Michael Stimson Aug 4 '14 at 21:42
  • Because each version is a workorder waiting to be built in the real world and they would each have conflicts that need to be resolved, so it can't be automated. It's a real dilemma. As far as the base and state tables. It would only need to query the states that are attached to an individual version, correct? So, if I'm in version A, it will only need to query states and state_lineages that are specific to version A and ignore the other records? Or does just having that large amount of states slow the query down? – geogeogeo Aug 5 '14 at 1:37
  • Spot on, but there's no way to tell which version the state belongs to - at least as far as I can tell, I don't like poking around inside the database any more than necessary in case I break something. To help speed this up you can prompt the database to cache frequently read tables so it looks for it in RAM and only reads from disc if it's modified. Refer to the Esri database tuning guide for your underlying database. The one I read for PostgreSQL helped our database a lot! – Michael Stimson Aug 5 '14 at 1:47
7

The answer is yes that the number of rows in the sde_state_lineages table directly impacts performance of the geodatabase. 200k rows is not considered "alot" but that is relative to your available resources and assuming there are no versioning issues that would require a diagnose/repair. Continue to compress often.

From your naming conventions it looks like you're using SQL Server as your underlying DBMS. The following articles apply to all RDBMS platforms but the queries are for Oracle. They can be adjusted for SQL Server, the same architecture applies. I've done so for the first one.

The official reason is:

When ArcGIS queries a versioned table for any given version, the version's lineage is used to derive the table's versioned representation (by joining the versioned table's adds, deletes and sde.state_lineages table). Performance can be impacted by the length of the version's lineage, because of the number of states that must be joined between the sde.state_lineages table and the delta tables.

HowTo: Report the length of a version lineage in Oracle http://support.esri.com/fr/knowledgebase/techarticles/detail/35500

Here's the query from that page rewritten for SQL Server:

SELECT v.owner +'.'+v.name "VERSION NAME", COUNT(sl.lineage_id) "LINEAGE LENGTH"
FROM sde.sde_states s, sde.sde_state_lineages sl, sde.sde_versions v
WHERE s.lineage_name = sl.lineage_name
AND sl.lineage_id <= s.state_id
AND v.state_id = s.state_id
GROUP BY v.owner, v.name, sl.lineage_name
ORDER BY "LINEAGE LENGTH";

This will tell you exactly how many states each version's lineage contains. This applies to transactional versions as well as replica system versions. You can also adjust this query to find versions that point to the same state as other versions; see #1.

Key Points

  • Versions

From your provided state count of 900 and version count of 1300, and your statement that you never reconcile, you have 400 versions that have never been edited. A version is a label that points to a state, and the only time multiple versions point to the same state is right after they have been reconciled or right after they have been created. I would look into removing those if your work order system allows it, as ArcGIS will scan the versions table for many functions. Your version count of 1300 could be more expensive than your state lineage count of 200k; it depends on what you're doing when you notice a performance hit.

  • Indexes

Maintain the indexes on the sde_state_lineages table, which in SQL Server are state_lineages_pk and lineage_id_idx2. The majority of the queries to ArcSDE will perform a full or range index scan. Keeping these indexes up to date after your daily compress operations may assist performance. Here is an article on the topic:

SDE.STATE_LINEAGES table index growth http://support.esri.com/en/knowledgebase/techarticles/detail/20596

  • State Identification

You can also query which version a state belongs to as well as how many states belong to a specific object edited by a specific version, using the following article:

HowTo: Discover the number of rows in the adds table for the DEFAULT version's lineage for a specific object class

http://support.esri.com/fr/knowledgebase/techarticles/detail/35030

  • Tracking objects by version & delta table

-Here's a query for SQL Server that will give you the current state id of each versioned object along with what version is responsible and what delta table it can be found in:

SELECT v.owner version_owner,
         v.name version_name,
         v.state_id,
         mm.state_id as modified_id,
         mm.registration_id,
         tr.owner layer_owner,
         TR.TABLE_NAME layer_name
FROM sde.sde_versions v
         FULL JOIN sde.sde_mvtables_modified mm ON (v.state_id = MM.STATE_ID)
         LEFT JOIN sde.sde_table_registry tr
            ON (tr.registration_id = mm.registration_id)
ORDER BY tr.owner, TR.TABLE_NAME;

Hope this helps.

| improve this answer | |
  • So what would a lineage length of 399 mean? – geogeogeo Sep 11 '14 at 21:29
  • A lineage length of 399 indicates 399 states participate in that lineage. If this is after a compress operation, this indicates there are 399 states that cannot be compressed together to collapse that lineage without affecting the versioned table structure. A likely cause of this in your scenario is that there are versions that directly reference those states. You can see what state a version currently points to in the sde_versions table. – ORA-55378 Sep 11 '14 at 22:54
  • hey I just saw this and actually made another qestion here: gis.stackexchange.com/questions/113581/… – geogeogeo Sep 11 '14 at 23:01
  • Awesome answer! Wrapping up those code snippets into a SQL agent job or a SSRS report that shows the lineage stats daily, or fires an email when they pass a certain value, would make for a great maintenance plan! – tpcolson Feb 3 '16 at 14:31
  • That's great. It's really helping me understand how the SQL works. One thing I am trying to figure out is how to determine the row count of each of the adds and deletes tables that is holding changes. I found this: github.com/Esri/developer-support/blob/master/arcsde-sql/oracle/… but it is in Oracle, and I'm using SQL. I'm new to SQl so I can't quite figure out how to loop it through the tables and concatenate the table number to run a count on the table. – user72873 May 9 '16 at 23:12

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.