How to speed up creation of points in Oracle geodatabase?
I have a procedure that inserts a bunch of point features into an Oracle feature class using a function like this:
INSERT INTO THETABLE (JOB_NUMBER, MUNICIPALITY, THE_LATITUDE,THE_LONGITUDE,SHAPE)
SELECT B.JOB_NUMBER,
A.MUNICIPALITY,
A.LATITUDE,
A.LONGITUDE,
sde.st_geometry(THE_LONGITUDE, THE_LATITUDE,null,null,4326)
FROM TABLE1 A, TABLE2 B
WHERE A.BATCH_ID = 'SOME NUMBER'
---------------------------------------------------
It's working but running slower than expected, it runs at about 30,000 records per minute.
I could use a lot better performance than this if possible; I need a process to run every 15 minutes or so that needs to handle hundreds of thousands of records every time, if not more.
It says Here in Esri's documentation that this is the optimal way to create points:
If anyone knows of something I may be missing that's slowing down the insert process I'd really appreciate it. I have database folks and developers I can work with to make changes if we have a direction to go in, but as it is this is quite new to all of us.
We have tried running the same Insert process without creating the geometry in the SHAPE column, and it's way faster, just a few seconds to run for ~ 30,000 records. So it's the geometry part that seems to be the culprit.
Thanks in advance.
