Re: Perl vs PL/SQL?

The performance impact of using embedded SQL is substantial in OLTP systems with queries that have a short run time. For longer queries, the performance loss becomes less significant.

Using an interface to SQL*PLUS has several issues:

• Your program will have an unneeded process. A direct library connection using the DBI and DBD for Oracle will consume the minimum number or resources.
• You are restricted by the interfaces limitations of SQL*PLUS. You can do more directly using stored procedures.
• The programming required to interface with SQL*PLUS will be much more substantial than interfacing with the dbi.

Stored procedures, functions, and packages provide several advantages over any other interface. Please see the whitepaper at www.hpdbe.com.

Each language should do what it does well. Using Perl to parse and separate data from columns is more efficient than doing so in Oracle. Both languages are slow when it comes to floating point operations, but Perl is definitely faster. Perl is better for sorting small record sets, for regular expression processing, for interfacing with files, and for interfacing with other processes. PL/SQL is better for all direct interactions with the database.

Good Luck!

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chris Weiss
mailto:chris_at_hpdbe.com
www.hpdbe.com
High Performance Database Engineering
Available for long and short term contracts


"Bill Haverberg" <haverberSPAMFREE_at_visi.com> wrote in message
news:bt5heucsnm6t8cc5frg1vqkm7i67c0eh6b_at_4ax.com...


> I have a perl module I created that interfaces with Oracle via an

> overengineered here file wrapping a sqlplus session which I keep open,

> allowing me to continuously send queries to Oracle and retrieve

> results without having to constantly open and close a sqlplus session

> (I know about PerlDBI but for administrative reasons we can't install

> it). I prefer doing it this way becaue of Perl's rich set of language

> features as compared to PL/SQL.

>

> My question is am I taking a massive performance hit doing this? Is

> PL/SQL inherently going to be faster then the perl approach? I realize

> for triggers and such I'll have to stay with PL/SQL but has anyone

> done a comparison of PL/SQL performance vs other embedded approaches?

>

> Thanks.

>