Insert statements in Oracle

1
votes

I'm having an SQL script as below that consists of 65000 insert statements.

SET DEFINE OFF;
SET AUTOCOMMIT 1000;
SET FEEDBACK OFF;

Insert into table (col1,..col20) values ('val1',...'val2');
Insert into table (col1,..col20) values ('val1',...'val2');
...
...

When I run this script through sqlplus through my command prompt(sqlplus usr/pwd@DB @script.sql) it took for me around 4 hours to complete.

But when I run the same script by logging to putty connecting the Server and run the script through sqlplus (sqlplus usr/pwd@DB @script.sql) it took only one minute to complete.

Could you please let me know why this difference is caused

1
sqloracleinsert-into
Sounds like network latency - the separate statements will all have their own individual round trips to the server, which is inefficient. But the difference sounds a bit extreme. Where is your client located - on the LAN? WAN? Accessing over the internet via VPN? How long does the remote version take if you just make it a block, putting begin ... end; around the entire list of inserts? (Also, why are you doing 65,000 individual inserts in the first place - where are those values coming from?) - Alex Poole
If I put a begin.. end enclosing the insert statements then it runs(from my command prompt) for 4 hours. We are connecting the server through internet(both through my command prompt and putty). I wanted to insert these values to a table from one higher env to development env. I knew there are other ways like SQL LOADER to load it fast, but I would like to understand the difference between the above 2 executions. - Vinoth Karthick
I'm not really sure about the reason for the time difference. One thing that I can think of is, when you acces the database through putty, you login to the db server. This reduces network latency (as Alex mentioned). - Invalidsearch
You can also try a single insert. It will be faster than separate inserts. with data_set (col1, col2)as (select 'val1', val2 from dual union all select 'val1', val2 from dual union all select 'val1', val2 from dual) insert into table select * from data_set; - Invalidsearch

1 Answers

0
votes

You can also try a single insert. It will be faster than separate inserts. 

with data_set (col1, col2)
as (select 'val1', val2 from dual union all 
select 'val1', val2 from dual union all 
select 'val1', val2 from dual) 
insert into table select * from data_set;