8

Update 4/11/2014

It looks like the script was getting hung up on the Delete Features tool, so I switched to Truncate Table, as suggested in the answer below. I also removed the unused variables from the append tool.

Update 4/10/2014

I ran this script on my co-worker's computer (his machine has more memory AND contains ArcGIS 10.0/Python26) and it ran quickly. Hooray! Once my tech support finds the ArcGIS 10.0 CD, I will install and test to see if that improves the speed on my machine. To be clear, we are running the same script, our network drive and database connection are mapped identically, and the print statements are the same. I'll post an update here once that happens.

End updates

I need to increase the speed of some Python scripts that perform updates on an Oracle database. I had these Python scripts running well for a year+, via scheduled tasks and batch files to initiate the scripts. Last week I moved from an XP to a Windows 7 machine and ArcGIS 10.0 --> 10.1. Since then the scripts have become terribly slow. If I run this script using a small feature class (containing ~20 features) it runs in 30 seconds. If I use a medium feature class (~80,000 records) it runs in 15 minutes. The feature class I really need to be able to transfer quickly contains about 1,000,000 records - the script only goes as far as the print statement to check if the files exist (if statement in code below). This process would take only 35 minutes to complete on my XP/ArcGIS 10.0 machine.

Below is the simplified code I have been testing with. Does anyone have suggestions on what I can do to increase the speed? Thanks, Patty

import arcpy, os, sys
from arcpy import env
arcpy.env.overwriteOutput = True
from datetime import datetime
import smtplib
import string
import urllib

#Define variables
inWorkspace = "O:/LANDING_PAD/BOE/example.gdb" 
lpFeatures = inWorkspace + os.sep + "fc1"
outWorkspace =  "Database Connections\\THIS.sde"
arcpy.env.workspace = outWorkspace
workspace = ""
copyFC = outWorkspace + os.sep + "SDE.fc1_1" #The feature class the script will update via delete and append
schema_type = "NO_TEST"
fieldMappings = ""
subtype = ""
t = datetime.now()
print "This script began at: " + str(t)

if arcpy.Exists(lpFeatures) is True and arcpy.Exists(copyFC) is True:
    print "Both files exist. Beginning delete..."
    arcpy.DeleteFeatures_management(copyFC) #(copyFC)

    print "ALL DONE DELETING!"

    arcpy.Append_management(lpFeatures, copyFC, schema_type, fieldMappings, subtype) #Append data from landing pad to db
    print "ALL DONE APPENDING!"
    record_count = arcpy.GetCount_management(lpFeatures)
    print record_count
    r = datetime.now()
    print "This script ended at: " + str(r)
  • 1
    I haven't used arcpy, but I have written some Python and a lot of parallel systems in C#. Is it possible you could break your work up into smaller chunks and work over them in parallel? Either dispatch multiple Python processes or try to use threading. It could get messy, especially if arcpy is not thread-safe, but it might pay off when you have a lot to do! It may help to ask on Stack Overflow as well. – jocull Apr 10 '14 at 15:07
  • The slowness is because you're deleting all of the individual features and appending to the emptied feature class. Is there any reason you can't delete the entire feature class with Delete_management() and then recreate it with CopyFeatures_management() or FeatureClassToFeatureClass_conversion()? – nmpeterson Apr 10 '14 at 16:11
  • 2
    Have you done any profiling (docs.python.org/2/library/profile.html) to determine where the bulk of your processing is occuring? It would be interesting seeing your results. – Aaron Apr 10 '14 at 16:16
  • 1
    @jocull yes, I've thought about putting together something that uses multiprocessing, but I was a little stuck on how the scripts were so fast on my XP/ArcGIS 10.0, and slow on Windows 7/10.1. Aaron, yes, it would be cool to see where the processing is occurring I'll look into profiling the script. Thanks, patty – Patty Jula Apr 10 '14 at 17:03
  • I posted an update above. Basically, the scripts run fast on my co-worker's machine which is good. – Patty Jula Apr 10 '14 at 17:03
6

I have wanted to comment first, but then it seemed more appropriate to wrap it to be an answer (even though it might be incomplete).

I've run your code on my machine (top hardware laptop with SSD) appending a file geodatabase feature class to a SQL Server geodatabase feature class on the same machine which took me around 13 min. I cannot tell you for sure why the speed of execution differs so much in your environment (10.0 >> 10.1), but you have asked for suggestions on what you can do to increase the speed, so here are some ideas which might increase the speed of running the script.

1) I run the script from the command line which is equivalent to running a .bat file (I run the script in the 64-bit flavor, I have ArcGISx6410.2 64bit Python installed).

c:\Python27\ArcGISx6410.2\python.exe c:\scripts\appendfc.py

From my experience, it is generally faster to run 64bit version of Python for executing long and heavy GP operations like Append. So you want to make sure you run this version of Python when executing the script.

2) I wouldn't recommend using arcpy.DeleteFeatures_management; it is much slower than running Truncate Table since the latter one doesn't use database transactions which improves performance over row-by-row deletion.

You mentioned that the script only goes as far as the print statement to check if the files exist (if statement in code). There is a good chance that it keeps deleting row by row which might be a very slow process when you accessing a table in a remote Oracle (or really any DBMS) database. Try running the script with Truncate Table, but with no Append first just to see the performance difference on the deletion stage.

