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

On Aug 8, 3:21 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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

Without going into any details, I hope many would agree in principle that keeping data access logic in the database is overall a desirable objective. In that case, the proposed solution does not have to be simpler or faster. As long as it is not worse in these and other categories, it should be worth doing.

I am now trying to figure out what the potential disadvantages of the proposed solution are. I asked about performance but I am glad people are brining up other concerns as well.

As far as the concern about "maintenance nightmare", I think the opposite is true.
The generic function is envisioned to be very thin. For every call it will do the same simple thing. It will query the table containing app_call_id / program_to_execute pairs to retrieve the name of the corresponding program and to execute it. (These calls will have to be assembled dynamically. Can that be a problem?)

In a simple case that would be the end of it - the program_to_execute will return all requested data.
In a more complex scenario the program_to_execute will consider calling fn_get_data1, fn_get_data2, and fn_get_data3. It may decide to execute any one, any two, or all three. This can get quite complex (just as it would if coded in the mid-tire) but these complexities will be coded in app_cal-specific functions rather than the single generic function.
I think conceptually this is similar to many servlets, each having multiple callable statements, except that now the logic that determines what database programs to call is coded in the database.

Thoughts?

Thanks
-Art Received on Wed Aug 08 2007 - 19:02:10 CDT