Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

plsql vs. perl vs. neither and API's (code)

by deprecated (Priest)
on Jul 28, 2001 at 03:03 UTC ( #100481=perlmeditation: print w/replies, xml ) Need Help??

Hello, Monks.

I've been working on a project recently here at work that involves some fairly heavy database interaction, and it needs to be available to non technical users. up until now, I had just been using sql in a client application when I wanted statistics on the project. Now these reports need to be made available dynamically to many people via cgi applications.

So I am thinking of building an API, because I forsee the management and "higher-ups" wanting more tools in the future that I might not be called on to create. The question I'm faced with is "do I use stored procedures (i.e., plsql), do I write a perl module, or do I just do what I've been doing, which is hack-as-needed?"

So I've been thinking about this a bit. I can do one of three things:
  1. Stored Procedures
  2. Perl Libraries
  3. Do Nothing
I'll lay out here what I think the pro's and con's here, and tell you where I'm leaning afterwards:


    • It's perl! I like perl, and I'm usually inclined to just sit down and start hacking in it.
    • Easily customizable API: I can use OO or non-OO, and tweak how things perform rather quickly (and globally).
    • We don't really have any SQL hackers other than our DBA's (who aren't in on this project), but we have perl hackers. It seems natural to use perl because of them.
    • Essentially what I am doing is creating SQL macros for programmers so they don't have to. It seems only natural to do it in SQL.
    • It seems less complicated to do it directly in SQL instead of going through a perl module.
    • It is my feeling that using SQL will be faster than building the queries every time i issue one in the module, and also using an object.
    • It's more portable than the perl module. If we move the database, the stored procedures move on their own. Scripts are less likely to break.
    • There is less "time capital" spent on just writing stored procedures.
    • Hey, I'm lazy.
    • It gives our programmers a lot more freedom to develop their own interface.


    • Adding an additional layer of abstraction to any project creates places where problems can arise. This, like much of the code at this organization, could wind up being legacy code, and I'd rather not be responsible for that.
    • Objects tend to be rather slow. Since the number of records in this database will exceed the tens of millions within a couple months, it might be necessary to perform hundreds of operations through the database -- within a CGI application. CGI applications tend to be slow enough. I'm not sure whether this would have a perceptible impact, hardware is not an issue; I can have whatever I want.
    • Perl applications here have a real stigma against them. This is due to the fact that much of the software that has been written here in perl is very buggy. Or at least very fragile. People tend to shudder a little when they hear somebody is developing "yet another perl script." The very word script makes people roll their eyes.
    • Our current policy, because of the attitude towards perl (and also our developers, myself excluded) is to use stored procedures. Developers do not have write access to the database through SQL -- just procedures. This would mean that doing this in SQL would be the "accepted" way to do things. I do, however, have executive freedom in this project, and can decide whatever I feel is "best." Still, going with policy would be nice... to some extent.
    • Doing it with stored procedures means that every time somebody wants a tool, the developer of the moment (and it may be me) has to write a wrapper around the sql procedures. I'm lazy, and the amount of work required down the road is a motivator.
    • I don't like SQL.
    • Well, I was brought on to perform a code audit and make new tools. I don't really trust the developers here to make code that _won't_ become legacy code. So not writing an API means trusting somebody else to write code that doesn't suck. That's a risk I dont really want to take.
    • I'd have to reuse code a lot too, and probably create _new_ code every time I wrote a new tool. I'd rather not.

The reason I bring this up here, and don't just go hack it out in perl is I'm worried I have nailitis. Like, I am a perl zealot and I am choosing to do something in perl that isnt really appropriate to do in perl. I think I've got a pretty good grasp of the situation, but I am looking to the monastery because I know that we have some DB hackers among us. I'm sure somebody here has encountered a situation similar to this and I'm also sure many of you have input on this, and also comments on my pros/cons.

I'd also like to know what people think about the speed of an object and running stuff through DBI rather than having the stored procedures in the database. The database in question is currently PostgreSQL, but we have talked about moving to Oracle. This may matter if significant gains can be had from stored procedures being optimized in Oracle ... I leave the commentary up to you.

Thanks for your time, Monks.
brother dep.

Laziness, Impatience, Hubris, and Generosity.

  • Comment on plsql vs. perl vs. neither and API's (code)

Replies are listed 'Best First'.
Re: plsql vs. perl vs. neither and API's (code)
by runrig (Abbot) on Jul 28, 2001 at 23:21 UTC
    Actually PL/SQL and perl can complement each other...

    With a stored procedure, you can abstract your API more efficienty in some situations. For instance, you could create a stored procedure called 'add_customer' or 'ship_order', supply it with all the necessary arguments, and have the stored procedure take care of validating the arguments and inserting and updating all the proper tables (assuming that there's more than one table involved in such a transaction, of course you can develop a perl module to do your 'add_customer' subroutines, etc). And since stored procedures are executed on the database server, there is a lot less network traffic or traffic between the app and the database server. With a two-tiered approach, there's a potential to more easily develop the app on other platforms, where (gasp) perl might not be available, or you just need a different interface.

    Downsides: stored procedures are usually not portable between database vendors; you're now developing one app in more than one language, so you need both skillsets available.

Re: plsql vs. perl vs. neither and API's (code)
by Stegalex (Chaplain) on Jul 28, 2001 at 07:15 UTC
    I LOVE PERL! However, if you value your job, you will step a bit further from the problem and try to find a good tool that will meet the needs of top management. There are several good OLAP tools used on data warehousing projects that can greatly simplify reporting if only you can develop datafeeds to their "cube" data model. Yes, it costs some money, but if there is a huge need for complex reports done quickly, OLAP is a good option. Hacking it out yourself is both time consuming and counterproductive (assuming you don't go to the trouble of building Perl modules and stored procedures). The maintenance of lots of hacked stuff gets overwhelming very quickly. Assuming you can't buy any tools, I suggest that at very least, you design Perl modules to control the interface to small groups of related tables. Your cgi code will be greatly simplified and your design process will be smoother. What works for me is to first design the tables, then design the interface .pm's and then to build the code that uses the .pm's. That's just my opinion and I am very opinionated. So there.
Re: plsql vs. perl vs. neither and API's (code)
by aquacade (Scribe) on Jul 28, 2001 at 07:39 UTC

    Don't be ashamed to be PERL PROUD brother dep! I think people trust you more when you stick to what YOU are truly passionate about.

    Depending upon the Oracle version you might buy (hopefully 8i v8.1.7 or later I suggest) it comes with the Oracle Express Engine (I'm told, it's a kind of "OLAP cube engine" for their OFA (Oracle Financial Analyzer) tool, which you still have to buy separately (of course, that's Oracle for ya!). Also Oracle 8i Discoverer has tools to create management reports.

    PL/SQL scripts or stored procedures are like going back to "dBASE for DOS" to me. They work but aren't very elegant or interesting.

    I think you can confidently put your Monk's hood down and hold your head up high! Your management can more easily hire another Perl programmer than find an OLAP expert for whatever flavor be it Cognos, BusinessObjects, Hyperion, or AlphaBlox. Unfortunately for us Perl programmers, probably cheaper too!

    I agree with another Monk who commented (sorry no reference) that nothing is faster than MySQL for very large databases. The DBI connection to MySQL isn't perfect, but damn near and better than any other (my opinion, no slight intended to other module authors). It doesn't have views, but you can "simulate" them with temporary tables cause it's so damn fast.

      Using DBI you have access to full SQL dialect of your database engine, and flexibility of perl. Don't do object if they will slow processing down, you do not have to. I suggest then to take plain old shared libraries to access database.
      Try solve 80% cases of data access in your shared code, and for 20% high performance use custom SQL. I guess Knuth said that root of all software evil is optimalization too soon -- so don't do that if you are strong enough to resist...
      I know it's hard not to optimize - because it is more fun, I am struggling with it too... ;)
      About MySQL: there are many threads all over Net that MySQL can be faster because does not have support for transaction. From your description I am guessing you need trasaction? For this many rows, ORACLE will be safer bet, IMHO.
      I am working on such an API right now, but sorry it is not ready yet...:(

      To make errors is human. But to make million errors per second, you need a computer.

      MySQL is very long as you don't need the features left out to make it fast, and as long as the amount of data you are dealing with at once does not exceed RAM limits. See DBI + 'SELECT *' - Memory Use Galore? for more on the latter issue and, of course, Why not MySQL for more on the former.

      Use the best tool for the job. MySQL is a great tool for some purposes. But it has limits and if you are going to advocate it, you should know what those limits currently are.

        Thankfully, I've never had this problem because all my Windows workstation have at least 384MB of RAM and my tables in MySQL are all under 3M rows thus far. Please keep making comments and connections like this tilly for us all! The thread you mention is VERY valuable to me. I haven't had time (as yet) to search and read the entire corpora of Perl Monks knowledge. I'm still sloshing through current stuff trying to be more helpful than harmful. Thankfully Monks like you are there to balance my experiences with your Wisdom!

        ..:::::: aquacade ::::::..

Re: plsql vs. perl vs. neither and API's (code)
by wintrowski (Novice) on Jul 29, 2001 at 05:18 UTC
    At work, I am currently working on a project that is very very similar to that which you have described (although I am working with a much smaller database and probably much fewer users). I have two or three databases that I have to interface with, plus an internal e-mail system, a future employee hours accounting system, and God knows what else.

    Because my project is not intended to be high-end where performance is going to be a major issue, I opted to hack things out in Perl, using CGI, DBI, and PostgreSQL.

    The advantages, I have found, to this approach are as follows.

    • I can hack things up pretty quickly
    • I can isolate frequently used subroutines into libraries, making for easier maintenance
    • Should we decided to move over to a different database, my code will not require a major overhaul to keep working
    • If management want new features, they can be added in pretty quickly
    • HTML templates allow you to isolate your middle-ware code from your front-end. This makes updating the front-end a relatively painless task

    I will admit that the Perl/DBI/CGI approach is not entirely perfect.

    • CGI is noticeably slow unless you use mod_perl (that is if you are also using Apache)
    • DBI is quirky and can be confusing
    • It's easy to write shit code

    In your case, I honestly think that if you went the Perl/CGI/DBI route, in combination with mod_perl, you would have a pretty kick-ass system. It will be versatile, and can accommodate *any* changes or modifications that management want to throw at it. There is nothing that it can't do.

    Although I would say to you that if you are working with a huge database which will have a lot of processing done on it by many users, you may need to consider porting your system over to C at some point, depending on whether or not you notice serious performance deficiencies. I have never seen any bench marks to compare C-based CGI applications and mod_perl/CGI applications, so I am only speculating with regards to this performance issue.

    You may also want to look into PHP. I have never used it so I can't say as to how well suited it is to this task.

    If you want to discuss the Perl//CGI/DBI route further, mail me at and I can further relate to you my experiences.


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://100481]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (2)
As of 2023-06-08 21:12 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (35 votes). Check out past polls.