Using python for creating SDE versions seems very slow...

WarrenDavison · ‎04-16-2015

Hi There,

Hopefully someone can point me in the right direction with this, since I haven't been able to find the answer so far.

I have a python script that I run after the reconcile and post procedure to recreate the required child versions (simply for consistency and to save myself some time). However, it seems to take a really long time and the strange thing is that all of the versions take almost the exact same amount of time to create.

Would anyone be able to suggest what is at play here? Or provide an example of a similar script they may be using to do the same?

Here an sample from my script:

    import sde_generate_connect_strings


    sde_user_connect = sde_generate_connect_strings.generate_sde_user()

    # --------
    # create MAINTENANCE
    #
    print 'Create MAINTENANCE...'
    logfile.write('Create MAINTENANCE...\n')
    print 'Connection used is:  ' + sde_user_connect
    if sde_user_connect:
        try:
            arcpy.CreateVersion_management(sde_user_connect,'sde.DEFAULT', 'MAINTENANCE', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create MAINTENANCE'
        print '* * * * * * * *'

Thanks,

Warren

JoshuaBixby · ‎04-17-2015

So running the script you posted is noticeably slower than doing it manually? Creating the versions is quicker if you do it one by one manually?

WarrenDavison · ‎04-17-2015

Hi Joshua,

Yes after posting this I did actually run the script and it took a total of 6 minutes and 10 seconds to complete one version. In the script it creates a total of 10 different child versions, so it added up to be quite a while. Manually, it's obviously much quicker.

Thoughts?

Warren

AsrujitSengupta · ‎04-17-2015

A sample script for Reconcile\Post, Delete Versions, Compress Geodatabase and then Recreate those same versions:

# Import arcpy module
import arcpy, os, string


#Populate parent and child versions in the following manner('Parent':'Child', etc).  DO NOT LIST DEFAULT
vTree = {'SDE.BOWS':'SDE.JIM', 'SDE.GIS':'SDE.KATIE'}

def RecPostNonDefault(workspace,log1):
    
    for key, val in vTree.iteritems():
        print "Reading {0} {1}".format(key, val)
        arcpy.ReconcileVersions_management(workspace, "ALL_VERSIONS", key, val, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", log1)
        print "Rec and posting {0} to {1}".format(val, key)


def RecPostDefault(workspace, log2,defaultVersion):
    arcpy.ClearWorkspaceCache_management()
    for key, val in vTree.iteritems():
        print "Reading {0} {1}".format(key, val)
        arcpy.ReconcileVersions_management(workspace, "ALL_VERSIONS", defaultVersion, key, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", log2)
        print "Rec and posting {0} to {1}".format(key, defaultVersion)


def DeleteChildVersions(workspace):
    arcpy.ClearWorkspaceCache_management()
    for key, val in vTree.iteritems():
        arcpy.DeleteVersion_management(workspace, val)
        print "Deleted {0}".format(val)

def DeleteParentVersions(workspace):
    arcpy.ClearWorkspaceCache_management()
    for key, val in vTree.iteritems():
        arcpy.DeleteVersion_management(workspace, key)
        print "Deleted {0}".format(key)

#Compress database
def Compress(workspace,logWorkspace,log3):
    arcpy.ClearWorkspaceCache_management()
    outLog = open(os.path.join(logWorkspace, log3), 'w')
    arcpy.Compress_management(workspace)
    print ("Compressed database {0}".format(workspace))
    outLog.write("Compressed database {0}".format(workspace))
    outLog.close()


def RecreateVersions(workspace, defaultVersion):
    for key, val in vTree.iteritems():
        arcpy.CreateVersion_management(workspace,defaultVersion, key[4:], "PUBLIC")
        print "Created version {0}".format(key)
        arcpy.CreateVersion_management(workspace, key, val[4:], "PUBLIC")
        print "Created version {0}".format(val)


if __name__=="__main__":
    
    workspace = r"C:\Users\asrujitse\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\gdb103_sqlserver.sde"
    log1 = ""
    log2 = ""
    logWorkspace = r"C:\TEMP"
    log3 = "compress.txt"
    defaultVersion = "sde.DEFAULT"
    RecPostNonDefault(workspace,log1)
    RecPostDefault(workspace, log2,defaultVersion)
    DeleteChildVersions(workspace)
    DeleteParentVersions(workspace)
    Compress(workspace,logWorkspace,log3)
    RecreateVersions(workspace, defaultVersion)

WarrenDavison · ‎04-17-2015

Hi Asrujit,

I will definitely give this script a shot and see if the performance is different. Would you be able to elaborate at all as to why I may be experiencing such slow performance when running my script?

I've pasted my entire script below.

Thanks,

Warren

# Import system modules
import os
##import arcgisscripting
import arcpy
#os.sys.path.append(r'c:\scripts')
os.sys.path.append(r'P:\GIS\Documentation\Data_Maintenance\Python_Scripts')
import datetimestring






# Write time to logfile function
def writeTimeToLogfile():
    dstr = datetimestring.generate()
    print 'Time: ' + dstr + '\n'
    logfile.write('Time: ' + dstr + '\n')
    logfile.write('\n')
    return






try:
    
    # create log file
    dstr = datetimestring.generate()
    logfilename = r'sde_create_child_versions_log_' + dstr + r'.txt'
    print logfilename + r' is the name of the log file.'
    print ''
    logfilepath = r'P:/GIS/Documentation/Data_Maintenance/Python_Scripts/logs/' + logfilename
    try:
        logfile = open(logfilepath,'w')
    except:
        print 'An error occurred while opening the log file for writing...'
        exit
        


    # write start to log file
    print r'Starting script sde_create_child_versions.py\n'
    logfile.write('Starting script sde_create_child_versions.py \n')
    writeTimeToLogfile()


    # create variables for connection / workspaces
    import sde_generate_connect_strings


    sde_user_connect = sde_generate_connect_strings.generate_sde_user()
    swm_editor_connect = sde_generate_connect_strings.generate_swm_editor()
    inframap_connect = sde_generate_connect_strings.generate_inframap()


    print ''
    logfile.write('\n')


    # --------
    # create MAINTENANCE
    #
    print 'Create MAINTENANCE...'
    logfile.write('Create MAINTENANCE...\n')
    print 'Connection used is:  ' + sde_user_connect
    if sde_user_connect:
        try:
            arcpy.CreateVersion_management(sde_user_connect,'sde.DEFAULT', 'MAINTENANCE', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create MAINTENANCE'
        print '* * * * * * * *'
        logfile.write('Database connection doesn\'t exist - failed to create MAINTENANCE')
    writeTimeToLogfile()




    # --------
    # create SWM
    #
    print 'Create SWM...'
    logfile.write('Create SWM...\n')
    print 'Connection used is:  ' + swm_editor_connect
    if swm_editor_connect:
        try:
            arcpy.CreateVersion_management(swm_editor_connect,'sde.DEFAULT', 'SWM', 'PROTECTED')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create SWM'
        print '* * * * * * * *'
        logfile.write('* * * * * * * *\n')
        logfile.write('Database connection doesn\'t exist - failed to create SWM')
        logfile.write('* * * * * * * *\n')


    writeTimeToLogfile()




    # --------
    # create PWS
    #
    print 'Create PWS...'
    logfile.write('Create PWS...\n')
    print 'Connection used is:  ' + sde_user_connect
    if sde_user_connect:
        try:
            arcpy.CreateVersion_management(sde_user_connect, 'sde.DEFAULT', 'PWS', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create PWS'
        print '* * * * * * * *'
        logfile.write('Database connection doesn\'t exist - failed to create PWS')


    writeTimeToLogfile()



    # --------
    # create FieldEdits
    #
    print 'Create FieldEdits...'
    logfile.write('Create FieldEdits...\n')
    print 'Connection used is:  ' + sde_user_connect
    if sde_user_connect:
        try:
            arcpy.CreateVersion_management(sde_user_connect, 'sde.DEFAULT', 'FieldEdits', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create FieldEdits'
        print '* * * * * * * *'
        logfile.write('Database connection doesn\'t exist - failed to create FieldEdits')


    writeTimeToLogfile()




    # --------
    # create IMTS4163
    #
    print 'Create IMTS4163...'
    logfile.write('Create IMTS4163...\n')
    print 'Connection used is:  ' + inframap_connect
    if inframap_connect:
        try:
            arcpy.CreateVersion_management(inframap_connect, 'sde_user.FieldEdits', 'IMTS4163', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create IMTS4163'
        print '* * * * * * * *'
        logfile.write('Database connection doesn\'t exist - failed to create IMTS4163')


    writeTimeToLogfile()
    # --------
    # create IMTS4164
    #
    print 'Create IMTS4164...'
    logfile.write('Create IMTS4164...\n')
    print 'Connection used is:  ' + inframap_connect
    if inframap_connect:
        try:
            arcpy.CreateVersion_management(inframap_connect, 'sde_user.FieldEdits', 'IMTS4164', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create IMTS4164'
        print '* * * * * * * *'
        logfile.write('Database connection doesn\'t exist - failed to create IMTS4164')


    writeTimeToLogfile()
    # --------
    # create IMTS4165
    #
    print 'Create IMTS4165...'
    logfile.write('Create IMTS4165...\n')
    print 'Connection used is:  ' + inframap_connect
    if inframap_connect:
        try:
            arcpy.CreateVersion_management(inframap_connect, 'sde_user.FieldEdits', 'IMTS4165', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create IMTS4165'
        print '* * * * * * * *'
        logfile.write('Database connection doesn\'t exist - failed to create IMTS4165')


    writeTimeToLogfile()
    # --------
    # create IMTS3803
    #
    print 'Create IMTS3803...'
    logfile.write('Create IMTS3803...\n')
    print 'Connection used is:  ' + inframap_connect
    if inframap_connect:
        try:
            arcpy.CreateVersion_management(inframap_connect, 'sde_user.FieldEdits', 'IMTS3803', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create IMTS3803'
        print '* * * * * * * *'
        logfile.write('Database connection doesn\'t exist - failed to create IMTS3803')


    writeTimeToLogfile()

    # --------
    # create IMTS3391
    #
    print 'Create IMTS3391...'
    logfile.write('Create IMTS3391...\n')
    print 'Connection used is:  ' + inframap_connect
    if inframap_connect:
        try:
            arcpy.CreateVersion_management(inframap_connect, 'sde_user.FieldEdits', 'IMTS3391', 'PUBLIC')
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
        except:
            print 'Oops!  Exception...'
            print arcpy.GetMessages()
            logfile.write(arcpy.GetMessages())
    else:
        print '* * * * * * * *'
        print 'Database connection doesn\'t exist - failed to create IMTS3391'
        print '* * * * * * * *'
        logfile.write('Database connection doesn\'t exist - failed to create IMTS3391')


    writeTimeToLogfile()


    logfile.write('\n\nEnd of processing...\n')





finally:
    logfile.write('\n\nClosing logfile in finally statement...\n')
    logfile.close()

WarrenDavison · ‎04-23-2015

Hi Asrujit,

I've tried the script you've suggested and have found little difference in terms of performance. Would you have any other recommendations? I suspect that this delves deeper than just the python used to execute the reconcile and post.

Thanks,

Warren

SteffenScharfe · ‎12-11-2015

A simple python-script to create a version takes too long to execute (about 5 minutes).

Same problem with delete_version, ReconcileVersions...

This problem occurs with ArcGIS 10.2.1, ArcGIS 10.1 was ok.

arcpy.CreateVersion_management("db-Verbindungen","SDE.DEFAULT","Steffen_test","PRIVATE")

Steffen

WarrenDavison · ‎12-11-2015

Hi Steffen,

It turns out the reason our script was so incredibly slow was that we were saving geoprocessing metadata logs. So everytime we ran our reconcile and post process it would save metadata. Eventually this piled up until we had a conversation with support and we tried removing the metadata from the geoprocessing logs. This made everything fast again.

You could look into this if you are experiencing the same performance issues.

Warren

JoshuaBixby · ‎12-12-2015

Can you elaborate on how you did this, that would be really helpful.

SteffenScharfe · ‎12-13-2015

Tnx for the infos.

arcpy.SetLogHistory(False) does the magic.

Now, arcpy.CreateVersion_management takes about 5 seconds.

But I think this is only a workaround....

I have no idea why logging need so much time.

Steffen