76 million rows...SDE st_geometry vs Oracle sdo_geometry...

[JohnLewis]

Does anyone know of why the sde.st_geometry functions take so long to run?  I need convert 76 million lat/lon's to shape objects. 

We're running on a Windows 2008 server using Oracle 11gR2 64bit with 64G ram and 24 core processors.

Following is the code I used to accomplish this and the results of both the SDE methods and the Oracle methods.

--Oracle sdo_geomtry�?�

create table sample_points (
RECORDID,
SHAPE) nologging as
  (select  /*+ NO_PARALLEL_INDEX(jl) */
   tab_row_id,
sdo_geometry(2001, null,
sdo_point_type(longitude, latitude, null)
null,null)
from jl_points_all jl);

Table created.

Elapsed: 00:01:24.29

--ESRI st_geometry�?�

create table sample_points (
   RECORDID,
   SHAPE) nologging as
   (select  /*+ NO_PARALLEL_INDEX(jl) */
           tab_row_id,
           sde.st_geometry (longitude, latitude, null, null,0)
   from jl_points_all jl);

--Still running after 11 hours �?? only 4% of the datablocks read from the source table.

Does anyone know of faster methods using the SDE functions?

Thanks in advance

John

[NidhinKarthikeyan]

John,

You can post the question on Geodatabse and ArcSDE.

[VinceAngelo]

I regularly load 8 million row GeoNames tables in minutes using the 'asc2sde' utility,
and have no difficulty copying sde.ST_GEOMETRY tables one to another, so the issue
may be something in your environment, or something specifically to do with the
sde. ST_GEOMETRY constructor.  Have you tried using sde.ST_POINT, much like
the SDO_POINT you're using?

You may be introducing inefficiencies by not specifying a coordinate reference (best
practice is to *always* define a non-zero SRID), but a call to Tech Support is the
best way to find out exactly what is happening, and what can be done to make
it better.

- V