Re: ODBC versus OCI: Performance comparison

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 - 16:23:05 CDT