1

I'm looking to automate publishing map services to ArcGIS Server, and in doing so I'm creating map documents that have only one layer; each layer is from a SQL Server 2014 database that I am accessing through an sde connection file. To connect to the database I am using this code, which I found here.

mxd = arcpy.mapping.MapDocument("C:/fullpath/to/blank.mxd")
arcpy.env.workspace = "C:/full/path/to/sdefile.sde"
dat = arcpy.ListFeatureclasses("layerName")[0]
df = arcpy.mapping.ListDataFrames(mxd, "*")[0]
lyr = arcpy.mapping.Layer(dat)
arcpy.mapping.AddLayer(df, lyr)
mxd.save()

This all works. The issue is that the line dat = arcpy.ListFeatureclasses("layerName")[0] takes almost twenty minutes to run, sometimes forty minutes. Why is it taking so long to read that data?

It is not the connection itself as I can read the layer fine when I connect to it from ArcMap (though this is slow, it draws on the order of seconds and not minutes). Note that I have tried this without the search string and it does not change the time. Edit: the search is very slow (greater than 20 minutes) in a database with 228 tables and 146 views. However, it seems the search is tolerable (1 minute) in a database with 278 tables but 0 views.

My guesses: The database is not an SDE database, just a table with a geometry column. When I try to bring these layers into ArcMap, it shrieks that it does not have a unique identifier field and that it must calculate the spatial extent itself.

The tables that take the longest are views, so that may have something to do with it. But these layers do still publish, they just take forever. Another Edit: I tested searching for a table and searching for a view in the same database and the search times were nearly identical. This must have something to do with needing to search through all the data.

I think its possible I have same issue as this OP with their database #2: Adding layer to MXD from different SDE slows down ArcPy script. I'm using ArcGIS Desktop 10.4.

Any confirmation on my guesses?

  • 1
    how many tables in your database? – Midavalo Jun 6 '16 at 22:43
2

Okay, figured it out and feel a little stupid about it. I was using arcpy.ListFeatureClasses(layerName) which searched through the entire database. If I specify the featureclass as "C:/full/path/to/sdefile.sde/layerName" it finds the data extremely fast.

Wisdom learned: don't search through the entire database if you don't have to, and if you do, just use pyodbc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.