Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    26

    Unanswered: Passing record to a variable - Performance Issue

    I need to optimize a module(Oracle Forms 6i), then I found so many statement that goes like this:
    Code:
    DECLARE
       v_param   VARCHAR2 (10);
    BEGIN
       FOR rec IN (SELECT col_name
                     FROM table_name)
       LOOP
          v_param := rec.col_name;
          EXIT;
       END LOOP;
    END;
    I was planning on changing/ simplifying it to something like this:
    Code:
    DECLARE
       v_param   VARCHAR2 (10);
    BEGIN
       SELECT col_name
         INTO v_param
         FROM table_name;
    END;
    Is there any difference? Or I'll just leave it as is? Thanks!
    Last edited by Rektanocrit; 06-15-12 at 05:51.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The second one will fail if the select returns more than one row.

    And please use [code] tags to format your SQL code.
    Details are in the online help: http://www.dbforums.com/misc.php?do=bbcode

  3. #3
    Join Date
    Feb 2012
    Posts
    26
    Thanks shammat! But will the second one perform faster?

    Sorry I forgot to use tags, it will not happen again.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Rektanocrit View Post
    Thanks shammat! But will the second one perform faster?
    The second one does something different and will behave different. If two pieces of code are not doing the same thing there is no way of telling which ones "faster".

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I would go with your second option, but include a stopkey to ensure it doesn't fail if it returns more than one row. That would make it logically equivalent to the other statement.
    Code:
    DECLARE
       v_param   VARCHAR2 (10);
    BEGIN
       SELECT col_name
         INTO v_param
         FROM table_name
         WHERE rownum <= 1; -- this is the stopkey
    END;
    From a performance perspective, I wouldn't expect much of a difference, unless the original query actually selects back lots of data only to then report the first result and ignore the rest. The revised query will ensure that Oracle only goes for a single row, resulting in less IO.
    Last edited by dayneo; 06-15-12 at 09:01.

  6. #6
    Join Date
    Feb 2012
    Posts
    26
    Thanks dayneo!

    I couldn't agree more on what you said, I'll try that. Thanks everyone.

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @Rektanocrit
    Remember, this is only an answer based on the information you have given us. In my opinion, this implementation is actually a latent bug.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •