0

On one of our projects, I have been creating .mxd Files that reference a PostgreSQL Database. For some corporate reasons, the database had to be relocated to a new server, leaving all the mxd connections broken.

This was supposed to be a simple correction with python and the arcpy library. I have been running into some issues, though. Here is the code I've been using to achieve said purpose:

import arcpy
import os
mxdPath = r"..." #Path where the files are stored
originalRef = "Database Connections\Connection to xxx-180.sde"
newRef = "Database Connections\Connection to new-server.sde"

for root, dirs, filenames in os.walk(mxdPath):
    for f in filenames:
        mxd_path = os.path.join(root, f)
        mxd = arcpy.mapping.MapDocument(mxd_path)
        mxd.findAndReplaceWorkspacePaths(originalRef, newRef) #Point where execution halts.
        mxd.save()
        del mxd

The problem is: This script takes forever to run (it's working, but taking over 2 hours on a single file!). I have tried debugging it, and apparently, the findAndReplaceWorkspacePaths takes just too long to update (I would guess that as the layers start to be updated, the program tries to redefine the query layers to the connection that no longer exists, but doesn't recognize it immediately. I am running into the same problem while opening the files per se with ArcMap - a long wait until the document manages to load).

What could be done?

I was hoping there was a way to pause the reloading of the query layers and then reenabling them.

  • 1
    Add some print statements into your code so you can see where the script is hanging at – NULL.Dude Apr 7 '17 at 17:57
  • How many layers per MXD? – Midavalo Apr 7 '17 at 17:58
  • Oh, I did. The only part where it is hanging at is the findAndReplaceWorkspacePaths. – fabio.avigo Apr 7 '17 at 17:59
  • Around 20 layers per file. – fabio.avigo Apr 7 '17 at 17:59
  • Please use the edit button beneath your question to revise it with any requested clarifications. – PolyGeo Apr 7 '17 at 20:04
1

You say you have query layers inside your mxd.

You can check that you are not actually succeeding in changing the query layer SQL query definition.

From the Layer properties > Source tab, it does look like it is using now a new database, but take a look inside the Edit Query Layer window by clicking the Change Query button. The Query will still represent the SQL with the reference to your source database. There is no way to update the query layer SQL definition using arcpy. As soon as you've created one, you either change it manually or use ArcObjects.

Another option would be save the symbology of the query layer, remove it, create a new one and then re-apply the symbology. I recommend using database views instead of query layers, because they can be updated easily using SQL tools without involving any ArcGIS tools.

In terms of performance, you might try multiple things:

  1. Disable Validate parameter on your call:

mxd.findAndReplaceWorkspacePaths (find_workspace_path, replace_workspace_path, {validate}) validate is True by default. You might try setting it to False.

  1. Use mxd.replaceWorkspaces method instead and compare performance.

  2. Use layer.findAndReplaceWorkspacePath and layer.replaceDataSource methods instead and compare performance. You will need to create Layer objects instances for every layer in the map document.

  3. Run your scripts referencing the full path to the .sde connection files instead of using the Database Connections which is a shortcut when running your script on a machine with ArcGIS Desktop installed.

  4. If possible, run your script on the same machine where you have the database (or at least as close in terms of the network as possible). Accessing SDE geodatabase over the network has always been slow and there is a huge overhead in terms of metadata fetching. There is another answer of mine to question about slow creation of .lyr files from SDE tables.

1

(Too long to post it as an Edit, so I have to post it as an answer): As suggested by Alex Tereshenkov, here are the results for the attempts:

  1. Disabling Validate parameter on the call, i.e. setting it to False where mxd.findAndReplaceWorkspacePaths (find_workspace_path, replace_workspace_path, {validate}) validate is True by default.: This did improve performance, although not significantly. It still took a very long time to execute.

  2. Use mxd.replaceWorkspaces method instead and compare performance.: Can't say I've noticed a big difference here.

  3. Use layer.findAndReplaceWorkspacePath and layer.replaceDataSource methods instead and compare performance. You will need to create Layer objects instances for every layer in the map document.: For it to work, I had to add a check before using the findAndReplaceWorkspacePath:

    for lyr in arcpy.mapping.ListLayers(mxd):
        if lyr.supports("DATASOURCE"): #execution halts here
            lyr.findAndReplaceWorkspacePath(originalRef, newRef, False)
    

    If I run without checking whether the layer has support for the datasource property, the code works much faster, but returns an error message for a layer that does not.

  4. Run your scripts referencing the full path to the .sde connection files instead of using the Database Connections which is a shortcut when running your script on a machine with ArcGIS Desktop installed.: I haven't tried this, since I wanted to maintain the relative paths. If someone were to give this a try, I suppose it'd be possible set the MapDocument relativePaths property to False, setting the new Datasource and then setting it back to True.
  5. If possible, run your script on the same machine where you have the database (or at least as close in terms of the network as possible). Accessing SDE geodatabase over the network has always been slow and there is a huge overhead in terms of metadata fetching. There is another answer of mine to question about slow creation of .lyr files from SDE tables.: This is impossible in the given scenario, as I can't directly access the machine where the database is set.

What I did: As much as I've tried to speed this up, it ended up just not being worth spending any more time on it. I have set the Validate property to False and let it run during a few days. It might also be worth noticing that I had to run the MXD Doctor for every file afterwards, or they would halt on the ArcMap loading screen. I didn't investigate the issue any further.

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.