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:
- Stored Procedures
- Perl Libraries
- 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. |
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.
| [reply] |
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. | [reply] |
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.
| [reply] |
|
|
| [reply] |
|
|
| [reply] |
|
|
| [reply] |
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 wintrowski@yahoo.com and I can further relate to you my experiences.
Wintrowski | [reply] |
|
|
