topic Re: Non-versioned data slowing way down in Data Management Questions

Non-versioned data slowing way down

MattLane — Mon, 21 Apr 2014 13:21:37 GMT

I have two unversioned featureclasses in a dataset that our organization adds and deletes up to 10s of thousands of records daily. One is over 3million points and the other is over 500k lines. Each have an organizational ID field with an index and a GlobalID field with an index. We will be versioning and replicating these in the near future. They are getting slower and slower over time, to the point where just deleting a couple records could take 8 minutes and getting a table feature count using the toolbox tool takes 3 minutes. The processing that used to take 1 to 2 hours now takes over 12.

Details:

SQL Server 2008 R2

SDE Release 101010

Desktop 10.1 sp1

Each spatial index is built by ESRI (16 cells per object, Medium on all levels)

Data is not in SDE schema, but a separate one

There isn't a lot of other data in this database, although there is a single versioned featureclass in a different dataset that has topology rules applied to it.

What I've tried:

I rebuild and reorganize all indexes in this database as needed according to Microsoft documentation

Checked the Bounding Boxes on the spatial indexes

Rebuilt statistics (esri tools)

Compressed

Shrunk database (our DBA does this periodically)

Restarted server (memory doesn't seem to be the issue)

Re: Non-versioned data slowing way down

MattLane — Mon, 21 Apr 2014 13:22:58 GMT

This has happened in the past and the only way I could find to fix it was to export and recreate the two layers completely. That worked well for about 4 months. This option won't be desirable once we start replicating daily to other agencies.

Re: Non-versioned data slowing way down

WilliamCraft — Mon, 21 Apr 2014 15:12:07 GMT

How are those features being added and deleted? Is this being done inside an edit session or outside an edit session? If you're deleting and adding tens of thousands of features on a daily basis, you might want to consider re-building the spatial indexes on those feature classes accordingly each time the number of records changes drastically. The need for doing this is rare, but it can happen depending on how the features are being added/deleted. I also believe this is more likely to happen when the changes are made in an edit session. As an example, a spatial index calculated for 50,000 lines might look much different than a spatial index for 140,000 lines in terms of grid size. Check out these articles to understand a bit more about spatial indexes:

http://resources.arcgis.com/en/help/main/10.1/index.html#//003n0000001r000000
http://resources.arcgis.com/en/help/main/10.1/index.html#//003n0000001s000000

So, your workflow might need to change to something like this at a high level:

Start edit session.

Delete 50,000 lines.

Recalculate the spatial index.

After several hours, add 80,000 lines.

Recalculate the spatial index.

Re: Non-versioned data slowing way down

MattLane — Mon, 21 Apr 2014 15:53:08 GMT

Thanks for the response.

The records are being deleted by the delete tool and added by the append tool outside of an edit session (python scripted process).

Like I mentioned, I'm monitoring the indexes and when a spatial index's fragmentation is > 30% I'm rebuilding. If the fragmentation is > 5% and < 30% I'm reorganizing.

I've tried using ArcCatalog to Recalculate as well as delete and recreate. It seems to do what the SQL Server functions do and neither improve access and editing times.

Re: Non-versioned data slowing way down

VinceAngelo — Mon, 21 Apr 2014 17:00:25 GMT

A couple of things to consider:

GUID columns, by their very nature, are always randomly distributed;

If the feature data has been gathered over time it is likely badly spatially

It's unlikely that the default spatial index tuning parameters are best for

- V

Re: Non-versioned data slowing way down

MattLane — Mon, 21 Apr 2014 20:39:58 GMT

If the feature data has been gathered over time it is likely badly spatially

fragmented. One of the hallmarks of spatial fragmentation is poor spatial
query performance, not matter how artfully the spatial index has been tuned.
There are many possible ways to defragment spatially, but the easiest is
to do an ORDER BY query on some attribute that imposes spatial order
(county code, etc). More exotic queries are available if you can use the
ST functions to group features into bands in X (or Y), then order by Y (or X).
Note that spatial defragmentation will hurt join performance on other
query constraints (like time), so that may need to be mitigated.

Thanks Vince, I'd like to look into how I can reorder the data without reloading it. How do I go about using an ORDER BY like you mentioned to defragment spatially? Are you referring to enforcing order during spatial index creation or just on queries to the db?

Re: Non-versioned data slowing way down

VinceAngelo — Sat, 11 Dec 2021 12:58:00 GMT

You can't defragment without reloading -- it's the load order which makes it fragmented.

One possible methodology is to create a backup table with

CREATE TABLE mytab_backup AS
SELECT * FROM mytab_backup

Then TRUNCATE the original, and execute something like

INSERT INTO mytab
SELECT * FROM mytab_backup 
ORDER BY county_fips

- V

Re: Non-versioned data slowing way down

MattLane — Tue, 29 Apr 2014 20:13:02 GMT

I'm still unable to get the two layers back to where they were a few weeks ago. They continue to get worse by the week 😞

I've followed your suggestion, ordering the geometries by the districts they are in, proved by using the TOP keyword in SQL Server. After rebuilding all the indexes the issue persists.

The display of the data in ArcMap is actually really fast. But adding it to ArcMap takes minutes. Just selecting it in ArcCatalog freezes the application for minutes. Other layers in the database, even fairly large ones, are instantaneous. This all makes me think there is some cruft in the SDE tables, or at least something wrong in them.

I've tried copy/pasting them into a new layer or importing into a new layer (even without the GlobalID field) and the new layers are just as slow.

Re: Non-versioned data slowing way down

VinceAngelo — Tue, 29 Apr 2014 20:37:24 GMT

Are you adding these layers as geodatabase feature classes or as Query Layers?

How many total features exist in the table?

- V

Re: Non-versioned data slowing way down

MattLane — Tue, 29 Apr 2014 20:59:49 GMT

I have two unversioned featureclasses in a dataset that our organization adds and deletes up to 10s of thousands of records daily. One is over 3million points and the other is over 500k lines

These were created in ESRI software and are edited with python tools outside of an edit session. They are not versioned (yet) but do have GlobalIDs. They will be replicated. They exist within a dataset, but exporting them out of the dataset into the main directory does not help. They were not like this 3 or 4 weeks ago, and there haven't been significant total record increases. Thanks for following up with me.

Re: Non-versioned data slowing way down

MarcoBoeringa — Wed, 30 Apr 2014 07:41:00 GMT

The display of the data in ArcMap is actually really fast. But adding it to ArcMap takes minutes. Just selecting it in ArcCatalog freezes the application for minutes. Other layers in the database, even fairly large ones, are instantaneous. This all makes me think there is some cruft in the SDE tables, or at least something wrong in them.

Do you, by default, work with ArcCatalog with the "Preview TAB" selected? Unfortunately, ArcCatalog always tries to draw the entire extent / dataset when hitting or opening the "Preview TAB", which may cause an apparent application hang. This voids any use of indexes or proper spatial ordering of the datasets in the underlying tables (spatial fragmentation), as it will need to read the entire table to draw the dataset.

If so, you would do good to avoid using the Preview TAB as much as possible, and only draw the datasets in ArcMap with a proper minimum and maximum display scale applied through the layer settings. It is also good practice to always access *.lyr files on a file system referencing your Feature Classes in the geodatabase using ArcCatalog, instead of accessing the Feature Classes directly through a database connection, as you will be a able to pre-define and limit display scales, set symbology and appropriate labelling.

It would also be wise to post some stats / specs about server hardware used, LAN speed, and the number of users on the servers and using these Feature Classes at any given point in time.

Re: Non-versioned data slowing way down

MattLane — Wed, 30 Apr 2014 15:05:40 GMT

No, this isn't previewing the data, this is just clicking the name in the contents tab. As in I want to right-click and select properties, which freezes ArcCatalog for 2 minutes before it shows the right-click menu.

I have software info in the initial post, I don't have hardware or network info and will have to ask. I can tell you that the DB servers are virtualized with 16gb of memory, and I have had the DBA restart the server to "free up memory" which didn't help in the slightest.

There is only 1 user during the day intermittently. I'll point out again that this has been a constant degradation over time. Things that used to be instantaneous on the dataset now take up to 4 minutes to do.

I guess I'll contact support today.

Thanks everyone.

Re: Non-versioned data slowing way down

George_Thompson — Wed, 30 Apr 2014 15:45:26 GMT

Matt,

I wonder if the geoprocessing history for those two feature classes is getting very large. Every time that you perform a GP process it adds to the metadata. The metadata is stored in a geodatabase system table.

You may want to look into this add-in: Delete Geoprocessing History from Metadata

Got the link from this post: http://forums.arcgis.com/threads/43399-metadata-geoprocessing-history

I would only do this after you have a verified back of the geodatabase.

Try it on the two feature classes in question

-George

Re: Non-versioned data slowing way down

WilliamCraft — Wed, 30 Apr 2014 16:48:58 GMT

George, I see there are separate tools/scripts for deleting geoprocessing history metadata from 9.x and 10.x geodatabases. Which ArcSDE system tables get cleaned out as a result of running these tools/scripts? Is it METADATA, GDB_USERMETADATA, or both?

Re: Non-versioned data slowing way down

George_Thompson — Wed, 30 Apr 2014 17:28:25 GMT

Just the geoprocessing history part of the metadata gets deleted. This can become rather large over time. The Documentation field is located in the SDE.GDB_ITEMS table, it is an XML field.

I just tested this on a local instance of SQL Server(2012), using ArcGIS Desktop 10.1 SP1 w/QIP installed, and it just deleted the Geoprocessing History for the feature classes that I selected. I verified in the Metadata, for those feature classes, that the geoprocessing section is now empty. I only tested this on a 10.x geodatabase, not a 9.3.x version.

System Tables of a Geodatabase in SQL Server

Here is a link to a KB article that talks about how to do it via Python:
Automate the process of deleting geoprocessing history

I would recommend doing this after you have a full, verified backup of the geodatabase or on a test instance. This is in case there are any hiccups during the process.

Hopefully this helps clear up some questions.

Re: Non-versioned data slowing way down

WilliamCraft — Wed, 30 Apr 2014 17:34:13 GMT

Thanks for clarifying, George. That is helpful. I'm curious to hear if this help's Matt's performance issues as described above.

Re: Non-versioned data slowing way down

MattLane — Thu, 01 May 2014 12:47:21 GMT

The metadata's Geoprocessing History proved to be the culprit. My processing has dropped to 1/8th the time it was taking. The lengths of the metadata fields for the two feature classes in the GDB_ITEMS table went from 27million and 19million in length down to 1500.

Thanks George, you're my new hero!