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

On Aug 8, 9:35 pm, Mark Townsend <markbtowns..._at_sbcglobal.net> wrote:
> > 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?)
>
> So at a minimum you have added a table lookup

Rather I _replaced_ the logic programmed or hard coded in the mid-tire with a table lookup.

> and a dynamic execute to
> every stored procedure call you make to the back end server.

Yes. Why would that be a problem?

> Plus in an
> earlier email you seemed to indicate that this call would be done by a
> construct such as SELECT fn_generic(p_xml_params) FROM dual
> So each SP call is in fact a SQL round trip consist of a prepare,
> execute and result return result. So potentially you could be doing a
> lot more on the network.

The generic program doesn't have to be function; it can be coded as a procedure.

> I predict that you will also need to add in insert into a table for each
> call so that you can actually track what got called by whom when.

Logging requirements should be independent of the proposed solution. The only difference is that now you can only log in the database - which is probably not a bad thing.

> And I have no idea how you are going to manage privileges and invokers
> rights (or indeed even if it is an issue)

I haven't thought of that but I don't see why the privs should work any differently, invokers or definers.

> But this is only the call. What unpacks the result (set or otherwise)
> from the call, or is no data being returned ?

I will be using Oracle XML DB features to query the incoming XML documents and to return XML result sets. The result set will be unpacked in the application.

> What's the actual business problem you are trying to solve ? Sounds to
> me like you are trying to roll your own SOA/BPEL type engine ? I think
> that approach just about makes sense if you are having to integrate a
> bunch of services together across different legacy systems. But if you
> can write the code and have x call y explicitly I think it's just
> better - simpler, and something the system was tuned to do.
Received on Thu Aug 09 2007 - 12:56:00 CDT