Re: Doubt - Performance

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 23 Sep 2010 08:53:26 +0100
Message-ID: <AANLkTika_A4_YmTmrVc-KN=VSMhhpzb4BHw1Qkp8WVUU_at_mail.gmail.com>

I'd prefer to put calls 1 and 2 in the package as well most likely (you don't actually tell us what the requests are - quite likely a single call will do - take a look at yesterday's PLSQL Challenge for a classic example) not for performance reasons especially but because packages are specifically useful for keeping business logic together. An application that keeps part of the business logic on the app server and part in the database *for the same transaction* seems to me to be badly designed. What happens if you want to add a 4th check or to change the order of the logic? I'm not saying where the best place to keep business logic and data access code is (though you might guess my preference) but in 2 places in the same time definitely isn't it.

From a performance point of view doing less work (the package solution with 1 dbcall and less parsing) is going to outperform doing more work (the other solution with up to 3 dbcalls, more parsing albeit soft hopefully) . But I'd rather have a clear and maintainable design above a performance driven design at the initial design stage. Fortunately clear and maintainable designs have a tendency to be rather fast and unclear and convoluted ones tend to be rather slow with unexpected bottlenecks.

On Thu, Sep 23, 2010 at 1:34 AM, Eriovaldo Andrietta <ecandrietta_at_gmail.com>wrote:

> Digite um texto ou endereço de um site ou traduza um documento.<http://translate.google.com.br/?tr=f&hl=pt-BR>
> Cancelar <http://translate.google.com.br/?tr=t&hl=pt-BR>
> Ouvir
> Hello Friends,
>
> I have an application that runs on Oracle database with Asp.net.
> A given situation need to do a select on the database and analyze the
> return.
>
> If return = Ok , then the request is solved.
> If NOT Ok, have to do another and different select in the database and
> analyze the return.
> if second return = OK, then the request is solved.
> if second return NOT OK, the application must call for a package in the
> database and this will definitely resolve the request.
>
> Which of the two following solutions will have better performance to serve
> 50 users simultaneously.
> The Asp.net server is installed on a SERVER1 and the Oracle database is
> installed on SERVER2.
>
>
> 1.) Call the first and the second sql statment via Asp.net and if the
> return is not OK, then call the package in order to solve the request,
>
> or
>
> 2.) Call the package once and it will perform the first tentative, second
> tentative and then if necessary run routine final, solving the request.
>
> I have in my mind that we need to consider the cost of net traffic when
> using the solution 1.
> and in the solution 2, that the package will be loaded every time and all
> sql statment will be parsed.
>
> Any idea about the best way to implement ?
>
> Best Regards
> Eriovaldo
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 23 2010 - 02:53:26 CDT