Delete data with SQL / Delete data with geoprocessing script.

NicholasO_Connor · ‎01-12-2011

SDE 9.3.1
Oracle 11.1.0.6.0
Solaris 10

I realise that this could also be posted in the geoprocessing thread but it is more related to what is happening "behind the scences" with SDE and in Oracle.

I have two feature classes stored in SDE:

FC1 point data 70,000 records / not versioned / not registered for editing
FC2 point data 150,000 records / not versioned / not registered for editing

I have written a script that does the following operations:

1. Count Management
2. Delete Features all features
3. Count Management
4. Append Management from a FGDB
5. Count Management

repeat for FC2

If I use gp.deletefeatures in step 2 the script takes 32 minutes to complete.

If I use a delete statement run in SQL developer in step 2 the script takes 8 minutes to complete.

The geoprocessing script and sql developer from Oracle run from my Windows PC.

1) Can anyone explain the big difference in execution time?
2) Is there any reason not to use an SQL delete statement rather than gp.deletefeatures?

Kind Regards.
Nicholas

VinceAngelo · ‎01-13-2011

If your purpose is to delete all features, then why not use TRUNCATE, which will complete
in seconds?

C:\>asc2sde -o init -l trunc_ex,shape -C trunc_ex.ctl -# 70000 -vI 30000
 
ASCII to ArcSDE 9.3 Loader Utility       Thu Jan 13 09:37:56 2011
------------------------------------------------------------------------
  30000 source records read...
  60000 source records read...
  70000 source records read...
Results:
     Records read: 70000
     Rows created: 70000
      Insert time: 26.47 secs (2644.60 TPS)
     Elapsed time: 34.42 secs
 
C:\>sdesql
 
ArcSDE 9.3 SQL Query Tool                Thu Jan 13 09:38:37 2011
------------------------------------------------------------------------
1> delete from trunc_ex where 1=1;
 Execution complete in 29.94 secs
1> quit
      1 command completed in 00:00:48.4
 
C:\>asc2sde -o init -l trunc_ex,shape -C trunc_ex.ctl -# 70000 -vI 30000
 
ASCII to ArcSDE 9.3 Loader Utility       Thu Jan 13 09:39:31 2011
------------------------------------------------------------------------
  30000 source records read...
  60000 source records read...
  70000 source records read...
Results:
     Records read: 70000
     Rows created: 70000
      Insert time: 24.73 secs (2830.11 TPS)
     Elapsed time: 31.86 secs
 
C:\>sdesql
 
ArcSDE 9.3 SQL Query Tool                Thu Jan 13 09:40:07 2011
------------------------------------------------------------------------
1> truncate table trunc_ex;
 Execution complete in 688.00 ms
1> exit
      1 command completed in 00:00:24.8

The 'init' option of ArcSDE import utilities use SE_table_truncate to prepare the target
table for repopulation; the 9.3.1 version even re-initializes the registered rowid column's
sequence to start at 1.

C:\>asc2sde -o create -l trunc_ex,shape -k ST_GEOMETRY -g 2 -C trunc_ex.ctl -v
 
ASCII to ArcSDE 9.3 Loader Utility       Thu Jan 13 09:30:33 2011
------------------------------------------------------------------------
Creating table 'trunc_ex'...
Array-insert mode enabled...
Loading data:
    100 source records read...
Changing layer to NORMAL I/O mode...
Results:
     Records read: 100
     Rows created: 100
      Insert time: 46.00 ms (2173.91 TPS)
     Elapsed time: 672.00 ms
 
C:\>sdesql
ArcSDE 9.3 SQL Query Tool                Thu Jan 13 09:30:44 2011
------------------------------------------------------------------------
1> truncate table trunc_ex;
 Execution complete in 172.00 ms
1> quit
      1 command completed in 00:00:16.4
 
C:\>asc2sde -o append -l trunc_ex,shape -C trunc_ex.ctl -v
 
ASCII to ArcSDE 9.3 Loader Utility       Thu Jan 13 09:31:18 2011
------------------------------------------------------------------------
Array-insert mode enabled...
Loading data:
    100 source records read...
Results:
     Records read: 100
     Rows created: 100
      Insert time: 454.00 ms (220.26 TPS)
     Elapsed time: 735.00 ms
 
C:\>sdesql
 
ArcSDE 9.3 SQL Query Tool                Thu Jan 13 09:31:21 2011
------------------------------------------------------------------------
1> select min(objectid),max(objectid) from trunc_ex;
      1
    MIN(OBJECTID): 101.0000000000
    MAX(OBJECTID): 200.0000000000
      1 row found in 15.00 ms
