nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Updating a feature layer with a global sql update

MarkWingfield

Currently, I have a feature layer that I step through row by row and update each row in the layer with output data from a SQL function. This works but is slow. I have been asked to try and speed this up and the suggestion was to attempt some form of global update in one go tat could link the feature class to a function or view and update all rows in one go. The data returned from the current function relies on inputs from the current row to create the returned data. I have attached the current code below:

    message = "[INFO] Looping through selected RoadNetwork started at " + str(datetime.datetime.now())
    print message
    arcpy.AddMessage(message)
    LogFile.write(message + "\n")
    # Call function to get extra columns
    cursor = arcpy.UpdateCursor(FinalNetworkLayerName)
    for featurerow in cursor:
        currentRow = currentRow + 1
        if currentRow%100 == 0:
            message = "[INFO] Processed " + str(currentRow) + " of " + str(RoadNetworkCount) + " rows at " + str(datetime.datetime.now())
            print message
            arcpy.AddMessage(message)
            LogFile.write(message + "\n")
        CurrentToid = featurerow.TOID
        # Establishing connection to MSSQL database

        sql = "SELECT * FROM [DWOP].[dbo].[ufunc_ReturnGGFData] ('" + CurrentToid + "'," + OfficeIDParm
        # Call function
        sde_return = sdeConn2.execute(sql)
        if isinstance(sde_return, list):
            for row in sde_return:
                featurerow.SEG_ID = row[0]
                featurerow.STL_NAME = row[1]
                featurerow.STR_NAME = row[2]
                featurerow.ST_CLASS = row[3]
                featurerow.ORIG_LVL = row[4]
                featurerow.DEST_LVL = row[5]
                featurerow.ONEWAY = row[6]
                featurerow.SPEED1 = row[7]
                featurerow.SPEED2 = row[8]
                featurerow.SPEED3 = row[9]
                featurerow.SPEED4 = row[10]
                featurerow.TRN_MODE = row[11]
                featurerow.BTH_MODE = row[12]
                featurerow.RESTR_ORI = row[13]
                featurerow.RESTR_DEST = row[14]
                cursor.updateRow(featurerow)

    del cursor, featurerow

As I stated above. this works but is quite slow (averaging over 2 seconds per row update).

It seems unlikely to me that such a global update could be done but I am new to ArcPy so may be missing something.

Cheers

Mark

タグ付けされた投稿をもっと見る

Accepted answers

All comments

RichardFairhurst

Thanks everybody. The data dictionary method was superfast and has definitely solved a very thorny problem. I did use makequerytable first to get the data into a layer i could interrogate in testing and then used this to populate the data dictionary.

All works very well

Cheers

Given all the help that Chris and I gave you, you need to assign points and check the post with the best answer to your question. See my signature line and click the link for details on assigning MVP points and answers.

MarkWingfield

Thanks everybody. The data dictionary method was superfast and has definitely solved a very thorny problem. I did use makequerytable first to get the data into a layer i could interrogate in testing and then used this to populate the data dictionary.

All works very well

Cheers

ChrisSnyder

Can you not just run the search cursor (and populate the dictionary) directly on 'fullViewName'? I'm unsure why you are creating a query table. Note there is a parameter for applying SQL expressions directly in the search cursor, although as I recall, maybe you can't use that parameter unless there is a valid (ESRI-recognized) OID field in the table.

To interrogate values in a dictionary, access them by key. So for example:

>>> sampleDict = {"cat": ("Meow Man", 23, 12.34), "dog": ("Bark Bark", 36, 1.432), "mouse":("Minnie Me", 3, 0.123)}
>>> sampleDict["dog"]
("Bark Bark", 36, 1.432)

MarkWingfield

