Re: Calling function from select statement is slower then using subselect in select

Gints Plivna wrote:
> Tomeo wrote:
> > Hi, I have SQL query, that I'm trying to tune up.
> >
> > If I simplify it, I can say: When I run my select statement, where I'm
> > calling function
> > (that is just returning ABS(SUM) value) in main query, then it takes
> > much more longer, than if I replace function with subselect(subquery).
> > Why this happens? I'm expecting almost the same execution time. Please
> > see query, execution time, explain plan and statistic below.
>
> User defined functions are one of the main killers of performance, they
> can probably make a more elegant and flexible solution sometimes but
> performance almost always are worse because there must be context
> switch from sql between pl/sql engines. If you are doing this switch
> for thousands of rows it adds up and takes a very observable time.
> So use user defined functions very cautiously and when you really need
> them.
> More info
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:60122715103602
>

One of the most bothersome things in Oracle (or probably in every other database).
Implementing "good" application design by reusing code, structuring stuff with packages and procedures, etc. will hit you on the head with the performance cudgel. >:-(

When will they give me templates for my packaged SQL?!? :)

cheers,
Martin Received on Wed Oct 25 2006 - 14:11:24 CDT