| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance concerns with a single db functions servicing all data requests
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 =
cst.registerOutParameter (1, OracleTypes.VARCHAR);
cst.execute();
System.out.println(cst.getString(1));
}
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 Received on Thu Aug 09 2007 - 11:16:39 CDT
 |
 |