Thanks all for your help so far. I am going with the data dictionary option and am currently working through this. The issue I do have is with debugging, in that, when running my code, it suggests that the data dictionary (I have called this rdlkDict) has been created successfully (based on using a MakeQueryTable step that calls a view returning SQL data to be matched). The data in the MakeQueryTable layer looks to be fully populated when I open it in ArcMap. However, the match does not appear to have updated anything so i need to check contents of the data dictionary. My code is below:

    message = "[INFO]  Create table of general GGF data started at " + str(datetime.datetime.now()) 
    print message
    viewName = "DWOP.DBO.VWO_GenerateGeorouteFilesNonSpatial"
    fullViewName = arcpy.env.workspace + os.sep + viewName
    tableLayerName = "GGF_Data" + OfficeID
    fieldList = [[viewName + ".OBJECTID"],[viewName + ".TOID"],[viewName + ".OFFICE_ID"],[viewName + ".SEG_ID"],[viewName + ".STL_NAME"],[viewName + ".STR_NAME"],[viewName + ".ST_CLASS"],[viewName + ".ORIG_LVL"],[viewName + ".DEST_LVL"],[viewName + ".ONEWAY"],[viewName + ".SPEED1"],[viewName + ".SPEED2"],[viewName + ".SPEED3"],[viewName + ".SPEED4"],[viewName + ".TRN_MODE"],[viewName + ".BTH_MODE"],[viewName + ".RESTR_ORI"],[viewName + ".RESTR_DEST"]]
    whereClause = "OFFICE_ID = " + OfficeID
    arcpy.MakeQueryTable_management(fullViewName,tableLayerName,"USE_KEY_FIELDS",viewName + ".OBJECTID",fieldList,whereClause)  

    message = "[INFO]  Create Data Dictionary of general GGF data started at " + str(datetime.datetime.now()) 
    print message
    mySqlFieldsToReturnList = ["OBJECTID",viewName  + ".TOID",viewName  + ".OFFICE_ID",viewName  + ".SEG_ID",viewName  + ".STL_NAME",viewName  + ".STR_NAME",viewName  + ".ST_CLASS",viewName  + ".ORIG_LVL",viewName  + ".DEST_LVL",viewName  + ".ONEWAY",viewName  + ".SPEED1",viewName  + ".SPEED2",viewName  + ".SPEED3",viewName  + ".SPEED4",viewName  + ".TRN_MODE",viewName  + ".BTH_MODE",viewName  + ".RESTR_ORI",viewName  + ".RESTR_DEST"]
    rdlkDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(tableLayerName, mySqlFieldsToReturnList)}

#Would like to check the rdlkDict values here    

    message = "[INFO]  Looping through selected RoadNetwork started at " + str(datetime.datetime.now()) 
    print message

    updateFieldsList = ["OBJECTID","SEG_ID","STL_NAME","STR_NAME","ST_CLASS", "ORIG_LVL","DEST_LVL","ONEWAY","SPEED1","SPEED2","SPEED3","SPEED4","TRN_MODE","BTH_MODE","RESTR_ORI","RESTR_DEST"]
    with arcpy.da.UpdateCursor(FinalNetworkLayerName, updateFieldsList) as updateRows:
        for updateRow in updateRows:
            ObjectIDVal = updateRow[0]
            currentRow = currentRow + 1
            if currentRow%100 == 0:
                message = "[INFO]  Processed " + str(currentRow) + " of " + str(RoadNetworkCount) + " rows at " + str(datetime.datetime.now())
                print message
            if ObjectIDVal in rdlkDict:
                updateRow[1] = rdlkDict[ObjectIDVal][3]
                updateRow[2] = rdlkDict[ObjectIDVal][4]
                updateRow[3] = rdlkDict[ObjectIDVal][5]
                updateRow[4] = rdlkDict[ObjectIDVal][6]
                updateRow[5] = rdlkDict[ObjectIDVal][7]
                updateRow[6] = rdlkDict[ObjectIDVal][8]
                updateRow[7] = rdlkDict[ObjectIDVal][9]
                updateRow[8] = rdlkDict[ObjectIDVal][10]
                updateRow[9] = rdlkDict[ObjectIDVal][11]
                updateRow[10] = rdlkDict[ObjectIDVal][12]
                updateRow[11] = rdlkDict[ObjectIDVal][13]
                updateRow[12] = rdlkDict[ObjectIDVal][14]
                updateRow[13] = rdlkDict[ObjectIDVal][15]
                updateRow[14] = rdlkDict[ObjectIDVal][16]
                updateRow[15] = rdlkDict[ObjectIDVal][17]
                updateRows.updateRow(updateRow)

How can I interrogate values in rdlkDict and display each row of data so I can check whether the data dictionary is correctly populated?

RichardFairhurst

I will try to adapt my example code to follow my last suggestion which would read the dictionary from the selection set of the output feature class, update the dictionary from a read source that may be much larger, and finally update the output feature class from the updated dictionary:

