http://qs1969.pair.com?node_id=100481

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:

Pro:

    Perl
    • 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.
    SQL
    • 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.
    Nothing
    • Hey, I'm lazy.
    • It gives our programmers a lot more freedom to develop their own interface.

Con:

    Perl
    • 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.
    SQL
    • 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.
    Nothing
    • 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.

Back to Meditations