Except that in ODBC the bind variables are just string substitutions on the
client side. So it is worthless. You can detect this by looking at the sql
area for the session and retrieving the actual text of the sql that the
server saw. Or run utlbstat.sql and utlestat.sql and notice how after a
long session ( several hours of people doing work and after the database has
been up for awhile) you will see not a lot of hits in the sql area. It
should be above 95%.
Jim
Stephen Tenberg wrote in message <72af65$ilr$1_at_news1.epix.net>...
>I understand your point but disagree.
>
>All of our ODBC code does essentially what you are describing, i.e., we do
>a 'SQLPrepare' to parse an SQL statement like:
>
>insert into authorizations( account , store , register ,"
> "transaction, amount , authorization_sequence ) values (?,?,?,?,?,?)
>
>Then, in subsequent code we do a:
>
>SQLBindParameter(...) which binds parameters to C++ variables.
>
>In other words we use bound host variables under ODBC exactly in the
>manner you describe as with Native API, with the same result.
>
>Only in a couple rare cases (Oracle hints for example) did I find native
>things
>that did not directly map to ODBC, we put these in stored procedures and
>executed
>them from ODBC to solve that problem.
>
>Steve
>
>
>Barbara Kennedy wrote in message ...
>>If you architect your application in certain ways then ODBC is as fast as
>>native. However, if you start using host variables and examine your
>queries
>>you will find native is much faster. Why?
>>
>>For example, let us say your SQL looks like this:
>>select * from emp where empid=5;
>>
>>The database has to parse the query.(figure out what you are doing)
>>The database has to see if you have rights to all the parts you are asking
>>for and operating on.
>>The database has to generate an execution plan. (how is it going to get
the
>>data; what indexes etc.)
>>The database has to describe the result set back to the client. (so
>everyone
>>knows what it coming)
>>The database has to then execute the query.
>>The database has to return the results(row in our example).
>>
>>If you then issue a query:
>>
>>select * from emp where empid=7;
>>The database must do all of the above again. The above is highly CPU
>>intensive. On a system with just one user (often used to "prove" that
ODBC
>>is just as fast as native drivers); most CPU's can execute the above again
>>and again fairly quickly. However, on a system with many users, which is
>>more real life, the above behavior severely limits the scalability of the
>>system which in turn decreases performance.
>>
>>Using a native api properly you would issue the same query like this:
>>select * from emp where empid=:hvempid;
>>(hvempid is a bound host variable that has the value of 5 in this case.)
>>The first time the above is executed the time will be very close to the
>ODBC
>>time to process the query. However, in most application syou keep issueing
>>similar queries over an over again. So to look up emplyee's record number
>7
>>you issue the following statement:
>>select * from emp where empid=:hvempid;
>>(hvempid is a bound host variable that has the value of 7 in this case.)
>>
>>Now the gain comes in. In this case, Oracle skips most of the steps and
>>just executes the query and returns the results. Now if you have a 100
>>people doing this it will even reuse the execution plan since the sql
>string
>>hashes to the same value.
>>
>>Having run load benchmarks looking at this type of thing it does make a
>>signifigant difference how you issue queries via an API.
>>
>>Part of the problem with MS SQL Server is that it cannot do this (no not
in
>>version 7 either). This is a prime reason it does not scale.
>>
>>Jim
>>
>>
>>
>>
>>Stephen Tenberg wrote in message <72a18d$ov3$1_at_news1.epix.net>...
>>>I use the Intersolve ODBC Oracle driver as (as far as I recall) its the
>>only
>>>one I could get working fairly well with Active-X controls and the like
>>that
>>>are looking for scrolling cursors.
>>>Those controls are needed for fast development of screens with
>sophsticated
>>>GUI.
>>>
>>>ODBC, is used correctly, is just as fast as native. Intersolve has
>studies
>>>supporting this at their website. When we use ODBC we implement the
>option
>>>to fetch hundreds of rows with each read, this, more than anything,
>>>dramatically speeds up access.
>>>
>>>For these types of applications, I would recommend a C++ that can
directly
>>>deal with ODBC, such as Visual C++ or Powersoft C++. I am not sure if
the
>>>new Borland C++ finally deals directly with ODBC or you still have to use
>>>their nasty IDE layer which you dont want to do.
>>>
>>>Stored procedures can be executed fine from ODBC or native.
>>>
>>>Steve
>>>
>>>>jerwynn wrote:
>>>>
>>>>> we need to build a system that will query huge bulks of data from
>>Oracle,
>>>>> process these data (conversions, and computations) and dump them to
>>>>> a new database.
>>>>>
>>>>> What languages / tools are best suited for these?
>>>>> Visual C++, Vbasic, Delphi, Borland C++, Java?
>>>>>
>>>>> Is Java or VB too slow?
>>>>>
>>>>> Also, is there a way to invoke stored procedures remotely in Oracle?
>>>>> Which APIs support it, aside from ODBC?
>>>>>
>>>>> Thanks.
>>>>>
>>>>> Jerwynn Lee
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Received on Wed Nov 11 1998 - 22:11:18 CST
