stain3565

Updating a feature layer with a global sql update

Discussion created by stain3565 on May 21, 2014
Latest reply on May 30, 2014 by rfairhur24
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

Outcomes