Re: Oracle/VB....can't we all just get along?

A copy of this was sent to "Steve P" <stevep_at_nospamprosoftcorp.com> (if that email address didn't require changing) On 25 May 1999 13:34:09 PDT, you wrote:

>Sorry, a little confusion in semantics. I know that they don't get executed
>all at once, but my main goal is to reduce network traffic (picking up
>faster execution time as a bonus) by only making one request over the
>network to the DB instead of potentially dozens (or even hundreds) of
>separate network round trips. thanks for the advice.
>
>SP

the problem is that you'll find 80% or more of your database execution time will be spent PARSING and OPTIMIZING the query, 20% or less will be spent actually doing it.

If you parse the statement
  update T set x = ? where y = ?

once and execute it a couple of dozen times -- only sending over bind variables each time -- you'll find it (in most cases) runs much faster then building a block with a couple dozen unique statements in it and sending that big block over to be compiled and executed. Your database server will respond better (and use lots less cpu) and you'll actually send *less* data over the network.

Just some food for thought. bind variables are the way to go here.

>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:374ffb6d.112905379_at_newshost.us.oracle.com...
>> A copy of this was sent to "Steve P" <stevep_at_nospamprosoftcorp.com>
>> (if that email address didn't require changing)
>> On 25 May 1999 10:34:57 PDT, you wrote:
>>
>> >Hi,
>> >
>> >Does anyone know how to submit multiple SQL statements to an Oracle
>database
>> >using
>> >SQL created in VB?
>> >
>> >For example in SQL Worksheet for Oracle I can successfully submit the
>> >following statement:
>> >
>> >UPDATE Users Set Name = 'Steve' WHERE User_ID = 1;
>> >UPDATE Users Set Name = 'Frank' WHERE User_ID = 2;
>> >
>> >and execute both at once. But in VB when I try:
>> >
>>
>> no - -they are not executed at once but are executed one after the other.
>>
>> >sSql = "UPDATE Users Set Name = 'Steve' WHERE User_ID = 1; " & vbCrLf &
>> >"UPDATE Users Set Name = 'Frank' WHERE User_ID = 2;"
>> >
>> >I get an Oracle error which says 'invalid character'. I get the same
>> >message when using SQL*Plus. Is there any way around this in VB?
>> >
>> >Please let me know if you know of a solution.
>> >
>> if you want to submit them both at once, try:
>>
>> begin
>> update users set name = 'Steve' where user_id = 1;
>> update users set name = 'Frank' where user_id = 2;
>> end;
>>
>>
>> But if you want *performance* (thats probably what you are trying to get
>by
>> submitting >1 statement in a block -- but it won't go as fast as it could)
>> submit something like:
>>
>> update users set name = ? where user_id = ?
>>
>> and bind the values (Steve,1), ( Frank, 2 ) in and execute the statement 2
>times
>> from the client.
>>
>> >Thanks a lot,
>> >
>> >SP
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
>Oracle8i'...
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Service Industries
>> Reston, VA USA
>>
>> --
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed May 26 1999 - 06:46:03 CDT