Re: Performance concerns with a single db functions servicing all data requests

On Aug 7, 10:18 pm, ar..._at_hotmail.com wrote:
> I am considering data access solution where every data request from
> the applications invokes a single stored function, and I am wondering
> if may result in resource contention.
>
> The single generic function will accept a CLOB containing the id of
> the calling process and the list of arguments - all recorded as XML
> document. Based on this information the function will figure out what
> stored programs to call, will execute them, and will return all
> requested data in XML format (as CLOB).
>
> The objective of this approach is to move all data access logic from
> the mid-tier to the database, and to simplify mid-tier development by
> having just a single servlet.
>
> Can many simultaneous calls to the same PL/SQL object (disregarding
> any possible data contention) in any way affect performance?
>
> Thanks
> -Art

Hi Art,

I 'm not sure how a single servlet with only one CallableStatement is any simpler than one servlet with say, ten CallableStatements?

I would also be concerned that a single function is a maintenance nightnmare. If you have a PL/SQL package with different programs in it, you can change one program at a time and not break the whole thing (always a possibility).

If you are looking to move data processing logic to the database, you can still do this without using only a single function.

In terms of performance, it seems like pay me now or pay me later. You can overload packaged procedures with parameters if you like, which will probably always be faster than parsing your own arguments and deciding what to call.

I love questions like this, though, as it always leads to good design discussion.

Regards,

Steve

Thanks,

Steve Received on Wed Aug 08 2007 - 14:21:42 CDT