PL/SQL Tip of the Month - Slow Dynamic SQL Performance when Binding Variables
in...@revealnet.com
resulting in August's PL/SQL Tip of the Month. Visit the PL/SQL Pipeline for
lively technical discussions, free utilities, white papers and more. The
PL/SQL Pipeline is hosted by author Steven Feuerstein and sponsored by
RevealNet. http://www.revealnet.com/pipeline.htm
----------------------------------------------------------------
August's Tip of the Month
Has Dynamic Binding Got You Down?
A recent thread on Pipetalk in the PL/SQL Pipeline addressed the problem of
slow performance in dynamic SQL when binding variables. The developer needed
to execute the same INSERT thousands of times, but with different values. His
DBMS_SQL parse statement looked like this:
DECLARE
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rows_inserted INTEGER;
BEGIN
DBMS_SQL.PARSE(cur,
'INSERT INTO SomeTable VALUES (:Bind_1,...:Bind_N)',
DBMS_SQL.NATIVE
);
FOR rowind IN 1 .. 1000
LOOP
DBMS_SQL.BIND_VARIABLE(cur, 'bind_1', val1);
...
DBMS_SQL.BIND_VARIABLE(cur, 'bind_N', valN);
rows_inserted := DBMS_SQL.EXECUTE (cur);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;
Each of the :bindN references requires a separate call to
DBMS_SQL.BIND_VARIABLE and the performance was inadequate.
Solomon Yakobson suggests the following "workaround": instead of using bind
variables, reference PL/SQL package global variables, and execute a dynamic
PL/SQL statement instead of a dynamic SQL statement.
In other words, create a package specification that contains a single
variable for each bind variable:
CREATE OR REPLACE PACKAGE myvars
IS
bind1 INTEGER;
bind2 DATE;
...
bindN VARCHAR2(30);
END;
and then execute the dynamic SQL as follows:
DECLARE
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rows_inserted INTEGER;
BEGIN
DBMS_SQL.PARSE(
cur,
'BEGIN '||
' INSERT INTO SomeTable VALUES ' ||
' (myvars.Bind1,...myvars.BindN);' ||
'END;',
DBMS_SQL.NATIVE
);
FOR rowind IN 1 .. 1000
LOOP
myvars.bind1 := val1;
...
myvars.bindN := valN;
rows_inserted := DBMS_SQL.EXECUTE (cur);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;
You might then see a significant improvement in performance of your dynamic
SQL!
Thanks to Solomon Yakobson once again for his insights and contributions on
the Pipeline!
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
