topic Re: counting search cursor results in Geoprocessing Questions

counting search cursor results

MikeTischler — Fri, 03 Sep 2010 12:24:28 GMT

Hi,
I'm using the arcpy.SearchCursor code to query a standalone table. All I really need is the count of the results - not any of the actual data. The searchcursor itself finishes rather quickly, but iterating through the search cursor takes a surprisingly long time.

Here's the method I'm using. Is there a faster way to get what I need?

 rows = arcpy.SearchCursor(table,searchstring)

                #count the number of rows in output   
                count = 0
               
                for row in rows:
                    count += 1

Re: counting search cursor results

KevinHibma — Fri, 03 Sep 2010 14:39:26 GMT

For sure, "Get Count":
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//0017000000n7000000.htm

If you need a selection/search on it, you can do a "make table view" then "select by attribute" Adding these couple steps might slow it down a little, however if you just want the number of rows in a table, featureclass, etc - Get Count should be the fastest.

Re: counting search cursor results

MikeTischler — Fri, 03 Sep 2010 14:59:14 GMT

Thanks!

I tried making a table view and then running GetCount on that, and it didn't seem any faster than what I was doing before. And unfortunately, I can't run GetCount on the SearchCursor object.

thanks,
mike

Re: counting search cursor results

ChrisSnyder — Sun, 12 Dec 2021 02:36:20 GMT

Just use the SQL paramater of either the MakeFeatureLayer or MakeTableView tool, then the GetCount tool.

It should take like 1 second. Some v9.3 code for example:

gp.MakeFeatureLayer_management(indxTileFC, indxTileFL, "FID_local_know_nw > -1")
if int(gp.GetCount_management(indxTileFL).getoutput(0)) > 0:
   gp.Blah_managment(this, that, theOther)

Re: counting search cursor results

MikeTischler — Fri, 03 Sep 2010 17:48:48 GMT

That method does work, but it isn't any faster (at least with my tables) than using a search cursor and iterating through it.

Re: counting search cursor results

ChrisSnyder — Wed, 08 Sep 2010 02:23:15 GMT

How many rows are in your table?

Re: counting search cursor results

DaleHoneycutt — Wed, 08 Sep 2010 03:41:01 GMT

