Re: Database APIs and Languages/Tools

Hi,

What about new Oracle 8 features? What ODBC driver supports them?

Andrew Protasov

> 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 - 01:50:52 CST