Re: Doubt - Performance
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 23 Sep 2010 08:31:37 +0200
Eriovaldo,
A single call and a single round-trip is obviously less expensive,
in terms of time spent, than three. I don't really understand what you
say about the package being loaded multiple times - if it's called
often, it would stay in the library cache (or you can make it stay there).
But you have several things to consider. Even in a PL/SQL package, one
statement is better than several ones. It all depends on what your
"analyze the return" means, but very often you can write a query that
always returns what you want and doesn't need complicated logic on the
program side.
Suppose for instance you are given an error message :errno, a "preferred
language" :lang, and that you want to retrieve the text for the message
in this preferred language, if not found in a default message (admitting
that some messages may not have been found), and if not found the text
of a ctach-all "message not found" (errno = -1). You can process in a
similar way as what you describe. Or you can write a query that reads
select message
from (select 1 pref,
message
from errors
where msgnum = :errno
and language = :lang
union all
select 2,
message
from errors
where msgnum = :errno
and language = :deflang
union all
select 3,
message
from errors
where msgnum = -1
and language = :lang
order by 1)
where rownum = 1
You'll always get one answer, which is the best (as defined by the
preference order) that is available.
Hope that helps.
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
On 09/23/2010 02:34 AM, Eriovaldo Andrietta 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
>
>
Other related posts:
