I'm writing a python script that uses 'NOT IN ('a', 'b', ...)'
statements on very large lists (like comparing 500 items in one feature class against >10k items in another) and am getting weird unexpected results (sometimes it works, sometimes it doesn't and it corrupts the geodatabase…?!?).
I've looked and can't find any ArcMap/arcpy documentation on how large a list can be using IN/NOT IN syntax and was wondering if anyone knew. Using ArcGIS 10.3.1 with 64-bit python.
Update: All of the data in question is stored in file geodatabases.
Best Answer
The limit imposed by the Select By Attributes window in ArcMap is 26114 symbols. I have just generated a list of integers and pasted in the dialog box:
Now just paste into the dialog box and see how it is truncated (
...
replaces the sequence, just to keep it short for posting here):Regarding Python, I am able to execute this code both in Python 32 and 64 bit. Tests done on ArcGIS 10.3.1. Doing selection both with Make Feature Layer and Select Layer By Attributes having in the selection tuple 100K+ items is no problem at all.
One thing to note is that having an index calculated on your field will speed up significantly the selection (this is what I've found from my tests). So, check whether you have the index calculated on the field you are doing selection.
In terms of performance, the Make Feature Layer with the IN statement in the where clause takes the following time to execute (the fc with 2.5mln rows is used with randomly generated numbers in range (1,10K):