updateFieldsList =  ["FROM_OBJECTID","FROM_ROUTE_NAME","FROM_MEASURE","FROM_MEAS","FROM_X_COORDINATE","FROM_Y_COORDINATE","FROM_X_Y_LINK","FROM_STREET_NAME","FROM_CROSS_STREETS","FROM_X_Y_ROUTE_NAME"]
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(outputFC, updateFieldsList)}

readFieldsList = ["OBJECTID", "RID", "MEAS", "X_COORD", "Y_COORD", "X_Y_LINK", "STNAME", "STNAMES", "X_Y_ROUTE"]
with arcpy.da.SearchCursor(readFC, readFieldsList) as readRows:
    for readRow in readRows:
        ObjectIDVal = readRow[0]
        if ObjectIDVal in valueDict:
            valueDict[ObjectIDVal][0] = readRow[1]
            valueDict[ObjectIDVal][1] = readRow[2]
            valueDict[ObjectIDVal][2] = readRow[2]
            valueDict[ObjectIDVal][3] = readRow[3]
            valueDict[ObjectIDVal][4] = readRow[4]
            valueDict[ObjectIDVal][5] = readRow[5]
            valueDict[ObjectIDVal][6] = readRow[6]
            valueDict[ObjectIDVal][7] = readRow[7]
            valueDict[ObjectIDVal][8] = readRow[8]

