Re: Re: Database APIs and Languages/Tools

1. ODBC drivers for major databases like Oracle, Sybase, SQL Server and DB2 have bulk/array operations so performance in not an issue. I tried both the native OCI and Oracle ODBC drivers, ODBC 2.5 and higher and the actual difference in performance was about 3-5% on reasonable buffer sizes. The main complain about ODBC being slow is that it is used in the scalar mode. Try the OCI with the buffer size == 1 and see how fast it is.
2. Newgroups usually are used for DISCUSSION, not for FREE ADVERTISING SPACE of COMMERCIAL PRODUCTS, Mr. President of Sylvain Faust Intl. To me, it's okay to post an announcement of a Freeware product, not commercial stuff 'cause the Freeware product is given away for FREE, no money in return. Why don't you, Mr. President, post an announcement of SFI's Free products to this newsgroup?

Speaking of ODBC as a standard of middleware database APIs versus native database APIs, like the Oracle Call Interface, I have something to offer to the people for FREE and of a better quality than a lot of commerial database access libraries.

ODBC Template Library (OTL):         

        http://home.sprynet.com/sprynet/skuchin/otlodbc.htm

OCI Template Library (OTL) for OCI7 and OCI8:

        http://home.sprynet.com/sprynet/skuchin/otl_1pg.htm

Performance itself is not that much different from product to product because it's driven by the network bandwith and the database server. What makes a big difference nowadays is that how much time it takes to develop an application. Eventually it boils down to how much source code the devloper has to write and maintain...

Sergei

Sylvain Faust wrote:
>
> Anyone tried using OCI directly instead? Just in case you want to do
> benchmark, our OCX for OCI can be download for free and will work (with
> some nag screens, but enough for testing) for this.
>
> In any case, let me know if you need it, I can manage to get you one if
> you need it. We also use our OCX for OCI for direct OCI programming
> from ASP pages as well from IIS3/4. SQL-Sombrero/OCX for OCI Example
> ASP page at:
> http://www.sfi-software.com/sombrero/sqloracle.asp
>
> Works with any OCX compatible environments. It takes care of Arrays,
> etc...
>
> For CD or to download the software (it has samples as well)
> http://www.sfi-software.com
>
> Let me know,
> Thanks
>
> Sylvain Faust
> President
> Sylvain Faust Intl (SFI)
> SFI Building
> 880 boul. de la Carrière, Suite 130
> Hull, Québec, Canada J8Y 6T5
>
> SQL-Programmer 2.VIII:
> "The best SQL integrated development environment we've seen, earning
> PC Week Labs' Analyst's Choice honors." [Oct. 26, 1998]
> http://www.zdnet.com/pcweek/stories/news/0,4153,363685,00.html
>
> Tel: (819) 778-5045, Ext. 804
> Toll Free: 1-800-567-9127 ( U.S. and Canada only)
> Fax: (819) 778-7943 International; (617) 722-9389 U.S. only
> Email: sfaust_at_sfi-software.com
>
> Sylvain Faust Inc. (SFI)
> A Decade of Delivering SQL Solutions, 1988-1998
> http://www.sfi-software.com
>
> To receive news bulletins from SFI automatically, just send a blank
> email to:
> SFI-NEWS-SUBSCRIBE-REQUEST_at_peach.ease.lsoft.com
>
> > -----Original Message-----
> > From: Barbara Kennedy [SMTP:barbken_at_teleport.com]
> > Posted At: Wednesday, November 11, 1998 11:11 PM
> > Posted To: server
> > Conversation: Database APIs and Languages/Tools
> > Subject: Re: Database APIs and Languages/Tools
> >
> > 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 Sat Nov 14 1998 - 01:41:53 CST