Spatial Insert Performance

From: Peter Sylvester <peters_no_spam_please_at_mitre.org>
Date: Fri, 09 Jul 2004 12:37:25 -0400
Message-ID: <ccmhju$psj$1@newslocal.mitre.org>

I'm running 9.2.0.3EE on W2K, and ran some simple performance tests.

With a simple non-spatial table (id, lat, lon, all numbers), I can get inserts up around 12,000 records per second (using batch insert mode of Oracle's JDBC driver).

I setup a similar table for use with spatial:

CREATE TABLE test2 (
id number not null,
location MDSYS.SDO_GEOMETRY not null,
constraint pk_test2 primary key (id)
);

When there is no spatial index, I can get about 10,000 inserts per second, slightly slower than the non-spatial table.

After adding a spatial index, insert performance drops to 135 inserts/second. Thats about 2 orders of magnitude different.

Here is the index setup (RTREE Geodetic):

INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'test2',
'location',

MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, 10),
MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 10)

),
8307 -- SRID for 'Lon/Lat WGS84 coordinate system );
commit;

CREATE INDEX test2_spatial_idx
ON test2(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('LAYER_GTYPE=POINT'); I'm just inserting simple points, the SQL being used is below:

insert into test2 (id, location)
values (testseq.nextval,
MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(?,?,NULL),null,null));

(this is from the PreparedStatement definition).

Any pointers/suggestions are appreciated! I know that dropping the index is a workaround, but I have an app that will expect to be doing inserts fairly continuously.

thanks,

--Peter Received on Fri Jul 09 2004 - 11:37:25 CDT