| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle/VB....can't we all just get along?
Thomas Kyte wrote:
>
> 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
Thoroughly agreed...although in my experience, one of the reasons people have stayed away from binding variables is that ODBC drivers are lousy at doing this...
Thus you end up with a nice choice of pass the SQL through and get slower parse times versus lousy ODBC bind times but minimal parse ....
(Of course a proc would address this...)
Cheers
--
"Hardware,n. The bits that can be kicked" Received on Thu May 27 1999 - 10:01:14 CDT
 |
 |