Re: Data Loading Performance Issues

Blv n Drms (blvndrms_at_aol.com) wrote:
: Amit,
: I would suggest going the Pro*C route. It will probably mean more time in
: the coding phase though. The sql*loader actually creates insert
: statements with rollback and commits....then the pl/sql - sql for
: massaging will create additional rollback and commits and finally the move
: to the permanent would create even further rollback..commits. With pro*c
: you can save yourself a lot of processing time due not having the rollback
: being used except on the final insert. Of course if you're rushed it will
: probably be faster to code in sqlloader and pl/sql and debug.

The first set of overhead assumes that you do not use DIRECT PATH. I suspect your Pro*C would have to be pretty hot to beat Direct Path loading.

The second set of overhead assumes that you cannot do the manipulation and transfer in one pass. You should be able to achieve this (even if you have to resort to Pro*C to do it :-) ).

In addition SQL*Loader gives you the option of loading in parallel. Re-inventing this wheel would add complexity to your Pro*C.

I would say SQL*Loader Direct Path is the way to go. Development time has got to be shorter. I reckon execution time should be also (especially if you have and use parallel load).

--
graeme
--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.