MakeFeatureLayer_management Performance Issue

[JamesCrandall]

I'm seeing wildly different performance when establishing Feature Layers in different SDE databases.

ws1 = r'\\somenetworkpath\gdb1.sde'
fcname = r'gdb1.someschema.FCNAME
input_fc1 = os.path.join(ws1, fcname)
arcpy.MakeFeatureLayer_management(input_fc1, "input_fl1")

ws2 = r'\\somenetworkpath\gdb2.sde'
fcname = r'gdb2.someschema.FCNAME
input_fc2 = os.path.join(ws2, fcname)
arcpy.MakeFeatureLayer_management(input_fc2, "input_fl2")‍‍‍‍‍‍‍‍‍

In the above examples, ws1 takes over 6 seconds on the MakeFeatureLayer_management line, while the other is sub-1 second!

ws1 is a "publication" SDE database where no editing is done.  ws2 is non-versioned editing.

Any input or what to look for?

Thanks!

[RandyBurton]

How are you timing the scripts?  Also, is this inside/outside arcmap?

[JamesCrandall]

Randy,

I have a Timer class that I use:

class Timer:
    def __enter__(self):
        self.start = time.clock()
        return self
    def __exit__(self, *args):
        self.end = time.clock()
        self.interval = self.end - self.start‍‍‍‍‍‍‍

Then just generate time messages at runtime to print out.

tscratch = Timer()
with tscratch:
     arcpy.MakeFeatureLayer_management(input_fc1, "input_fl1")
msgtscratch = "Create Scratch FL (EGIS) completed in %.02f secs." % (tscratch.interval)
print msgtscratch‍‍‍‍‍

This is being executed in pyScripter.

[RandyBurton]

Thanks.  And things like "include arcpy" are not part of the section being timed?

[JamesCrandall]

I'm just timing each method to arcpy.MakeFeatureLayer_management -- and I might see 6 seconds on the first workspace (print msgtscratch), while the other may be 1 second or less (print msgtscratch2)!  Something like this:

#time the first make feature layer call from SDE gdb1
tscratch = Timer()
with tscratch:
     arcpy.MakeFeatureLayer_management(input_fc1, "input_fl1")
msgtscratch = "Create Scratch FL (EGIS) completed in %.02f secs." % (tscratch.interval)
print msgtscratch

#time the first make feature layer call from SDE gdb2
tscratch2 = Timer()
with tscratch2:
     arcpy.MakeFeatureLayer_management(input_fc2, "input_fl2")
msgtscratch2 = "Create Scratch FL2 (EGIS2) completed in %.02f secs." % (tscratch2.interval)
print msgtscratch2‍‍‍‍‍‍‍‍‍‍‍‍‍

[JoshuaBixby · Accepted solution]

Are gdb1 and gdb2 completely different DBMS instances or databases?  And if you reverse the order, gdb2 is the slow one and gdb1 the fast one?

I suspect there is either a library or collection of libraries that get loaded by the first Make Feature Layer call involving an enterprise geodatabase.  Since the libraries are already loaded, subsequent enterprise geodatabase connections go much faster, even connections to other databases.

What if you do something like:

ws2 = r'\\somenetworkpath\gdb2.sde'
arcpy.env.workspace = ws2
arcpy.ListFeatureClasses()

ws1 = r'\\somenetworkpath\gdb1.sde'
fcname = r'gdb1.someschema.FCNAME
input_fc1 = os.path.join(ws1, fcname)
arcpy.MakeFeatureLayer_management(input_fc1, "input_fl1")

ws2 = r'\\somenetworkpath\gdb2.sde'
fcname = r'gdb2.someschema.FCNAME
input_fc2 = os.path.join(ws2, fcname)
arcpy.MakeFeatureLayer_management(input_fc2, "input_fl2")‍‍‍‍‍‍‍‍‍‍‍‍‍

or

tmp_fc = arcpy.CreateFeatureClass_management("in_memory", "tmp", "POLYGON")
arcpy.MakeFeatureLayer_management(tmp_fc, "tmp_lyr")

ws1 = r'\\somenetworkpath\gdb1.sde'
fcname = r'gdb1.someschema.FCNAME
input_fc1 = os.path.join(ws1, fcname)
arcpy.MakeFeatureLayer_management(input_fc1, "input_fl1")

ws2 = r'\\somenetworkpath\gdb2.sde'
fcname = r'gdb2.someschema.FCNAME
input_fc2 = os.path.join(ws2, fcname)
arcpy.MakeFeatureLayer_management(input_fc2, "input_fl2")‍‍‍‍‍‍‍‍‍‍‍‍

[JamesCrandall]

Looking at the dba's schema printout: they are entirely different SLQ Server db's:

db1: "read only, nonversioned"

db2: "editable, nonversioned"

Also, switching the order produced similar initial times on the first connection, subsequent connections no matter if it is an entirely different db are much faster.  So, I guess it's not anything specific to the SDE connection or db, rather with how the python execution spins up those connections.