This is a mystery -- Get Count (using the mechanics that Chris showed) should be WAY faster than cycling through the records with a cursor. There's something about your data or process that we're missing -- any other clues (besides # of records, like Chris asks?)

Re: counting search cursor results

MikeTischler — Sun, 12 Dec 2021 02:36:23 GMT

Sorry for the late response - my email filter sent the reply notices to my spam folder. Go figure.

There are a substantial amount of records for some tables....3-4 million in a few cases.

A test with a 2,130,000 records took 19.07 seconds using the following code:

from arcpy import *
from stopwatch import clockit
@clockit
def maketv_count(table):
    
    arcpy.MakeTableView_management(table,"mytv5k","NEAR_DIST < 1000")
    arcpy.GetCount_management("mytv5k")

using a search cursor and iterating through took 19.43 seconds using this code:

def maketv_cursor(table):
 
    rows = arcpy.SearchCursor(table,"NEAR_DIST < 1000")
    count = 0
    for row in rows:
        count += 1

Granted, 2 million records is a LOT...but I didn't expect the methods to be nearly identical in their speed.

Thanks
mike

Re: counting search cursor results

ChrisSnyder — Sun, 12 Dec 2021 02:36:25 GMT

Well.... That's a lot of records - seems 20 seconds would be a reasoable time to expect to return the number of selected records in 2.1 million record table! However:

1. If you add an attribute index, the performance may increase (probably for both the cursor and getcount methods): http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000005z000000.htm and http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//005s00000011000000.htm

2. This probably isn't any faster (could be for some weird reason though?), but there is a nifty geoprocessing scripting function that will return the OIDs of a selected set of records within a feature layer. From that you can gleen the selected count. For example:

gp.MakeFeatureLayer_management(fc, "fl")
gp.SelectLayerByAttribute_management("fl", "NEW_SELECTION", "WIDGETS > 30")
fidSet = gp.describe("fl").fidset #this returns a big honking text string - can be millions of charatcters long in my experience!
selectedOidList = []
for fid in fidSet.split(";"):
   selectedOidList.append(fid)
print len(selectedOidList)

Re: counting search cursor results

ChrisSnyder — Thu, 09 Sep 2010 21:24:02 GMT

Also, I should mention that if you have to find the selected record count repeated (like in some sort of loop), I would suggest investigating a Python dictionary object. Basically you have to take an initial time hit by loading the table's records into the dictionary, but once it's there.... Holy crap it's fast (light speed and not magnetic speed)!

See http://forums.arcgis.com/threads/8428-Setting-a-Single-Record-to-Selected-with-Python?p=25930&viewfull=1#post25930

Re: counting search cursor results

KimOllivier — Fri, 10 Sep 2010 13:04:14 GMT

Surely you don't need to iterate the split list?
It is already a list after a split

print len(fidSet.split(";"))

just being economical.

Re: counting search cursor results

MikeTischler — Sun, 12 Dec 2021 02:36:28 GMT

Chris,
Thanks for the tips.

Re: suggestion 1, I don't think I can use SelectLayerByAttribute because I have a standalone table.

Re: suggestion 2, I thought this might be the winner, since I do have a series of searches to run over the same table. However, the overhead in creating the 2.3mil key:value pairs was REALLY expensive. I let my script run for about 6-7 minutes, and the dictionary hadn't yet been created...let alone complete any searches.

I thought I might be able to leverage some functionality from numpy, since all my values are numeric. Turns out there is a pretty cool way to load an array (my_array = numpy.fromiter) but that, too, was quite expensive.

For comparison sake, I used a smaller table with ~160K records. For each of the trials, I started a fresh ArcMap session. I'm not sure if the individual methods are coded the best, but here's the times and code to run 10 searches over the same table:

by cursor:14.17s

by making table and counting: 28.4s

by dictionary: 98.5s

from arcpy import *
from stopwatch import clockit
from numpy import *

@clockit
def do_search_by_count(table):
    count = 0
    for i in range(100,1000,100):
        searchstring = "NEAR_DIST < " + str(i)
        count += make_table_and_count(table, searchstring)
    print "done by count: " + str(count)
    return count

@clockit
def do_search_by_cursor(table):
    count = 0
    for i in range(100,1000,100):
        searchstring = "NEAR_DIST < " + str(i)
        count += cursor_search_count(table,searchstring)    
    print "done by cursor: " + str(count)
    return count

@clockit
def do_search_by_dict(table):

    lookupDict = create_dict(table)    
    count = 0    
    for i in range(100,1000,100):    
        count += count_dict(lookupDict,i)
    print "done with dict: " + str(count)
    return count

@clockit
def make_table_and_count(table,searchstring):        
        arcpy.MakeTableView_management(table,"mytv5k",searchstring)
        x = int(str(arcpy.GetCount_management("mytv5k")))
        return x
    
@clockit
def cursor_search_count(table,searchstring):
    rows = arcpy.SearchCursor(table,searchstring)
    count = 0
    for row in rows:
        count += 1
    return count
    
@clockit
def create_dict(table):
    lookupDict = {}
    searchRows = arcpy.SearchCursor(table)
    for myrow in searchRows:
       lookupDict[myrow.OBJECTID] = [myrow.NEAR_DIST]
      
    del myrow
    del searchRows
    print "created dict"
    return lookupDict

@clockit
def count_dict(lookupDict,dist):
    count =0
    for k,v in lookupDict.iteritems():
        if v[0] < dist:
            count += 1
    return count

Thanks again for the tips.

Mike

Re: counting search cursor results

ChrisSnyder — Fri, 10 Sep 2010 14:50:37 GMT

It is already a list after a split

Yes, typing faster than I was thinking...

Re: counting search cursor results

MikeTischler — Sun, 12 Dec 2021 02:36:31 GMT

And if anyone's interested, the numpy-based method clocked in at 62.6s

@clockit
def do_search_by_numpy(table):
    my_array = numpy.fromiter(countrows(table),dtype=numpy.dtype(float))
    count = 0    
    for i in range(100,1000,100):    
        count += numpy.size(where(my_array < i))
    print "done with dict: " + str(count)
    return count

@clockit
def countrows(table):
    searchRows = arcpy.SearchCursor(table)
    for myrow in searchRows:
        yield myrow.NEAR_DIST

Re: counting search cursor results

ChrisSnyder — Sun, 12 Dec 2021 02:36:33 GMT

suggestion 1, I don't think I can use SelectLayerByAttribute because I have a standalone table

In fact you can use a table with SelectLayerByAttribute, but use the MakeTableView tool 1st (in lieu of MakeFeatureLayer), and use the tableview as input.

Re: suggestion 2, I thought this might be the winner, since I do have a series of searches to run over the same table.

The dictionary idea is probably only a good idea if you have to search the table a whole lot (like hundreds of times - as noted in another thread, it can be a good replacement for an embedded cursor, or some sort of tracing algorithm), because you right, there is a lot of overhead, but maybe something basic like this would work:

#v9.3 code BTW
dict = {}
searchRows = gp.searchcursor(tblPath)
searchRow = searchRows.next()
while searchRow:
   dict[searchRow.MY_KEY] = [searchRow.FIELD1,searchRow.FIELD2] #this could be a tuple instead of a list to save memory if you didn't plan on changing the values... 
   searchRow = searchRows.next()
del searchRow
del searchRows

#yes, there's probably a faster/more elegant way to do this... Kim? .iteritems or .itervalues any faster?
rowCounter1 = 0
rowCounter2 = 0
for myKey in dict:
   if dict[myKey][0] == 100 or dict[myKey][1] == "dog":
      rowCounter1 = rowCounter1 + 1
   if dict[myKey][0] == 200 or dict[myKey][1] == "cat":
      rowCounter2 = rowCounter2 + 1   
print rowCounter1
print rowCounter2

Re: counting search cursor results

ChrisSnyder — Sun, 12 Dec 2021 02:36:36 GMT

Just playing around with it, but this code (1.5 million keys) took less than 1 second to execute on my machine:

dict = {}
for item in range(1,1500000):
   dict[item] = random.uniform(1,10) #return a random float
count = 0
for item in dict:
   if dict[item] > 5:
      count = count + 1
print count

>>> 833384

Re: counting search cursor results

MikeTischler — Fri, 10 Sep 2010 16:22:22 GMT

huh

I tried your code in the last reply in the python window inside arcmap, and it took 46s and change.

From a pythonwin command line, it took just over 1s.

hmmm...

Re: counting search cursor results

ChrisSnyder — Fri, 10 Sep 2010 16:34:00 GMT

Hmm indeed.

I'm pretty much PythonWin all the way... That new-fangled PythonWindow is strange and scary to me. I still can't figure out how to execute simple Spatial Analyst commands though the blasted thing - the help seems totally contradictory! Good ole' SingleOutpuMapAlgebra tool.... look what they have done to you!

Re: counting search cursor results

MikeTischler — Fri, 10 Sep 2010 16:38:25 GMT

Wow - so running the same scripts in a pythonwin window yielded the following results.

search by making table and getcount: 4.3s
search by iterating cursor: 6.5s
search by dictionary: 16.18
search by numpy: 12.54

obviously, I need to get out of the python window in ArcGIS 10.

Re: counting search cursor results

ChrisSnyder — Fri, 10 Sep 2010 16:45:09 GMT

obviously, I need to get out of the python window in ArcGIS 10

ESRI/Anyone know why there is a performance issue here? I thought that gp-type code (like cursors, ESRI tools, etc.) was supposed to run faster in the Toolbox and PythonWindow environments compared to extrenal IDEs like PythonWin and Wing... What happened to that "in process" thing?