arcpy.ListFeatureClasses() slow with database views...

576
3
12-20-2018 08:39 AM
Arne_Gelfert
Regular Contributor

So I was starting to get back into using arcpy after a non-GIS hiatus and wanted to get a list of feature classes in SDE.

Easy enough, you'd think:

import arcpy
from arcpy import env

conn = r'<path_to_my_sde_connection>'
fclist = arcpy.ListFeatureclasses(conn)

for fc in fclist:
    print(fc)


This took about 7 minutes for me to list 154 feature classes. Whoa! I was stumped. What's going on here? I tried it on another similarly sized database, and it ran in seconds.

So I set up SDE-intercept. If you're not familiar with that, it's explained here, and ran the same script.

Turns out that those six (6) lines of code, or really just line 4, generated 16MB of log file. What the heck?

While I have no idea why there is so much info (I assume I can configure a less verbose log). it certainly was interesting to scroll through the log. What I found from the timestamps is that it takes split seconds to connect and start reading feature classes that are tables. But then we hit database views, and the connection essentially goes idle... there a 2-minute long intervals in the log where nothing happens.

Can someone explain to me why that is? What is the code/connection doing? I assumed that this would be pretty straight forward on the database side (in my case SQL Server 2012), some kind of query against SYS.TABLES or SYS.VIEWS with some ESRI/Arc overhead. But it feels like it doesn't like those views. I am trying to understand the mechanics here and figure out if there is something "wrong" that could be fixed or optimized because I'd like to get this to run a whole lot faster. Otherwise, it's rather useless.

0 Kudos
3 Replies
George_Thompson
Esri Frequent Contributor

What is the version of Python being used? Is it from a desktop install, if so, what version of Desktop?

Are there a lot of DB views in the database?

On the other DB that ran in seconds, are there also DB views in it?

--- George T.
0 Kudos
Arne_Gelfert
Regular Contributor

Ah.... now you got me thinking. Could it be the version of Python? In fact, having turned a Jupyter Notebooks junkie for all things Python, I installed arcpy along with arcgis using conda install using the following Python version:

  • 3.6.5 |Anaconda, Inc.| (default, Mar 29 2018, 13:32:41) [MSC v.1900 64 bit (AMD64)]

Please don't tell me I'm going to have to go back to 2.7.x!?!

There are only about 50 views. The database without these issues has 94 views. I could see how the number of views if large could slow things down. But it seems to be specific views. What I don't understand is why that should matter if all I want is the name of the featureclass.

0 Kudos
George_Thompson
Esri Frequent Contributor

I am NOT saying go back to the older Python. I wonder if it has to do with the complexity of the view in the DB?

Are all the views registered with the geodatabase?

I wonder if it is looking at each view and querying the source table? I am not familiar enough with that function to say one way or another.

--- George T.
0 Kudos