1> truncate table trunc_ex;
 Execution complete in 94.00 ms
1> quit
      2 command completed in 00:00:32.6
 
C:\>asc2sde -o init -l trunc_ex,shape -C trunc_ex.ctl -v
 
ASCII to ArcSDE 9.3 Loader Utility       Thu Jan 13 09:33:15 2011
------------------------------------------------------------------------
Truncating table 'trunc_ex'...
Changing layer to LOAD_ONLY I/O mode...
Array-insert mode enabled...
Loading data:
    100 source records read...
Changing layer to NORMAL I/O mode...
Results:
     Records read: 100
     Rows created: 100
      Insert time: 63.00 ms (1587.30 TPS)
     Elapsed time: 610.00 ms
 
C:\>sdesql
 
ArcSDE 9.3 SQL Query Tool                Thu Jan 13 09:33:22 2011
------------------------------------------------------------------------
1> select min(objectid),max(objectid) from trunc_ex;
      1
    MIN(OBJECTID): 1.0000000000
    MAX(OBJECTID): 100.0000000000
      1 row found in 0 secs

I don't see any obvious references to table truncation in the gp tools, though.

- V

NicholasO_Connor · ‎01-13-2011

Hello Vince.

>> then why not use TRUNCATE

My understanding is of using the TRUNCATE command is the Table (Feature Classe) must be free of active connections?

On both FC1 and FC2 we have ArcIMS and ArcGIS Server connections even at 3am in the morning. Hence my use of a DELETE statement.

Is this correct?

VinceAngelo · ‎01-13-2011

I can't recall ever having an 'init' fail due to an ArcIMS or AGS app running. Of course, I disable
auto-locking when I create my layers (-L OFF) and don't regularly run IMS or AGS against my
database development servers.

It's probably wise to shut down services during wholesale replacement of database contents,
just to prevent inconsistency. From that respect, both tables could be truncated and reloaded
in less than the time it's taking to execute either DELETE.

When I have regular updates that contain changes, and a desire to make those changes without
a scheduled outage, I'm likely to use a change detection algortihm against the source, splitting
the replacement job into a smaller set of insert, update, and delete operations. Then every once
in a while I'll need to export the whole table out in optimized order, then reload to preserve
system efficiency.

- V

RobertHu · ‎01-13-2011

Just curious.

I frequently run deletefeature tool, either manually or in scripts, to remove all features in FCs on both Oracle based GDB (hp-ux/Oracle 10.2.0.3/SDE 9.3.1) and SQL Server based GDB (SQL Server 2008 R2/SDE 10.0). The features in FCs range from a few to a few millions. The process time never passed 30 seconds mark. Actually I just tried to delete 2.3 millions features from a FC on a SQL Server GDB. It was 1 second. And I did it from a client computer.

Why did it take you so long to do the same thing? Are you sure the time difference is caused by the deletefeatures process?

NicholasO_Connor · ‎01-13-2011

>> Why did it take you so long to do the same thing? Are you sure the time difference is caused by the deletefeatures process?

Ok. I made some further tests. Please note I have made changes to the relative pathways of sde connections so not to disclose too much info on a public forum.

1) In my vbs script, I have two simple functions 1) deletefeatures 2) Append_management

From the timestamp you can see the delete took 17 minutes (15:13:56
- 15:30:23) the append took 4 minutes (15:30:23 - 15:34:06)

Function deletefeatures(sData, sStep)

WriteToFile sGLOBAL_TextFile, vbCrLf & "------------------------------------------", True
WriteToFile sGLOBAL_TextFile, vbCrLf & sStep & ") Delete Features", True
WriteToFile sGLOBAL_TextFile, vbCrLf & "------------------------------------------", True

if iMAKE_IMPORT = "YES" then
gp.deletefeatures sData
else
WScript.StdOut.WriteLine "[TEST MODE]"
WriteToFile sGLOBAL_TextFile, vbCrLf & "[TEST MODE]", True
end if

WScript.StdOut.WriteLine sStep & ") deletefeatures: " & sData
WriteToFile sGLOBAL_TextFile, vbCrLf & "deletefeatures: " & sData & " : " & Now(), True

WriteToFile sGLOBAL_TextFile, vbCrLf & " ", True

End Function

------------------------------------------
h) Get Count Management
------------------------------------------
GetCount_management: xyz\sde\FC2 : 181792 : 12.01.2011 15:13:56
------------------------------------------
i) Delete Features
------------------------------------------
deletefeatures: xyz\sde\FC2 : 12.01.2011 15:30:23

