Re: Mass data insert --- HELP
Date: Mon, 11 May 1998 14:30:09 GMT
Message-ID: <355807d8.6719371_at_192.86.155.100>
A copy of this was sent to "Troy Weyant" <troy.weyant_at_stoner.com> (if that email address didn't require changing) On Mon, 11 May 1998 09:16:32 -0400, you wrote:
>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 time could easily be larger then actual time to execute the statement. For example, I created a simple table x ( x int ); and in sqlplus inserted a couple hundred rows, one insert at a time. The sql trace shows me:
using statements like insert into x values ( 1 );
insert into x values ( 2 );
call count cpu elapsed disk query current rows
------- ----- ------- --------- --------- --------- --------- ----------
Parse 204 0.38 0.39 0 0 0 0
Execute 205 0.09 0.12 0 100 104 202
Fetch 2 0.00 0.00 0 0 0 8
------- ----- ------- --------- --------- --------- --------- ----------
total 411 0.47 0.51 0 100 104 210
So, the parse time was over 4 times the execute time (we spent 4/5's of our total execution time doing parsing of the inserts, not actual work)
So, lets use bind variables and see what happens:
INSERT INTO X
VALUES
( :b1 )
call count cpu elapsed disk query current rows ------ ------ ------- --------- --------- --------- --------- ---------
Parse 1 0.00 0.00 0 0 0 0 Execute 200 0.07 0.06 0 1 204 200 Fetch 0 0.00 0.00 0 0 0 0------ ------ ------- --------- --------- --------- --------- ---------
Big difference already, the parse time (since we parsed once) is not even measurable anymore....
Lets add array inserts:
C code was:
int t[200]; int i;
for( i = 0; i < 200; i++ ) t[i] = i;
EXEC SQL INSERT INTO X VALUES (:t);
EXEC SQL COMMIT WORK;
insert into X
values
(:b0)
call count cpu elapsed disk query current rows ------- ----- ------- --------- --------- --------- --------- ---------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 2 10 200 Fetch 0 0.00 0.00 0 0 0 0 ------- ----- ------- --------- --------- --------- --------- --------- total 2 0.01 0.01 0 2 10 200
[Quoted] 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
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon May 11 1998 - 16:30:09 CEST