2

I'm working with a table that has over 180,000 records. My code searches through the table and pulls field values (meeting a SQL statement) into a list. This list is used as part of a Search by Attribute SQL statement on a feature class using the IN parameter. For example, SELECT from fc WHERE field IN('value1', 'value2', etc).

Only posting the relevant code here. The problem is that the initial query might return as many as 180k+ records and thus the resulting where clause is a massive list. I've determined that if the list of values (GUIDS as strings) gets over 20,000 or so, the script fails with an "Invalid Expression" error.

The only thing I can think to do is modify this code so that it runs the first query on the table no more than 10,000 rows at a time. That should keep the IN clause manageable. Once that list is used to select features with matching field values in the fc, the next 10k would run as "Add_to_selection."

However, I'm having a hell of a time figuring the best way to manipulate this bit of code to do it that way.

Summary: Both the table and the fc have a field called "Main_ID". The code searches for those values in the table based on a user query, puts them into a list and searches on the "Main_ID" field of fcs in the mxd using a sql expression with an IN clause. I think this line needs to be altered to just do this maybe 10k records at a time:

set([row[0] for row in arcpy.da.SearchCursor(linkTable, linkKeyField, sqlExp)])

Any help?

linkKeyField = "Main_ID"


if len(layers) > 0:
    # Set the SearchCursor to look through the selection of the linkTable
    sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(linkTable, linkKeyField, sqlExp)])
# Add DBMS-specific field delimiters
fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(linkTable).path, linkKeyField)
# Add single-quotes for string field values
valueList = ["'%s'" % value for value in sourceIDs]
# Format WHERE clause in the form of an IN statement
whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))
arcpy.AddMessage("SQL Clause: {}".format(whereClause))
for lyr in layers:
    if len(arcpy.ListFields(lyr, "Main_ID")) > 0:
        # Process: Select Layer By Attribute
        arcpy.AddMessage("Querying related records in {0}".format(lyr))
        arcpy.SelectLayerByAttribute_management(lyr, selectType, whereClause)
  • Why are you using select by attribute? Are you're going to do some calculations on feature class using these records? – FelixIP Oct 13 '17 at 3:32
  • I use SelectLayerByAttribute because I need to potentially have records in multiple fcs selected at the same time. The 'table' in question has relationship classes between it and as many as 15 feature classes. The point of the script was to do a query on the table for something, get all the values from the related field and find all the related records in each feature class and simply select them. – MattS Oct 13 '17 at 12:26
2

There's How do you split a list into evenly sized chunks? on Stack Overflow that describes 'chunking' up a list, integrating that into the code that I can see:

def chunks(l, n): # have this up the top of your code
    """Yield successive n-sized chunks from l."""
    for i in range(0, len(l), n):
        yield l[i:i + n]

if len(layers) > 0:
    # Set the SearchCursor to look through the selection of the linkTable
    sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(linkTable, linkKeyField, sqlExp)])

sourceChunks = list(chunks(sourceIDs,10000))

for sourceIDs in sourceChunks: # re-use the sourceIDs variable now that it's copied to sourceChunks
    # Add DBMS-specific field delimiters
    fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(linkTable).path, linkKeyField)
    # Add single-quotes for string field values
    valueList = ["'%s'" % value for value in sourceIDs]
    # Format WHERE clause in the form of an IN statement
    whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))
    arcpy.AddMessage("SQL Clause: {}".format(whereClause))
    for lyr in layers:
            if len(arcpy.ListFields(lyr, "Main_ID")) > 0:
                    # Process: Select Layer By Attribute
                    arcpy.AddMessage("Querying related records in {0}".format(lyr))
                    arcpy.SelectLayerByAttribute_management(lyr, selectType, whereClause)

If the list is shorter than 10000 then the iteration occurs only once as the conversion is a list of lists, which is a list with one element of a list of ids where the length is less than 10k. Note that the last list could be very short but is guaranteed to be less than 10k elements. There is also the list comprehension [l[i:i + n] for i in range(0, len(l), n)] if you don't want to def a function.

  • Even though I'm exploring a completely different way of going about this, I'm going to test this suggestion and get back to you. But one thing I see that's a potential problem is the 'selectType' in the last line there. If the user picked "New_Selection", then i imagine with each iteration it'll just overwrite the previous selection. – MattS Oct 13 '17 at 14:29
  • Ah yes, it would. You can set selectType = "NEW_SELECTION" prior to entering the loop then on the very next line selectType = "ADD_TO_SELECTION" to increment the selection if you want to get one big selection or alternatively do what you need to do with the selection in batches... it's all the same in the end. – Michael Stimson Oct 15 '17 at 20:53
1

You're working this problem from the wrong end.

If you have an actual database, a Query Layer based on a JOIN between the tables is a fast and easy way to access the features you need.

If you're not using a real database, I recommend you start using one, and use the JOINed Query Layer.

If you cannot use a database, then you'll need to break the relationships into the pieces SQL would implement so easily, and play with selection sets and related tables (which may be brutal to learn -- harder than installing PostgreSQL, certainly).

Did I mention you should use a database for this?

The root issue is that this is an instance of an XY problem. Working with massive lists of lists or lists of massive lists is not the best solution, so you need to take a step back and use the software as it's been designed to be used (from the long end of the lever instead of the short one).

Please do consider using a SQL database. And if you want to lay out the problem that a 180k IN element query is trying to solve, we'd likely be able to find a better solution (which might involve using a real database).

  • It is an sql database with relationship classes between the table in question and the feature classes in the table of contents in the mxd. I developed this script maybe two years ago and only just noticed it's failing when certain queries yield large results. Happy to explore another option but didn't want to start over. – MattS Oct 13 '17 at 12:22
  • Okay, so it's now time to specify the RDBMS in use and describe the root problem (in a new question). IN clauses don't scale unless used in a subquery (which get optimized as a JOIN). The list of lists solution is going to be orders of magnitude slower than a Query Layer. – Vince Oct 13 '17 at 12:52
  • As I mentioned, it's an SQL Server database...sde Geodatabase in arc. I thought I had once previously posted all this (1-2 years ago?) and that's how I ended up w/this mess. I'm not a full-time developer or DBA. I can write python but I'm rusty. But, I'll try to re-address the issue in a new question, as you propose. Thanks – MattS Oct 13 '17 at 13:25
  • I reposted the question @Vince [link]gis.stackexchange.com/questions/258430/… – MattS Oct 13 '17 at 18:47

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.