Re: Any projects with lots of server-side PL/SQL?

From: Marty Himmelstein <marty.himmelstein_at_valley.net>
Date: 1995/09/20
Message-ID: <43nrmt$39f_at_dartvax.dartmouth.edu>#1/1


I am using server side PL/SQL for a data warehousing project. I am a database consultant doing work for a software company that specializes in health care utilization analysis. The company has many large insurance companies as customers.

Previously I used Sybase's Server side stored procedure language Transact/SQL.

Primarily for the reasons you stated, server side development is very compelling: it hides the data model from the front end tools, allows a higher level of data abstraction and encapsulation, enhances sharing, and makes changes easier: database changes can be made with affecting client software. I am generally happy with PL/SQL; it is a rich language, as far as database manipulation goes and compared to Transact/SQL, but it does have some limitations (e.g., inability to return result sets. My understanding is Oracle is fixing this particular problem.)

If PL/SQL isn't up to a specific task, I would recommend exploring the feasibility of writing server side C code (or whatever) instead of client-side code. Let the front-end people do the GUI stuff. Tools like Powerbuilder and Visual Basic can be used to create tentacle like hooks into the database, which is bad.

I've been satisfied with PL/SQL's performance. One benchmark I tried inserted rows into a table as 1) insert ... select ... and 2) open a cursor. Loop: fetch, insert. The cursor version took about twice as long as insert via select (1.5 million rows). If you have to insert from one source into many destinations, the relative advantages will change. I didn't have time to do more extensive testing of the overhead of different PL/SQL constructs, although it would have been fun.

Hope this helps.

Marty Himmelstein
marty.himmelstein_at_valley.net Received on Wed Sep 20 1995 - 00:00:00 CEST

Original text of this message