topic Re: Python Script - Updating Feature Class in SDE daily in Geodatabase Questions

Python Script - Updating Feature Class in SDE daily

timdunlevie — Fri, 02 Oct 2015 00:17:37 GMT

Hi all,

I am going to implement a nightly script which will remove all records in a Feature Class in our spatial DB, and append new records.

Apart from compressing the Database, is there anything else I should manage? Analyze ? Should I remove any locks that could be connected?

This process will run every night.

Will compressing the entire DB have an effect with any child/parent DBs ?

Also, are you able to compress a single Feature Class rather than an entire DB ?

we run 10.2

Thanks,

Tim

Re: Python Script - Updating Feature Class in SDE daily

AsrujitSengupta — Fri, 02 Oct 2015 04:39:39 GMT

Compress, Rebuild Indexes, Analyze datasets are some of the maintenance tasks.

Disconnecting all users before doing the compress will help. Considering that the script will run nightly, this shouldn't be a problem I guess.

No, compressing the sde geodatabase will not affect replica GDBs.

No, the compress tool runs for the entire geodatabase.

Re: Python Script - Updating Feature Class in SDE daily

timdunlevie — Fri, 02 Oct 2015 04:41:24 GMT

Brilliant!

Many thanks….just what I needed to know.

Tim

Re: Python Script - Updating Feature Class in SDE daily

WesMiller — Sun, 12 Dec 2021 09:34:39 GMT

Here's what i use

# Import system modules
import arcpy,os


# set workspace
workspace = r"Your File here"


# set the workspace environment
arcpy.env.workspace = workspace


# No more users to connect to database
arcpy.AcceptConnections(workspace, False)


#Disconnect Users
arcpy.DisconnectUser (workspace, "ALL")


# Compress database
arcpy.Compress_management(workspace)
print 'Compression Complete'


# Get a list of stand alone feature classes
dataList = arcpy.ListFeatureClasses()


# Add Feature classes in datasets
for dataset in arcpy.ListDatasets("", "Feature"):
    arcpy.env.workspace = os.path.join(workspace,dataset)
    dataList += arcpy.ListFeatureClasses()


# reset the workspace
arcpy.env.workspace = workspace


# Execute rebuild indexes
arcpy.RebuildIndexes_management(workspace, "NO_SYSTEM", dataList, "ALL")
print 'Rebuild Complete'


# Allow connections.
arcpy.AcceptConnections(workspace, True)

Re: Python Script - Updating Feature Class in SDE daily

JoshuaBixby — Fri, 02 Oct 2015 20:12:03 GMT

Is this feature class large? Are all the records being altered/modified each day? I ask because completely deleting all records in a feature class only to replace them with many of the same records creates a lot of overhead, especially if the feature class is versioned, even more especially if there are replicas based off of the geodatabase. If most of the records don't change, it might be more efficient to implement a record comparison workflow and only purge and replace records that are different.

Re: Python Script - Updating Feature Class in SDE daily

MichaelVolz — Fri, 02 Oct 2015 20:28:50 GMT

I agree with Joshua as the DBA of my organization would only do an incremental update of data in an SDE feature class and not a full truncate and append as there is a cost in swap space (or something to that effect) with the truncate and append process.

Re: Python Script - Updating Feature Class in SDE daily

BlakeTerhune — Fri, 02 Oct 2015 21:25:56 GMT

Why no Analyze Datasets Wes Miller?

Re: Python Script - Updating Feature Class in SDE daily

timdunlevie — Mon, 05 Oct 2015 00:29:00 GMT

Thanks Joshua,

Yeah the FC is ~ 8000 records, of which maybe only a handful are added/modified.

So a record comparison is the way to go….thanks.

Do you run a script to perform this comparison?

Thanks,

Tim

Re: Python Script - Updating Feature Class in SDE daily

WesMiller — Mon, 05 Oct 2015 13:38:46 GMT

I thought i had put that in, apparently, I didn't thanks

Re: Python Script - Updating Feature Class in SDE daily

BlakeTerhune — Mon, 05 Oct 2015 15:42:52 GMT

Are all of your feature classes in the same schema?

Re: Python Script - Updating Feature Class in SDE daily

WesMiller — Tue, 06 Oct 2015 14:34:31 GMT

Yes they are all in the same schema.

Re: Python Script - Updating Feature Class in SDE daily

RandyKreuziger — Tue, 06 Oct 2015 15:31:50 GMT

Our Corporate Library SDE database has no versioned feature classes so we do the Truncate / Append all the time. Many of the datasets are coming from other state agencies so it's just easier to do it this way.

Re: Python Script - Updating Feature Class in SDE daily

MichaelVolz — Tue, 06 Oct 2015 15:34:58 GMT

How often do you truncate and append your non-versioned feature classes from other state agencies?

Re: Python Script - Updating Feature Class in SDE daily

JoshuaBixby — Tue, 06 Oct 2015 16:05:12 GMT

Instead of getting into the specifics of what works for us, and might not work for others, I encourage you to look over An overview of the Data Comparison toolset if you haven't already. I have used both Feature Compare and Table Compare. Although they require a bit of setup, once done, the tools can be quite efficient. The Detect_Feature_Changes tool has always intrigued me, but I have never taken the time to try it out.

Re: Python Script - Updating Feature Class in SDE daily

BarryGuidry — Thu, 11 Apr 2019 13:35:37 GMT

Tim, would you mind posting your script? I did this in the past, and posted to the ESRI forums, but was removed with the archives I guess.

Thank you,

Barry Guidry

Re: Python Script - Updating Feature Class in SDE daily

timdunlevie — Fri, 12 Apr 2019 01:41:55 GMT

Hi Barry,

I will have to dig out the code as I have since left the company I implemented this with…

Thanks,

Tim