Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Help on Development Philosophy

    Being a young Oracle shop, we are definitely lacking in experience, so I was hoping to enlist your help yet again.

    A proposal was recently brought up, where a Developer was asking if it was better to define custom functions in the database that could be used in SQL statements, or if it was better to write the functionality into the SQL statement itself.

    My objective answer would be to use the approach which is more efficient. If I was developing, though, I think that I would go for the custom function first, evaluate efficiency more in terms of the length of time it took to get the results back, and if it took too long, try to embed the functionality into the SQL statement. (After a brief look at what they're trying to accomplish, it looks like much of it could be accomplished in inline views if embedded into the query.)

    How would you approach these kinds of issues?
    Thanks again,
    -cf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Using custom functions in SQL is nice in theory, but not always so good in practice because they can perform badly. It depends what they do.

    What is not a good idea (but is often favoured by programmers) is to use functions to avoid joins, e.g. this:
    Code:
    select emp.name, get_dname(emp.deptno), get_jname(emp.job)
    from emp;
    instead of this:
    Code:
    select emp.name, dept.dname, job.desc
    from emp, dept, job
    where emp.deptno = dept.deptno
    and emp.job = job.job;
    Why is not not a good idea? Because Oracle cannot optimize the query with functions: it is forced to access the dept and job records one at a time, in a procedural fashion.

    For this kind of thing, creating a view is a much better option.

    Deterministic functions are another matter: these are functions that do not access the database but just manipulate their argument values - like SUBSTR, UPPER, TO_CHAR, SQRT. If you have a frequent need to perform a complex calculation on values from the database, then a function might be the way to do it:
    Code:
    select empno, cuberoot(sal) from emp;
    The important difference is that this function will not access the database, so does not impact the query optimization.

    However, calling any user-defined function from SQL has some performance penalty, because of the "context switch" from SQL to PL/SQL that takes place for each row. So care is still needed! Example:
    Code:
    SQL> create or replace function double(i in integer) return integer
      2  is
      3  begin
      4    return 2*i;
      5  end;
      6  /
    
    Function created.
    
    SQL> set timing on
    SQL> select sum(object_id*2) from all_objects;
    
    SUM(OBJECT_ID*2)
    ----------------
          1.9612E+10
    
     real: 17093
    SQL> select sum(double(object_id)) from all_objects;
    
    SUM(DOUBLE(OBJECT_ID))
    ----------------------
                1.9612E+10
    
     real: 20077
    That's 20 seconds instead of 17 processing a single function call over 47,000 records. Not terrible, but if you have multiple function calls it can add up.

  3. #3
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Functions may be fine for use in your application. But, if they get put into a view, and that view gets used in reporting, then you can watch the SQL of that function rise to the top of your statspack report. I have this situation with our programmers. I've rewritten their view to eliminate the use of the function, but the ole 'Not invented here' philosophy exists.

    Are you using production statistics on your development machine?
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    "Are you using production statistics on your development machine?"

    The answer would be no, since I'm not sure what this is. Like I said, we're a young shop learning as we go. Would you mind clarifying this statement?

    Thanks,
    Chuck

  5. #5
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106

    Smile

    Most development is done without the magnitude of production data, so your code performs differently between test and production. You can simulate the statistics that you have in production, without having the actual data (i.e. generate plans that are similar, if not identical.)

    Assuming you have access to Metalink look at article 117203.1 :

    How to Use DBMS_STATS to Move Statistics to a Different Database: ================================================== =============== You want to copy database statistics from one database to another database. For example, you want to test certain operations on a scaled-down copy of your production database and you need the statistics from the production database. This article shows you how to use the DBMS_STATS package to copy statistics from one database to another database.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

Posting Permissions

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