Slow Select By Attribute processing

Question

Hi there,

I have written some code that reads each line of a text file and adds the lines to a list. It then loops through each item in this list and applies the item to a definition query to select By attribute features in a feature layer. Once its finished looping through the list it copies all selected features to a shapefile.

My concern is that it is extremely slow to process and I am wondering if there is anyway I can do to speed the process up or perhaps a different way I could approach this to speed the script up?

Any ideas on how I could speed up my script would be greatly appreciated...

Here's my code:

# Text files                       
output = "D:\Subblocks\SubblocksOutput.txt"

# Set overwrite property
gp.OverWriteOutput = 1

# Make feature layer
gp.MakeFeatureLayer("D:\SUBBLOCKS.shp", "feature_lyr")

# Open output file in read only mode
text_file = open(output, "r")

# Loop through text file and add to list
lines = text_file.readlines()
print len(lines)

for line in lines:
    print line
    query = ""KEY"" + " = " + "\'" + line.rstrip("
") + "\'"
    gp.SelectLayerByAttribute_management("feature_lyr", "ADD_TO_SELECTION", query)

# Copy selected features to shapefile
gp.CopyFeatures_management("feature_lyr", "D:\Subblocks\SELECTED_SUBBLOCKS.shp")

# Close text file
text_file.close()

# Free memory
del gp

MPickering · Answer

Good to know it worked and glad I could return the favour. I think I will use this method too. 😉

Thanks again Thom, you've saved me a lot of frustration and time.

m

ThomMackey · Answer

Good tip! I always forget about SQL's "IN" operator. I just tested it with one of my old scripts, and was able to extract >300,000 unique records in one step - no messing around with merging!

So doing it that way, your script would go something like:
# The field you're querying
queryfield = "KEY"

# Read the file into a list
data = text_file.readlines()

# Strip the data of trailing newlines
cleandata = [x.strip() for x in data]

# Put single quotes around each string for SQL-friendliness
queryterms = ["'%s'"%x for x in cleandata]

# Put ", " between each of the queries
comma_sep_terms = ", ".join(queryterms)

# Then make the full expression
querystring = '"%s" IN (%s)'%(queryfield,comma_sep_terms)

# Use the Select tool once
gp.Select(input_shapefile,output_dataset,querystring)

Much cleaner! It seems odd, because I've always understood that "IN" is procedurally equivalent to multiple "OR"s (if anything a little slower), but for whatever reason, the GP prefers it.

Thanks heaps for mentioning that 😄

MPickering · Answer

Hi Thom,

I was a little worried it wouldn't work for that many records too but when I changed the shapefile to a feature class in a file geodatabase and run the script it worked. Before as a shapefile it just crashed.

I have read on other posts that people have had similar issues with the SQL string failing with too many characters, conditions in the statement. I did see one post that mentioned something about using the 'IN' keyword in the SQL syntax e.g. "KEY" IN ( 'COOK1729A' , 'COOK1729B', etc, etc) and building it that way. They had success with this apparently for a large number of conditions. But I haven't tested it ...

I'll definitely keep the batching code you posted in mind for the future. Its a tidy bit of code that I will no doubt use in the future.

Thanks again Thom.

m