3) You seem to be using "Database Connections\\THIS.sde" in the code. It is better however to refer to the connection file itself (.sde file) with the file system or UNC path, not the Catalog window "Database Connections" folder. You can access the .sde file created at C:\Users\%user%\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog. You can move this .sde file around as you need and place in the folder the Python script will have access to.

4) In the arcpy.Append_management function, you use a couple of empty parameters. In theory, it shouldn't make any difference, but I would suggest running the function without specifying those parameters just because you don't need them. You never know what's going on behind the scenes and whether those empty strings are evaluated at some point and whether this can impact the performance. Just go with arcpy.Append_management(lpFeatures, copyFC, schema_type) and don't specify parameters for which you don't supply any values.

5) I discourage using os.sep when building a path to a feature class. Use os.path.join(geodatabase,featureclassname) for that instead. It is just cleaner and more readable.

You can add more details to the question after you've tried the things above and had some tests and code review.

A couple of good questions to read to get more insight on how to speed up the Python scripts in ArcGIS:

Performance of ArcGISScripting and large spatial data sets

Background Geoprocessing (64-bit)

Arcgis CopyFeatures tool extremely slow when exporting to SDE

Ways to Speed Up Python Scripts Running As ArcGIS Tools

Geoprocessing considerations for ArcSDE data

  • Thanks so much, you saved my Friday. I wanted to add that yes, I can call a database in a batch file that is named "Database Connections\\THIS.sde". Maybe this is because the batch file is just initiating the Python scripts that use this variable? I can't have a database named THIS database.sde which is strange to me since there is a space in Database Connections. Thanks again, – Patty Jula Apr 14 '14 at 4:15
  • Glad it was helpful. 1. How does it look performance wise now? 2. Interesting with "Database Connections". I was so sure that you cannot refer to this "folder" within Catalog window when not running the script from ArcGIS Desktop GUI, but I was wrong. I've updated my answer to reflect this. 3. You can have "this database.sde" connection file, spaces is OK to use. But you cannot have a database in Oracle with spaces with is just how DBMS works. – Alex Tereshenkov Apr 14 '14 at 6:21
  • The performance is improved now. The process to run the million record feature class (of addresses points) runs on the Windows 7/ArcGIS 10.1 machine now (before it just froze on the delete features tool). It takes 3 hours to run this entire process. This process took 50 minutes on my XP/10.0 machine. Is this link what you were referring to with point 1 in your answer? – Patty Jula Apr 14 '14 at 15:43
  • @PattyJula, right, it is this one. You don't need to install/use background processing software that is installed on top. You just need to use 64bit flavor of Python when running your script. – Alex Tereshenkov Apr 14 '14 at 16:39
  • Thanks for the suggestion. I've installed 64bit Python plus a 64bit Oracle client. My scripts are still not running at the speed they ran with my XP/ArcGIS 10.0 configuration. I'm going to schedule task to run the batch files and Python scripts this evening, if the speed does not improve, I may need to set up another machine running ArcGIS 10.0. – Patty Jula Apr 18 '14 at 15:05
1

I am hoping this example will help answer the question too and is on newer software. It builds on the answers and comments mentioned above.

Setup:

  1. Windows 7
  2. SQL Server 2012 R2
  3. ArcGIS 10.2.2 (Server and Desktop)

Load needed to be nightly. It was ~9300 records and 234 attributes.

The original model was below and was done all in SQL Server 2012 R2/SDE (7 mins via ArcCatalog & 3 hours with python):

  1. Delete Rows of Feature Class in SDE
  2. Make XY event Layer from table in SQL Server
  3. Append to Feature Class in SDE

How I altered it was (10 seconds via ArcCatalog & 10 seconds via python): •

  1. Replaced delete rows tool with Truncate tool for GIS Feature class in SDE
  2. Export the SQL Table to a FGDB on the local C Drive
  3. Make XY Event Layer on local memory
  4. Feature Class to Feature Class within FGDB on Local C drive
  5. THEN Append FGDB Feature class into SDE
  6. Note that my SQL Server database is on the same C drive as my FGDB. It may slow down a bit over a network but still most likely not the 3 hours I was seeing.

What did help a bit on the original model was replacing the data sources as per #3 recommended above. It shaved off 30 seconds when running in ArcCatalog. With python it shaved off about 20 mins. Therefore it is a variable in speed but it is not the most valuable variable to address in my case. It appears according to most blogs SQL Server just does not like heavy data loading “from memory” (i.e. make xy event layer). SQL/SDE seems to prefer an actual object to load. This explains why my other loads I do the same way take 1 minute, but those are only 1000 records with 15 attributes so I never questioned the efficiency of my models until this load needed to be done nightly. As mentioned this load is 9000 records with 236 attributes.

0

The problem with performance between 10.0 and 10.1 is something in the SDE libraries was changed in desktop. We used to post 1,000,000 nightly and took only 45 mins, after a software update it took almost 24 hours. Obviously there is no problem with the data and only the software was changed.

Check the version of the geodatabase to make sure it matches the version of the client running arcpy. We reported this to ESRI with no response or recognition of a bug. It is pretty obvious and the problem started after 10.0 SP1.

Also another test a copy/paste is faster than an append try this from 10.0 and 10.1 and performance should be similar. This proves there is some sort of bug going to prior versions when appending geometries.

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.