Problem with Arcpy for ArcPro - SelectLayerByAttribute_management takes longer and longer if it is called many times

1368
10
Jump to solution
02-22-2019 12:54 PM
VincentLantaca1
New Contributor III

I have set up a simple test script to demonstrate the problem I'm running into:

#importing python modules
import arcpy, time

basePath = r"C:\Users\vincentl\AppData\Local\PythonTesting\Geodatabases\ScratchAddressing.gdb"
arcpy.env.workspace = basePath
addresses = basePath + r"\GIS_DBO_Places_Sample"

whereClause = "OBJECTID IN (1)"

for i in range(0, 100):
    start = time.time()
    addSelect = arcpy.SelectLayerByAttribute_management(addresses, "NEW_SELECTION", whereClause)       #selecting current record
    stop = time.time()
    print("Time elapsed: {0}".format(stop - start))
‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Output:

Time elapsed: 2.4421534538269043
Time elapsed: 0.2509007453918457
Time elapsed: 0.26900672912597656
Time elapsed: 0.2650105953216553
Time elapsed: 0.2917516231536865
Time elapsed: 0.2583498954772949
Time elapsed: 0.27279186248779297
Time elapsed: 0.2871057987213135
Time elapsed: 0.31418442726135254
Time elapsed: 0.2747483253479004
Time elapsed: 0.2698488235473633
Time elapsed: 0.2711470127105713
Time elapsed: 0.30519843101501465
Time elapsed: 0.30664968490600586
Time elapsed: 0.3076004981994629
Time elapsed: 0.2947726249694824
Time elapsed: 0.3275151252746582
Time elapsed: 0.3086280822753906
Time elapsed: 0.3205225467681885
Time elapsed: 0.32954835891723633
Time elapsed: 0.32260847091674805
Time elapsed: 0.328507661819458
Time elapsed: 0.3341994285583496
Time elapsed: 0.32455968856811523
Time elapsed: 0.3244626522064209
Time elapsed: 0.3398430347442627
Time elapsed: 0.3336300849914551
Time elapsed: 0.3469429016113281
Time elapsed: 0.3484632968902588
Time elapsed: 0.3517005443572998
Time elapsed: 0.3604257106781006
Time elapsed: 0.36439085006713867
Time elapsed: 0.3885536193847656
Time elapsed: 0.44512104988098145
Time elapsed: 0.395768404006958
Time elapsed: 0.3962588310241699
Time elapsed: 0.4052317142486572
Time elapsed: 0.4457588195800781
Time elapsed: 0.4870429039001465
Time elapsed: 0.42713308334350586
Time elapsed: 0.4171907901763916
Time elapsed: 0.4220712184906006
Time elapsed: 0.4343099594116211
Time elapsed: 0.4841780662536621
Time elapsed: 0.4685649871826172
Time elapsed: 0.4868438243865967
Time elapsed: 0.4662456512451172
Time elapsed: 0.5436382293701172
Time elapsed: 0.5267820358276367
Time elapsed: 0.5091872215270996
Time elapsed: 0.5081048011779785
Time elapsed: 0.5297191143035889
Time elapsed: 0.5239181518554688
Time elapsed: 0.5485963821411133
Time elapsed: 0.5876140594482422
Time elapsed: 0.5736618041992188
Time elapsed: 0.5639777183532715
Time elapsed: 0.5830345153808594
Time elapsed: 0.6187348365783691
Time elapsed: 0.5978899002075195
Time elapsed: 0.6382076740264893
Time elapsed: 0.6436002254486084
Time elapsed: 0.6225919723510742
Time elapsed: 0.6568043231964111
Time elapsed: 0.7085685729980469
Time elapsed: 0.6644773483276367
Time elapsed: 0.6723501682281494
Time elapsed: 0.6834757328033447
Time elapsed: 0.6862823963165283
Time elapsed: 0.7144062519073486
Time elapsed: 0.75490403175354
Time elapsed: 0.7380294799804688
Time elapsed: 0.7777352333068848
Time elapsed: 0.782534122467041
Time elapsed: 0.8311202526092529
Time elapsed: 0.7933468818664551
Time elapsed: 0.8011541366577148
Time elapsed: 0.8183913230895996
Time elapsed: 0.8311331272125244
Time elapsed: 0.8553295135498047
Time elapsed: 0.8610031604766846
Time elapsed: 0.9558396339416504
Time elapsed: 0.9203469753265381
Time elapsed: 0.9103479385375977
Time elapsed: 0.9160957336425781
Time elapsed: 0.928046703338623
Time elapsed: 0.950376033782959
Time elapsed: 0.9951839447021484
Time elapsed: 0.9768331050872803
Time elapsed: 0.9877028465270996
Time elapsed: 1.0079317092895508
Time elapsed: 1.0394885540008545
Time elapsed: 1.0345790386199951
Time elapsed: 1.0764164924621582
Time elapsed: 1.0987389087677002
Time elapsed: 1.108257532119751
Time elapsed: 1.114212989807129
Time elapsed: 1.1997053623199463
Time elapsed: 1.154165267944336
Time elapsed: 1.1690573692321777

If SelectLayerByAttribute_management is called multiple times, each successive call will take longer and longer. Is there anything I am doing wrong?

0 Kudos
10 Replies
DanPatterson_Retired
MVP Emeritus

Actually, it was erroring out until I made the lyrx to disk for some reason.  When I had Pro open, it was slower.  Couldn't nail it down, but the memory hog is reduced.

As for 

Feature class vs Feature Layer

  just some  Symbology, Labels, Scale Range, Joins, Relates, Definition Query

  I have scratched out the ones I don't use, the others can be done with arrays

I usually summarize text fields in tables using a simple function which does all fields and all conditions at once.  Here is a stripped down example of querying one field and getting a summary.  Normally 'fld' is a list of text fields and it is passed into the summary function

gdb = r"C:\Git_Dan\arraytools\array_tools_testing\array_tools.gdb"
fc = gdb + r"\pnts_2000"
fld = 'Text01'
def summary(fc, fld):
    """ """
    a = arcpy.da.TableToNumPyArray(fc, fld)
    uniq = np.unique(a[fld])
    result =  np.array([[u, a[a[fld] == u].size] for u in uniq])
    out = np.zeros(len(result), dtype=[('Class', 'U8'), ('Count', '<i4')])
    out['Class'] = result[:,0]
    out['Count'] = result[:, 1]
    return out
out = summary(fc, fld)
prn(out)
Array info...
shape... (10,)  ndim... 1  dtype... [('Class', '<U8'), ('Count', '<i4')] 
╔═ 
║ ╔═ 
10 
 id  Class Count  
------------------
 000 A         473
 001 Aa          1
 002 Aaa         1
 003 B         544
 004 Bbbbb       1
 005 C         492
 006 Ccccc       1
 007 D         481
 008 Dddd        1
 009 None        5

milliseconds to complete, and the nice thing is I can store the whole array (geometry and attributes) and its summary in one *.npy file.  Arcpy and NumPy play really nice together... more people should use it