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

> 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 and a dynamic execute to every stored procedure call you make to the back end server. 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.

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.

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

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

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 Wed Aug 08 2007 - 20:35:31 CDT