Re: Database APIs and Languages/Tools

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 Tue Nov 10 1998 - 16:04:16 CST