AnsweredAssumed Answered

Efficient way to delete all table rows

Question asked by evtguy on Dec 16, 2015
Latest reply on Dec 18, 2015 by matthewrusso

I'm not a Python expert so I'm wondering if my script is already the most efficient way of doing this or if there's a better way. We have a table in SDE (SQLServer2012 back end FWIW) and we're running a Windows Task scheduled service to pull records from an Access Database over to the SDE table every 15 minutes. Because the table is providing content to a web map, I cannot delete the table (at least I don't think I can without affecting the web map). For this reason, I chose to develop a script that empties the table of all records and then inserts the updated records. Usually, we're talking about 2800-3400 records at any given time.

 

Digging through my script, I'm seeing that the initial step of deleting the existing records is taking *much* longer than the task of copying records from the Access database into the SDE world. I'm quite shocked by that; I suspected that the reverse was true. Here is my script:

 

import sys
import os
import linecache
import logging
import arcpy
import time
from datetime import datetime
from arcpy import env


file01 = r"\\pmc-floodwatch\DIADvisorDatabases\DvLive.mok" #This file must exist
file02 = r"\\pmc-floodwatch\DIADvisorDatabases\DvLive.mno" #This file must NOT exist
expression = '1=1' #SQL shorthand which select all records
theTable = "SPW_GIS_PROD.SPW_GDBA.HYDROGRAPHY__tblGageData"


#Establish the error log file 
logger = logging.getLogger('errorLog')
hdlr = logging.FileHandler(r'\\python\errorLog.log')
logger.addHandler(hdlr)


# The tables within DIADvisor must not be accessed during its daily database maintenance.
# OneRain recommends checking for the existence and non-existence of two specific files.
# If both conditions are true, it is safe to proceed with connecting to the data within
# the dvLive Access database
if os.path.exists(file01) and not os.path.exists(file02):
        print "=================================================="
        print "Processing start time: " + str(datetime.now())
        print "==================================================" + "\n"
        
        env.workspace = r"C:\Users\spwscc\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\SPW_GDBA@SPW_GIS_PROD@SQL2012-SQL127.sde"
        try:
                # Set some local variables
                tempTableView = "gageTableView"


                # Execute MakeTableView
                arcpy.MakeTableView_management(theTable, tempTableView)


                # Execute SelectLayerByAttribute to select all records
                arcpy.SelectLayerByAttribute_management(tempTableView, "NEW_SELECTION", expression)


                print "     * Deleting existing records in SDE: " + str(datetime.now())
                timeDelStart = time.time()


                # Execute GetCount and if some records have been selected, then execute
                #  DeleteRows to delete the selected records.
                if int(arcpy.GetCount_management(tempTableView).getOutput(0)) > 0:
                        arcpy.DeleteRows_management(tempTableView)


                timeDelEnd = time.time()
                timeDelElapsed = timeDelEnd - timeDelStart
                print "          Time elapsed: " + str(timeDelElapsed) + " seconds" + "\n"
                
                # Now connect to the DIADvisor access database and import the most recent data
                # This requires the OLD DB connection previously established using ArcCatalog
                counter = 0


                print "     * Starting record transfer from DVLive: " + str(datetime.now())
                timeStartTransfer = time.time()
                
                accessRows = arcpy.SearchCursor(r"C:\Users\spwscc\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\jetConnectForDvLive.odc\last3days")
                curSde = arcpy.InsertCursor(theTable)
                # Loop through the results returned via the OLE DB connection
                for cRow in accessRows:
                    curSensorId = cRow.sensor_id
                    curEpoch = cRow.epoch
                    curData = cRow.data
                    curDataValue2 = cRow.dataValue2                                     
                    counter += 1


                    #Insert a new row into the SDE table with the current DIADvisor record's information
                    row = curSde.newRow()
                    row.SENSOR_ID = curSensorId
                    row.EPOCH = curEpoch
                    row.DATA = curData
                    row.dataValue2 = curDataValue2
                    curSde.insertRow(row)


                timeEndTransfer = time.time()
                timeTransferElapsed = timeEndTransfer - timeStartTransfer
                print "          Time Elapsed: " + str(timeTransferElapsed) + " seconds" + "\n"
                
                # We're done so perform some variable cleanup
                del row
                del accessRows
                del curSde
                del cRow
                print "=================================================="                
                print "Processing end time: " + str(datetime.now())
                print "==================================================" + "\n"                
                print "Number of record(s) in the DIADvisor database: " + str(counter) + "\n"
        except Exception as e:
                # If an error occurred, print line number and error message
                exc_type, exc_obj, exc_tb = sys.exc_info()
                fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
                theMessage = "\n" + 80*"#" + "\n" + 80*"#" + "\n"
                theMessage = theMessage + "DATE/TIME: " + str(datetime.now()) + ":" + "\n"
                theMessage = theMessage + "EXECPTION: " + str(e) + "\n" + "\n"
                theMessage = theMessage + "CALLBACK TRACE: " + "\n"
                theMessage = theMessage + 20*" " + "File: " + str(exc_tb.tb_frame.f_code.co_filename) + "\n"
                theMessage = theMessage + 20*" " + "Line " + str(exc_tb.tb_lineno) + ": " + str(linecache.getline(exc_tb.tb_frame.f_code.co_filename, exc_tb.tb_lineno))
                theMessage = theMessage + 20*" " + "Exception Type: " + str(exc_type)
                print theMessage
                logger.error(theMessage)
else:
        sys.exit()

 

In terms of runtimes, here's a quick sampling:

 

==================================================

Processing start time: 2015-12-16 12:49:01.322000

==================================================

 

 

     * Deleting existing records in SDE: 2015-12-16 12:49:03.491000

          Time elapsed: 66.4879999161 seconds

 

 

     * Starting record transfer from DVLive: 2015-12-16 12:50:10.022000

          Time Elapsed: 4.12600016594 seconds

 

 

==================================================

Processing end time: 2015-12-16 12:50:14.157000

==================================================

 

 

Number of record(s) in the DIADvisor database: 3167

 

 

>>> ================================ RESTART ================================

>>>

==================================================

Processing start time: 2015-12-16 13:00:50.661000

==================================================

 

 

     * Deleting existing records in SDE: 2015-12-16 13:00:53.012000

          Time elapsed: 71.2430000305 seconds

 

 

     * Starting record transfer from DVLive: 2015-12-16 13:02:04.295000

          Time Elapsed: 4.01099991798 seconds

 

 

==================================================

Processing end time: 2015-12-16 13:02:08.314000

==================================================

 

 

Number of record(s) in the DIADvisor database: 3173

So- am I already clearing the contents of my SDE table in the most efficient way possible or is there a better way?

 

Thanks!

Steve

Outcomes