TimesTen : Performance Issue With Execute Immediate Vs. Embedded Sql
edited Jan 10, 2014 7:05AM in Gateways, Oracle Lite, Oracle DB Mob. Server, TimesTen (MOSC) 4 commentsAnswered ✓
Hello,
.
I am testing a PL/SQL loop doing inserts and it seems that embedded sql is a lot faster
than using execute immediate, sample code below. Is this expected behavior?
.
Execute Immediate:
.
declare
type t_row_collection is table of t1%rowtype index by binary_integer;
t_rows t_row_collection;
sql_str varchar(50);
begin
select * bulk collect into t_rows from t1;
for j in t_rows.first .. t_rows.last loop
sql_str := 'insert into t2 values (' || t_rows(j).f1 || ', ''' || t_rows(j).f2 || ''');';
execute immediate sql_str;
commit;
end loop;
I am testing a PL/SQL loop doing inserts and it seems that embedded sql is a lot faster
than using execute immediate, sample code below. Is this expected behavior?
.
Execute Immediate:
.
declare
type t_row_collection is table of t1%rowtype index by binary_integer;
t_rows t_row_collection;
sql_str varchar(50);
begin
select * bulk collect into t_rows from t1;
for j in t_rows.first .. t_rows.last loop
sql_str := 'insert into t2 values (' || t_rows(j).f1 || ', ''' || t_rows(j).f2 || ''');';
execute immediate sql_str;
commit;
end loop;
0