PL/SQL Tip of the Month - Slow Performance in Dynamic SQL when Binding Variables

From: <info_at_revealnet.com>
Date: Mon, 10 Aug 1998 14:36:02 GMT
Message-ID: <6qn0gh$btd$1@nnrp1.dejanews.com>

A recent thread on the PL/SQL Pipeline generated plenty of discussion, 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 Received on Mon Aug 10 1998 - 09:36:02 CDT