Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Unanswered: Performance Question

    There are two alternatives to bind variables;

    Code:
    V_ID INTEGER;
    First way:

    Code:
    BEGIN
    SELECT MY_VALUE INTO V_ID FROM CUSTOMERS WHERE CUST_ID=1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
    V_ID:=0;
    END;
    Second way:

    Code:
    CURSOR CUR IS SELECT MY_VALUE FROM CUSTOMERS WHERE CUST_ID=1;
    OPEN CUR;
    FETCH CUR INTO V_ID;
    IF CUR%NOTFOUND THEN
         V_ID := 9999;
    END IF;
    CLOSE CUR;
    do you know which one provides best performance and why?
    Last edited by tenkyu; 12-26-06 at 04:29.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is from my experience, not documentation:

    Queries run much faster if written in pure SQL then PL/SQL. Sometimes, when there's a query which, for example, INSERTS customer's data into a table, it fails for TOO-MANY-ROWS. I don't know who is to blame, so I write a cursor and iterate through all customers; in exception hanlder, I either print out this/those "guilty" customer(s) or put it/them into an "error log" table and check them later (10g allows you to log errors in SQL, so there's no need for PL/SQL). Such a way - cursor for loop - takes more time to finish (even when everything is fine, an noone raises an error) than the same query written in SQL.

    Now, both of your examples are PL/SQL, but I guess that first one *should* be faster just because my previous "cursor story" (which doesn't have to be true at all).

    However: your first example is likely to fail on TOO-MANY-ROWS error, while the second one would finish correctly.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I take it like this: the less code you use, well.. less time the <whatever here> has to spend executing it. I put <whatever here> because it can be the engine, it can be the OS (object code here), or the VM, etc. In Oracle, just write code when you need to.. really.

  4. #4
    Join Date
    Dec 2006
    Posts
    3

    Performance gain=?

    But memory allocation is different between this ways. The first way occurs an exception "NO_DATA_FOUND" it gets some load to oracle machine. And the second way needs an extra memory to put the data into cursor but dont occurs an exeption.

    There are some advantages and disadvantages of this ways, sometimes i can't decide which is the efficient for performance gain.

    If any one has an experience please give an example for solving this dilemma.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Do you have sample test data? If not, you should have one.

    When you have it (not Scott's schema - it is too small for benchmarking purposes), use both approaches and see for yourself.

  6. #6
    Join Date
    Dec 2006
    Posts
    3
    Hi guys, i made a test with our customer table which includes over 1.500.000 tuples. My test result is "first way" is more efficient than the "second way".
    If your sql doesn't bring any tuples (no data found) the results are the same, because oracle machine decides the same way.

    no_data_found exception = CURSOR%NOTFOUND exception

    But if your sql brings a result the first way is more efficient, because cursor requires extra memory to put tuples in second way.

    I asked the same question to my teacher who is a oracle guru(Prof. Dr. Birol Aygun, he worked 20 year in IBM) he said to me "if you get an exception from oracle; the is no performance gain because the exeption execution time is independent, but if you don't get an exception; performance gain is related to your database desing(Indexes, Table spaces, etc.)"

    As a result the first way is suitable to bind variables in Oracle database.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm glad you found the solution.

    However, *cough*, I'd rather say that prof. dr. Aygun is a DB2 guru (unless he spent 20 years closely looking at their competitor, Oracle)

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    what about #3?
    PHP Code:
    for i in (SELECT MY_VALUE FROM CUSTOMERS WHERE CUST_ID=1)
    loop

      insert 
    (xvalues (i.my_value);

    end loop
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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