Re: What's the best alternative to this pl/sql ?

>>>>> "Karsten" == Karsten Farrell <kfarrell_at_belgariad.com> writes:

 Karsten> no-spam_at_no-spam.com said...
>> "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
>> news:b3kvp1$d51$1_at_ctb-nnrp2.saix.net...
>> > Gabriel Gonzalez wrote:
>> >
>> > > Otherwise, save yourself some time and create the SQL on the
>> client. Make > > sure you use bind variables/parameters and
>> prepared statements so that at > > least the parsed queries remain
>> in the cache to speed up performance of > > repeat SQL statements.
>> It should give you a speed boost anyway.
>> >
>> > Sorry, I don't understand this. It sounds like bollocks to me.
>> >
>> > How can you say that shifting the code from PL/SQL to something
>> like C++ or > Delphi or Perl or whatever, will boost speed?
>>
>> As compared to not using prepared statements, it will... Maybe I
>> dod not make that clear enough. Comparison was uprepared vs
>> prepared client queries, not client vs server queries.
>>
>>

 Karsten> You are correct if you mean using Java's PreparedStatement
 Karsten> as opposed to its Statement ... because a PreparedStatement
 Karsten> gives you bind variables.  However, in the web world, you
 Karsten> can't always use PreparedStatements because (at least in
 Karsten> some cases) you have to maintain a connection.  Sometimes
 Karsten> connecting for each PreparedStatement takes longer than the
 Karsten> hard parse required by a Statement. You have to test in your
 Karsten> particular environment to see which is more efficient. Do
 Karsten> you want to spend your time in the middle tier or the
 Karsten> backend tier?  -- /Karsten DBA > retired > DBA

I've found that while prepared statements are faster than straight statements in java, perl et. al., at best, they only perform as fast as stored pl/sql procedures - often not as fast due to network latency and connection overhead. Moving code from the server to the client is unlikely to improve performance, even with prepared statements. You are far better off trying to optimize your pl/sql stored procedures and possibly look at tuning your database parameters.

Tim

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!