Efficiency of Procedures/Functions Over In-line Code ?

If I encapsulate a common SQL task in a Function, I find that it is MUCH less efficient than performing the same with inline code; is this normal behavior? Is there a way to make it more efficient? Is that a trade-off for modularity?

For example, if I want to trim and change the case of a column, I might use:

  UPDATE MYTABLE SET COL1 = RTRIM(LTRIM(LOWER(COL1))) And it finishes (e.g., 6500 rows) in < 20 secs.

If I use instead the following Function:

  FUNCTION TEST ( INDATA CHAR )
  RETURN CHAR IS
  BEGIN
    RETURN LTRIM(RTRIM(LOWER(INDATA)));
  END TEST; Then issue:

  UPDATE MYTABLE SET COL1 = TEST(COL1) It takes approximately 3-4 minutes, which is quite a change in performance.

Is there something wrong in the definition of my Function, or its use? Perhaps my configuration? I am using Oracle 7.3.3 .

Thanks much for any help,   

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Wed Dec 30 1998 - 00:00:00 CST