AnsweredAssumed Answered

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

Question asked by agelfert on Dec 20, 2018
Latest reply on Dec 20, 2018 by George_Thompson-esristaff

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.

Outcomes