Re: ODBC versus OCI: Performance comparison

OCI and ODBC with decent ODBC drivers are quite the same as long as array/bulk interface is used properly. You may want to check out a couple of things:

        http://home.sprynet.com/~skuchin/otlodbc.htm -- Odbc Template Library (C++)

        http://home.sprynet.com/~skuchin/otl_1pg.htm -- Oci Template Library (C++)

Both OTL's provide the same SQL Stream APIs with Oracle or ODBC data sources. Both are thin and performant layers of C++ template code on top of ODBC and OCI.

New, unified version will be available soon.

Sergei

Barbara Kennedy wrote:
>
> You might see a dramatic improvement. One dependency is how efficiently you
> write the OCI code and take advantages of things like host variables.
> Taking advantage of host variables and optimizing the code can make a large
> difference on the CPU usage on the server. However, if there is not much
> else going on on the server it might not appear to be much faster (the
> largest factor at that point being the disk writes which are probably pretty
> equal for the same set of statements whether it is ODBC or OCI). What I
> mean is that with ODBC on one machine with one set of tests with only 1
> person logged on you might see average CPU of 70% usage. The same test with
> OCI you might see 10% CPU usage. The wall times for the two tests might be
> about the same, but the scalability of the application under those two
> scenarios are very different. I did make up the two CPU usages numbers as
> illustrations, however, in working on a variety of projects I have seen
> areas where OCI can make a magnitude of difference between ODBC and OCI. I
> have also seen OCI code that is not optimized and run no faster than ODBC or
> at least did not scale well. Read the Oracle Application Developer's Guide
> for clues.
>
> 1. Use host variables - so you do not have to keep recreating cursors and
> the server can reuse the execution plans.(Traditionally, people open a
> cursor in some subroutine and then close it at the end. One could have a
> cursor manager that kept track of open cursors and did not require the
> rebuilding of them again and again - just reuse it.)
> 2. Use hints to speed up your queries.
> 3. Don't keep asking for the data over and over again if you do not have to.
> (I know sounds silly who would do that! You would be surprised! - often the
> developer is surprised also.)
>
> Jim
>
> <arivlin_at_my-deja.com> wrote in message news:7s61h9$v0l$1_at_nnrp1.deja.com...
> > Does anyone know what difference is in performance between OCI and ODBC?
> > I need to write an application that will select up to 10000 rows from
> > table foo ( c1 varchar ( 100 ) Not null,
> > c2 varchar2 ( 100 ) null,
> > c3 date,
> > c4 date,
> > c5 number,
> > c6 number )
> >
> > There could be more columns in select,numbers, nullable and not
> > nullable.
> > I did it with ODBC, and selects and decodes do take time. What speed
> > improvement can I expect with Oracle's native library?
> >
> > Also, the application needs to insert 1000 rows ( a varchar2 ( 200),
> > b date, c int ).
> >
> > What kind of improvement I can expect there?>
> >
> > Thank you very much in advance.
> > Alex
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
Received on Mon Sep 20 1999 - 17:13:53 CDT