[mapguide-users] Speed up for loading shp files into oracle

[Zac Spitzer]

Hi Brad

there was no-change between the commit size. i was always using 10,000
rows as the commit interval

the speed up occurs because you offload the decision to oracle on when
to incur the penalty of the commit (ie writing to disk) rather than
making the assumption yourself.

depending on the number of redo logs you have, how big they are and
how fast your disks are and how large the rows are, which in the case
of spatial data can be very varied, the oracle engine is in a much
better position to make the call when to commit to disk.

i find that having 100mb redo's give me better performance than
smaller redo files (ie the 10mb defaults with oracle) when your
dealing with a lot of data. otherwise, oracle doesn't get the chance
to process the redo files before it comes back round to write to the
redo log again... redo1,redo2,redo3,redo4 then redo1 again

PreparedStatement commit =  conn.prepareStatement("commit work write
batch nowait");

and replace any conn.commit() with commit.executeUpdate(); except the
last one of course so you know when it finishes, it's really finished.

Zac
	

On 4/19/07, Brad Nesom <kidsmake6 at msn.com> wrote:
> Zac,
> I'm interested... was the code set to 100 records before the commit? And
> that was the slow down?
> I have read that with problems in large datasets the 1000 record commit can
> help save resources and prevent slow down.
> Hmm.
> Brad
>
> -----Original Message-----
> From: mapguide-users-bounces at lists.osgeo.org
> [mailto:mapguide-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
> Sent: Thursday, April 19, 2007 3:30 AM
> To: mapguide-users at lists.osgeo.org
> Subject: [mapguide-users] Speed up for loading shp files into oracle
>
> I've been loading a lot of shp files into oracle for a mapguide
> project and i found a good way to speed the whole process up by about
> 25%
>
> I'm using the Oracle Java Shapefile Converter sample and the trick was
> to tweak the code to use "COMMIT WORK WRITE BATCH NOWAIT" which
> basically lets oracle decide when to flush your inserts out to disk
> (ie commit)... it's not always recommended as you ain't guaranteed
> your changes have been written disk, but it's only an import right?
>
> i blogged about it here -
> http://zacster.blogspot.com/2007/04/trouble-with-commits.html
>
> basically i got a 25% performance boost which is handy when your
> loading a lot of shape file which can takes hours and hours...
>
> --
> Zac Spitzer
> +61 405 847 168
> _______________________________________________
> mapguide-users mailing list
> mapguide-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapguide-users
>
> _______________________________________________
> mapguide-users mailing list
> mapguide-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapguide-users
>


-- 
Zac Spitzer
+61 405 847 168