Re: Mass data insert --- HELP

> >I need to issue many (>30,000) insert statements into multiple tables within
> >an application, but performance is lousy. I believe this is due to the
> >overhead of parsing of each statement, but I'm not certain of this.
> >
> >Just for a performance comparison, I've loaded 20000 records into a table in
> >8 seconds using sql-loader. The same data using insert statements in
> >sql-plus took 9 minutes. The sql-loader conventional load still uses sql
> >statements, so there has to be a way of achieving similer performance.
> >
> >If anyone has any insight here, I'd be greatly appreciative. If anyone knows
> >of code that works similer to sqlloader, this would be just as useful.
> >
> >Thanks
> >
>
> Its called bind variables and Array inserts (both of which sqlldr does but
> sqlplus does not).... As an aside -- why do you want something that works
> similar to sqlldr -- whats wrong with sqlldr?
>
> Anyway, sqlldr will parse a statement like:
>
> insert into mytable values ( :1, :2, :3, ..., :n );
>
> only once and then execute it over and over again. For an insert, the PARSE

SNIP........
> Now, the time to execute the insert it barely measurable. This is what sqlldr
> does -- it uses array inserts and bind variables (and hence is lots faster then
> sqlplus parsing each and every single row insert).
>
> Hope this helps explain the differences....
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA

 Tom,
  Will the same hold true writing an app in Pro*C ? We are implementing a system which will 'add' 1.5 million rows a month (into a 90million max row table). Your explaination has me wondering now how long the sucker could run.

thnx
bobh
>
Received on Tue May 12 1998 - 03:47:32 CEST