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

On Aug 9, 12:16 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> On Aug 8, 8:02 pm, ar..._at_hotmail.com wrote:
>
>
>
>
>
> > 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
>
> Hi Art,
>
> You know, this kind of stuff always interests me, so I'm glad you
> posted this thread. I always want to make sure I understand why
> something is good or bad, rather than just parroting what I have
> always "known" (of which I am guilty, I am afraid).
>
> It was such an unusual design you posted, I thought I would build a
> test case, just to make sure I understand what you are asking.
>
> All I did was create two generic procedures (program1 and program2)
> that are called and return a different value from each other. The
> decision as to when each is called is based on input to a function
> (big_honking_function)
>
> create or replace procedure program1(p_int in out number) is
> begin
> p_int := p_int * 100;
> end;
> /
>
> create or replace procedure program2(p_int in out number) is
> begin
> p_int := p_int * 2;
> end;
> /
>
> create or replace function big_honking_function (p_xml in xmltype)
> return clob as
> l_num number := 2;
> l_clob1 varchar2(100) := p_xml.extract('/begin_xml/
> foo2').getStringVal();
> l_clob2 varchar2(100) := '<foo2>number 2</foo2>' ||chr(10);
> begin
> if trim(l_clob1) = l_clob2 then
> program1(l_num);
> else
> program2(l_num);
> end if;
> return 'value = ' || l_num;
> end;
> /
>
> I then created a very simple standalone java application that calls
> the function, with hardcoded values (I didn't spend *that* much time
> on it)...
>
> import oracle.jdbc.OracleTypes;
> import java.sql.*;
> import oracle.jdbc.OracleCallableStatement;
>
> public class testBigHonkingFunction {
> public static void main(String args[]) {
> try {
> Class.forName("oracle.jdbc.driver.OracleDriver");
> Connection conn =
> DriverManager.getConnection("jdbc:oracle:thin:@**********:****/*****",
> "******","*****");
> OracleCallableStatement cst =
> (OracleCallableStatement)conn.prepareCall("{? = call
> big_honking_function(xmltype('<begin_xml><foo2>number 2</foo2></
> begin_xml>'))}");
> cst.registerOutParameter (1, OracleTypes.VARCHAR);
> cst.execute();
> System.out.println(cst.getString(1));
> }
> catch(Exception e) {
> e.printStackTrace();
> }
> }
>
> }
>
> Other than the fact that I hardcoded the call logic, is that what you
> are referencing?
>
> If so, I still think your calls to extract on the XMLType (I assume
> that is how you would parse the arguments in the database) would use a
> *lot* more CPU, as we have seen this when using extract on xmltypes.
>
> Regards,
>
> Steve

Thanks for your input Steve.

Instead of manually constructing XML tags I will be using built-in Oracle functions like dbms_xmlgen.getxml. I suppose there must be some cost to envoking this function, I don't know how large. Another important point is that this solution is intended only for the applications that choose to use XML regardless. That would exclude the apps that request very large result sets.

-Art Received on Thu Aug 09 2007 - 13:19:10 CDT