with arcpy.da.UpdateCursor(outputFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        ObjectIDVal = updateRow[0]
        updateRow[1] = valueDict[ObjectIDVal][0]
        updateRow[2] = valueDict[ObjectIDVal][1]
        updateRow[3] = valueDict[ObjectIDVal][2]
        updateRow[4] = valueDict[ObjectIDVal][3]
        updateRow[5] = valueDict[ObjectIDVal][4]
        updateRow[6] = valueDict[ObjectIDVal][5]
        updateRow[7] = valueDict[ObjectIDVal][6]
        updateRow[8] = valueDict[ObjectIDVal][7]
        updateRow[9] = valueDict[ObjectIDVal][8]
        updateRows.updateRow(updateRow)

RichardFairhurst

Given that there was mention of using a list of objectIDs from the FinalNetworkLayerName feature layer, it would be possible for the function to be part of a SQL Select statement and the objectids passed into this as a Where clause. And then I guess this can be called to create a data dictionary but still unsure about the code.

If this was the solution (and given that I would need to deal with chunks of data as I don't believe SQL Server 2008 can deal with over 60000 entries in a single WHERE.....IN (....)... statement), how would I code:

1. Create a data Dictionary for the first chunk of objectIDs from FinalNetworkLayerName, passing these to the WHERE clause of a SQL statement
2. Update the same Data Dictionary for x chunks of objectIDs, also passing to a WHERE clause, until the end of FinalNetworkLayerName

3. Then update FinalNetworkLayerName by matching to the objectIDs in the Data Dictionary

Cheers

Mark

Your concept is wrong and I am no longer advocating that approach. No query where clause filter needs to be built. It can be handled by the conditional python code in the for loop that reads:

ObjectIDVal = updateRow[0]
if ObjectIDVal in valueDict:

As the cursor is read this code performs the function of a where clause against the dictionary you have built to validate that the current ObjectID value exists as a dictionary key. Updates are only done if the key is found.

MarkWingfield

Given that there was mention of using a list of objectIDs from the FinalNetworkLayerName feature layer, it would be possible for the function to be part of a SQL Select statement and the objectids passed into this as a Where clause. And then I guess this can be called to create a data dictionary but still unsure about the code.

If this was the solution (and given that I would need to deal with chunks of data as I don't believe SQL Server 2008 can deal with over 60000 entries in a single WHERE.....IN (....)... statement), how would I code:

1. Create a data Dictionary for the first chunk of objectIDs from FinalNetworkLayerName, passing these to the WHERE clause of a SQL statement
2. Update the same Data Dictionary for x chunks of objectIDs, also passing to a WHERE clause, until the end of FinalNetworkLayerName

3. Then update FinalNetworkLayerName by matching to the objectIDs in the Data Dictionary

Cheers

Mark

RichardFairhurst

Here is a sample of code I just wrote to replace a data transfer operation that originally used joins and field calculations. It affects about 114,000 related records in two tables. It takes about a half minute to processes as compared to about 30 minutes with the original calculations. Assume readFC and outputFC have been defined previously in my code as the two related feature classes. OBJECTID in the readFC joins to FROM_OBJECTID in the outputFC.

readFieldsList = ["OBJECTID", "RID", "MEAS", "X_COORD", "Y_COORD", "X_Y_LINK", "STNAME", "STNAMES", "X_Y_ROUTE"]
valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(readFC, readFieldsList)}

updateFieldsList =  ["FROM_OBJECTID","FROM_ROUTE_NAME","FROM_MEASURE","FROM_MEAS","FROM_X_COORDINATE","FROM_Y_COORDINATE","FROM_X_Y_LINK","FROM_STREET_NAME","FROM_CROSS_STREETS","FROM_X_Y_ROUTE_NAME"]
with arcpy.da.UpdateCursor(outputFC, updateFieldsList) as updateRows:
    for updateRow in updateRows:
        ObjectIDVal = updateRow[0]
        if ObjectIDVal in valueDict:
            updateRow[1] = valueDict[ObjectIDVal][0]
            updateRow[2] = valueDict[ObjectIDVal][1]
            updateRow[3] = valueDict[ObjectIDVal][1]
            updateRow[4] = valueDict[ObjectIDVal][2]
            updateRow[5] = valueDict[ObjectIDVal][3]
            updateRow[6] = valueDict[ObjectIDVal][4]
            updateRow[7] = valueDict[ObjectIDVal][5]
            updateRow[8] = valueDict[ObjectIDVal][6]
            updateRow[9] = valueDict[ObjectIDVal][7]
            updateRows.updateRow(updateRow)

This code is based on the original suggestion that reads the entire related table into a dictionary and not the suggestion of my last post. However, it shows how to retrieve items from a list inside of a dictionary.

RichardFairhurst

I think I need to build the data dictionary first based on the list of rows already in the feature layer FinalNetworkLayerName as this restricts the number of row to be processed to between 3000 and 64000, whereas, in total, there are millions of potential rows to be processed. This still looks to me like I need to build the data dictionary row by row, which seems to me will probably result in the same amount of time taken if that time is taken up by mostly the SQL call istelf. However, if it is the update of the feature layer that is the majority of the 2 seconds taken, then there may be a saving, assuming that the building of the data dictionary is quick.

That would mean attempting to build the data dictionary row by row using the list of all objectIDs from FinalNetworkLayerName. So is there efficient code for this process?

Cheers

Mark Wingfield

Start by making the cursor a da cursor. That is the most important thing to deal with first. The old cursors will cause 10 times the delay. Test your code on a small sample after doing that to see the difference it will make.

I would not build a query with 3,000 to 64,000 ObjectIDs. It will perform horribly since that is too huge of a list.

You could read the selected record set of the 3,000 to 64,000 records into a dictionary and then build a second dictionary doing a complete read of the entire database with millions of records but only add a record to the second dictionary where the key values matched. Then do the UpdateCursor using the second dictionary. Or perhaps you could just create the first dictionary with all of the fields needing updates, read the database with millions of records and if the key values match update the one dictionary and then use the one dictionary with the update cursor. Either of these approaches will blow your current code away and not cause a memory issue.

Your current code is already hitting an index with millions of records repeatedly each time you query the huge database, so a single read of the entire database is nothing in comparison. The single read of the unfiltered database is likely to be faster than a query filter with 3,000 to 64,000 conditions and you should try the above suggestion before creating a where clause with that many conditions.

MarkWingfield

I think I need to build the data dictionary first based on the list of rows already in the feature layer FinalNetworkLayerName as this restricts the number of row to be processed to between 3000 and 64000, whereas, in total, there are millions of potential rows to be processed. This still looks to me like I need to build the data dictionary row by row, which seems to me will probably result in the same amount of time taken if that time is taken up by mostly the SQL call istelf. However, if it is the update of the feature layer that is the majority of the 2 seconds taken, then there may be a saving, assuming that the building of the data dictionary is quick.

That would mean attempting to build the data dictionary row by row using the list of all objectIDs from FinalNetworkLayerName. So is there efficient code for this process?

Cheers

Mark Wingfield

ChrisSnyder

So 1st thing is to get your MSSQL table into a dictionary... I don't use MYSQL, but I image you have some sort of database connection file, right? Also, in my example below, I am assuming the MySQL table has a field called OBJECTID that you are using as the join item, but that's probably not the case. OBJECTID is generally a poor choice for a join field since it is not stable. Anyway...

mySqlTable = r"\\mynetwork\myconnectionfile.sde"
mySqlFieldsToReturnList = ["OBJECTID", "SEG_ID", "STL_NAME", "STR_NAME", ...]
rdlkDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(mySqlTbl, mySqlFieldsToReturnList)}

So now you should have this awesome dictionary where the key is the join item, and all the field values
that you will need in your update cursor are readily available at light speed. The cool thing now is that the dictionary can look up the values from the SQL table a kazzilion times faster than the "sql = "SELECT * FROM [DWOP].[dbo].[ufunc_ReturnGGFDataObjectID" thingy was.

MarkWingfield

I think I get the giste of what you are all saying but I am brand new to Pythons and am still struggling.

Given the code I had before and the fact that a slightly altered version of the SQL function (hopefully to fit in with a data dictionary solution) returns one row of data in the following format with the OBJECTID for the current row as an input:

SEG_ID int,
STL_NAME varchar(100),
STR_NAME varchar(100),
ST_CLASS char(1),
ORIG_LVL int,
DEST_LVL int,
ONEWAY int,
SPEED1 float,
SPEED2 float,
SPEED3 float,
SPEED4 float,
TRN_MODE char(8),
BTH_MODE char(8),
RESTR_ORI varchar(2000),
RESTR_DEST varchar(2000),
TOID varchar(16),
OBJECTID id

My current code using da.UpdateCursor instead of the standard UpdateCursor is:

    fields = ("SEG_ID", "STL_NAME", "STR_NAME", "ST_CLASS", "ORIG_LVL", "DEST_LVL", "ONEWAY", "SPEED1", "SPEED2", "SPEED3", "SPEED4", "TRN_MODE", "BTH_MODE", "RESTR_ORI", "RESTR_DEST", "TOID", "OBJECTID")
    cursor = arcpy.da.UpdateCursor(FinalNetworkLayerName, fields)
    for featurerow in cursor:
        CurrentObjectID = featurerow[17]
        # Establishing connection to MSSQL database

        sql = "SELECT * FROM [DWOP].[dbo].[ufunc_ReturnGGFDataObjectID] (" + CurrentObjectId + "," + OfficeIDParm 
        # Call function
        sde_return = sdeConn2.execute(sql)
        if isinstance(sde_return, list):
            for row in sde_return:
                featurerow[0] = row[0]
                featurerow[1] = row[1]
                featurerow[2] = row[2]
                featurerow[3] = row[3]
                featurerow[4] = row[4]
                featurerow[5] = row[5]
                featurerow[6] = row[6]
                featurerow[7] = row[7]
                featurerow[8] = row[8]
                featurerow[9] = row[9]
                featurerow[10] = row[10]
                featurerow[11] = row[11]
                featurerow[12] = row[12]
                featurerow[13] = row[13]
                featurerow[14] = row[14]
                cursor.updateRow(featurerow)

    del cursor, featurerow

I am looking at a data dictionary definition of :

rdlkDict = dict([(r.OBJECTID, (r.SEG_ID, r.STL_NAME, r.STR_NAME, r.ST_CLASS, r.ORIG_LVL, r.DEST_LVL, r.ONEWAY, r.SPEED1, r.SPEED2, r.SPEED3, r.SPEED4, r.TRN_MODE, r.BTH_MODE, r.RESTR_ORI, r.RESTR_DEST, r.TOID)) for r in arcpy.SearchCursor(FinalNetworkLayerName)])

How would I populate this from the SQl call and then how do I link this to the FinalNetworkLayerName feature class? I cannot get my head around how the loops work and relate to each other to update 15 columns from the SQL call via the data dictionary.

Sorry, I am completely new to Python.

Cheers

RichardFairhurst

After reading your original post, all you are doing is basically a join/calc... the performance of which still can be improved using a dictionary/update cursor. This is some older code (doesn't use .da cursors, dictionary comprehensions, etc.) but it illustrates the traditional join and calc sort of thing you are trying to accomplish.

http://forums.arcgis.com/threads/9555-Modifying-Permanent-Sort-script-by-Chris-Snyder?p=30010&viewfull=1#post30010

The only time I have found that this style is necessary is in a label expression that pulls data from a related table with a One to Many or Many to Many relationship. Because the label expression is repeatedly read for every label generated, almost like the expression is within a cursor loop, there is no benefit to reading the entire related table into a dictionary within the label expression. The dictionary would just be regenerated for every label and no cursor reads would be eliminated.

ChrisSnyder

After reading your original post, all you are doing is basically a join/calc... the performance of which still can be improved using a dictionary/update cursor. This is some older code (doesn't use .da cursors, dictionary comprehensions, etc.) but it illustrates the traditional join and calc sort of thing you are trying to accomplish.

http://forums.arcgis.com/threads/9555-Modifying-Permanent-Sort-script-by-Chris-Snyder?p=30010&viewfull=1#post30010

RichardFairhurst

Some comments and examples:

1. Use a dictionary, as Richard said its way faster than using imbedded cursors, and your bosses will of course give you a raise, since you improved the performance so much.
2. If the dictionary gets "too big"... that is it occupies more than ~2.1 GB of RAM, which is the (sort of) limit of 32-bit Python, you can use 64-bit Python along with the 64-bit "ArcGIS_BackgroundGP_for_Desktop_101sp1.exe", which basically turns arcpy (v10.1+) into a 64-bit bad a$$ capable of using gobs of RAM.

Here's an (untested) example of that dictionary calculaty sort of thing... It sucks and existing table into a dictionary, and then updates the table by calcing the ID field value = to the sum of all the (original) ID values that are >= the ID value being processed. Why would you would want to do that? I don't know, but it illustrates the general concept.
myTable = r"C:\temp\test.gdb\test"
valueDict = {r[0]:(r[1]) for r in arcpy.da.SearchCursor(myTable, ["OID@","ID"])} 
updateRows = arcpy.da.UpdateCursor(myTable, ["OID@","ID"])
for updateRow in updateRows:
   oidVal, idVal = updateRow
   updateRow[1] = sum([i[0] for i in valueDict if valueDict[0] >= idVal])
   updateRows.updateRow(updateRow)
del updateRow, updateRows 

Chris:

Good point. The entire related table can just be read into a dictionary so that it has the complete set of ID keys without using any where clause. If that was done before processing the update cursor then the update loop can just match to the dictionary keys. That way it doesn't matter how big the ObjectID list is and no where clause needs to be generated at all. It also cuts the number of cursor loops down to just two cursor loops that are completely independent of each other.

ChrisSnyder

Some comments and examples:

1. Use a dictionary, as Richard said its way faster than using imbedded cursors, and your bosses will of course give you a raise, since you improved the performance so much.
2. If the dictionary gets "too big"... that is it occupies more than ~2.1 GB of RAM, which is the (sort of) limit of 32-bit Python, you can use 64-bit Python along with the 64-bit "ArcGIS_BackgroundGP_for_Desktop_101sp1.exe", which basically turns arcpy (v10.1+) into a 64-bit bad a$$ capable of using gobs of RAM.

Here's an (untested) example of that dictionary calculaty sort of thing... It sucks and existing table into a dictionary, and then updates the table by calcing the ID field value = to the sum of all the (original) ID values that are >= the ID value being processed. Why would you would want to do that? I don't know, but it illustrates the general concept.

myTable = r"C:\temp\test.gdb\test"
valueDict = {r[0]:(r[1]) for r in arcpy.da.SearchCursor(myTable, ["OID@","ID"])} 
updateRows = arcpy.da.UpdateCursor(myTable, ["OID@","ID"])
for updateRow in updateRows:
   oidVal, idVal = updateRow
   updateRow[1] = sum([i[0] for i in valueDict if valueDict[0] >= idVal])
   updateRows.updateRow(updateRow)
del updateRow, updateRows

RichardFairhurst

This could certainly throw a wrench into the "IN" statement approach --- is there any issues with chunk processing part? One potential alternative idea is to use a Table Variable instead. If the list of OID's is really large you could first run an insert into the table variable then simply JOIN this to the table in your main query. This design approach would be best fit into a StoredProcedure rather than dynamic SQL though (not a bad plan either as the SQL statement will have already been compiled on the server).

I agree that really large ObjectID lists will be a significant factor in deciding the best optimization approach if that is a factor. Such lists are even more of a reason to scrap the approach that creates a new query for every individual ObjectID. Under any circumstance start by using the Data Access version of the Updatecursor, since that will give the code a huge boost and should immediately improve the 2 seconds per record performance which should largely be due to using an old style cursor.

JamesCrandall

If the ObjectID list is crazy huge you may need to chunk it into selection groups with the modulus operator so that the SQL where clause is not excessively large. Then query that group back to a separate layer copy of the input selection layer. The layer copy would be created outside of your loop using the Make Feature Layer operation.

This could certainly throw a wrench into the "IN" statement approach --- is there any issues with chunk processing part? One potential alternative idea is to use a Table Variable instead. If the list of OID's is really large you could first run an insert into the table variable then simply JOIN this to the table in your main query. This design approach would be best fit into a StoredProcedure rather than dynamic SQL though (not a bad plan either as the SQL statement will have already been compiled on the server).

RichardFairhurst

    message = "[INFO]  Looping through selected RoadNetwork started at " + str(datetime.datetime.now()) 
    print message
    arcpy.AddMessage(message)
    LogFile.write(message + "\n")
    # Call function to get extra columns
    cursor = arcpy.UpdateCursor(FinalNetworkLayerName)
    for featurerow in cursor:
        currentRow = currentRow + 1
        if currentRow%100 == 0:
            message = "[INFO]  Processed " + str(currentRow) + " of " + str(RoadNetworkCount) + " rows at " + str(datetime.datetime.now())         
            print message
            arcpy.AddMessage(message)
            LogFile.write(message + "\n")
        CurrentToid = featurerow.TOID
        # Establishing connection to MSSQL database

        sql = "SELECT * FROM [DWOP].[dbo].[ufunc_ReturnGGFData] ('" + CurrentToid + "'," + OfficeIDParm 
        # Call function
        sde_return = sdeConn2.execute(sql)
        if isinstance(sde_return, list):
            for row in sde_return:
                featurerow.SEG_ID = row[0]
                featurerow.STL_NAME = row[1]
                featurerow.STR_NAME = row[2]
                featurerow.ST_CLASS = row[3]
                featurerow.ORIG_LVL = row[4]
                featurerow.DEST_LVL = row[5]
                featurerow.ONEWAY = row[6]
                featurerow.SPEED1 = row[7]
                featurerow.SPEED2 = row[8]
                featurerow.SPEED3 = row[9]
                featurerow.SPEED4 = row[10]
                featurerow.TRN_MODE = row[11]
                featurerow.BTH_MODE = row[12]
                featurerow.RESTR_ORI = row[13]
                featurerow.RESTR_DEST = row[14]
                cursor.updateRow(featurerow)

    del cursor, featurerow

As I stated above. this works but is quite slow (averaging over 2 seconds per row update).

It seems unlikely to me that such a global update could be done but I am new to ArcPy so may be missing something.

Cheers

Mark

First of all, use the Code tags (the # button) around Python code to preserve the spacing in a forum post.

If you are using ArcMap 10.0 or above convert your UpdateCursor to a Data Access Update cursor. They are at least 10 times faster than the old style cursors. Also, supply a field list that limits the fields read to only access the fields you need.

About the only way to speed it up is to loop through all selected features with a SearchCursor first and create a list or dictionary of all ObjectIDs, then input the full list to a single query to the MS SQL database using an IN operation. Then create a dictionary of the entire read of the MS SQL data and all its related fields in one pass using the same ObjectID value as the key. Then do the update code, but use the dictionary key to find the related data instead of performing a query. Dictionary searches are up to 100 times faster than SQL queries. For example, if there are 100 selected features this approach would reduce 101 queries to 3 queries and 2 list/dictionary reads. That should gain a large performance boost.

Queries are expensive because they have to use physical hardware and network throughput to access and transfer the data, while dictionaries are built in local memory. So 100 random access hits by query are much slower than 100 random access hits to a dictionary in memory. A single query of a 100 ObjectID list will be faster, because the cursor read is not random, but sequential, and field indexing has a better effect through 1 query than through 100 queries, since the index is only traversed once for 1 query, but has to be traversed 100 times for 100 queries. (I assume you have an index on all of the related fields your queries hit, since unindexed fields should never be used to create a relationship between 2 database tables.)

If the ObjectID list is crazy huge you may need to chunk it into selection groups with the modulus operator so that the SQL where clause is not excessively large. Then query that group back to a separate layer copy of the input selection layer. The layer copy would be created outside of your loop using the Make Feature Layer operation.

If this is helpful, see my signature line for info on voting for helpful posts.

会話に参加する

サインインしたメンバーは投稿、更新のフォローなどができます。初めてですか？無料アカウントを登録してください。

クイックリンク

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.