Function Append_management(sData, sData1, sStep)

WriteToFile sGLOBAL_TextFile, vbCrLf & "------------------------------------------", True
WriteToFile sGLOBAL_TextFile, vbCrLf & sStep & ") Append Management", True
WriteToFile sGLOBAL_TextFile, vbCrLf & "------------------------------------------", True

if iMAKE_IMPORT = "YES" then
gp.Append_management sData, sData1, "NO_TEST"
else
WScript.StdOut.WriteLine "[TEST MODE]"
WriteToFile sGLOBAL_TextFile, vbCrLf & "[TEST MODE]", True
end if

WScript.StdOut.WriteLine sStep & ") Append_management: " & sData
WriteToFile sGLOBAL_TextFile, vbCrLf & "Append_management: " & sData & " : " & Now(), True
WScript.StdOut.WriteLine sStep & ") Append_management: " & sData1
WriteToFile sGLOBAL_TextFile, vbCrLf & "Append_management: " & sData1 & " : " & Now(), True

WriteToFile sGLOBAL_TextFile, vbCrLf & " ", True

End Function

------------------------------------------
j) Get Count Management
------------------------------------------
GetCount_management: xyz\sde\FC2: 0 : 12.01.2011 15:30:23

------------------------------------------
k) Append Management
------------------------------------------
Append_management: xyz\x.gdb\FC2 : 12.01.2011 15:34:06
Append_management: xyz\sde\FC2 : 12.01.2011 15:34:06

2) When I run "DeleteFeatures" from ArcCatalog / ArcToolbox it took 1 seconds to delete ??? and 3 minutes 48 seconds to append.

Executing: DeleteFeatures "xyz\sde\FC2" "xyz\sde\FC2"
Start Time: Fri Jan 14 09:15:38 2011
WARNING 000117: Warning empty output generated.
Executed (DeleteFeatures) successfully.
End Time: Fri Jan 14 09:15:39 2011 (Elapsed Time: 1.00 seconds)

Executing: Append 'xyz\x.gdb\FC2' "xyz\sde\FC2" NO_TEST
Start Time: Fri Jan 14 09:19:08 2011
Executed (Append) successfully.
End Time: Fri Jan 14 09:22:56 2011 (Elapsed Time: 3 minutes 48 seconds)

OPEN QUESTION:
Now I can't see when why my script takes so long to delete?

Just curious.

I frequently run deletefeature tool, either manually or in scripts, to remove all features in FCs on both Oracle based GDB (hp-ux/Oracle 10.2.0.3/SDE 9.3.1) and SQL Server based GDB (SQL Server 2008 R2/SDE 10.0). The features in FCs range from a few to a few millions. The process time never passed 30 seconds mark. Actually I just tried to delete 2.3 millions features from a FC on a SQL Server GDB. It was 1 second. And I did it from a client computer.

Why did it take you so long to do the same thing? Are you sure the time difference is caused by the deletefeatures process?

anthonysanchez · ‎01-14-2011

Hello,
I'd suggest running a level 12 trace and include waits and binds during your next test. The formatted trace results (tkprof) will tell us if the problem is in the database or on the client.

Anthony

NicholasO_Connor · ‎01-16-2011

Hello Anthony.

Thanks for the advice.

I assume it must be client side? Since in ArcCatalog the delete process took 1 second and when I called the same geoprocessing object in my script it took 17 minutes ???

Cheers
N.

Hello,
I'd suggest running a level 12 trace and include waits and binds during your next test. The formatted trace results (tkprof) will tell us if the problem is in the database or on the client.

Anthony

RandyKreuziger · ‎01-16-2011

Under SDE 9.2 my deletefeatures took only a few seconds to run. When I upgraded to 64-bit SDE 9.3.1 (SQL 2008) it started taking 20 minutes or more. What I didn't realize is that my ArcGIS client was still at 9.2. Once I upgraded to ArcGIS 9.3.1 matching ArcSDE it went back to normal.

NicholasO_Connor · ‎01-17-2011

PERFECT !! That did the trick.

I was using ArcGIS client 9.2; now upgraded to ArcGIS client 9.3.1 and the delete statement went from 17 minutes to 1 second - slight improvement 😉

Thank you EVERYONE for answering.

Under SDE 9.2 my deletefeatures took only a few seconds to run. When I upgraded to 64-bit SDE 9.3.1 (SQL 2008) it started taking 20 minutes or more. What I didn't realize is that my ArcGIS client was still at 9.2. Once I upgraded to ArcGIS 9.3.1 matching ArcSDE it went back to normal.