Forgot your password?
typodupeerror
Perl Programming

MySQL Gets Perl Stored Procedures 266

Posted by CmdrTaco
from the well-isn't-that-special dept.
ryarger writes "Woo Hoo! After a seeming eternity of wait, there is finally an implementation of stored procedures for MySQL. It uses Perl as the stored proc language, too!" Also note that this piece of work was done by OSDNs own Krow. Very cool work I must say.
This discussion has been archived. No new comments can be posted.

MySQL Gets Perl Stored Procedures

Comments Filter:
  • by Hagabard (461385) on Friday August 24, 2001 @12:57PM (#2213941)
    Why didn't they use a Transact-SQL compatible stored proc syntax? This would ease migrating and also enable people who prototype DBs on MySQL to move it to either Sybase or MS-SQL with a minimal of fuss.

    I'm not saying Transact-SQL is great or anything but it'd be nice if it was a bit more compatible with other systems.

  • What I find extremely funny about this all is that Microsoft is doing the same thing in SQL Server 9.0, by putting the CLR in the SQL Server database. This way you can write your procedures for SQL Server in many languages, including Perl. OUCH. It causes more overhead than you realize to have an interpreter for more than SQL in the database.

    I'm a DBA. I have seen the last few versions of Oracle with their Java Stored Procedure and SQLJ support, which is pretty bad. Oracle can't even get their PL/SQL running right between queries and views and stored procedures (the engine has not changed for PL/SQL since 7.3 in 8i, and 9i does not change it that much. Yes, they run 2 engines, one for SQL and one for PL/SQL. It makes Oracle perf tuning a complete nightmare). Yet they find it necessary to shoehorn a complete JVM in. No, no one really uses it, because it doesn't provide advantages.

    It only makes the code completely unmaintainable since it's nothing more than code that calls the internal JDBC driver rather than an external one.

    PL/SQL, T-SQL, and the other stored procedure languages at least are written in a superset of the main DML/DDL language. This allows you to use the same language optimizer, which reduces code size, and allows for code consistency across the entire project. In other words, all the queries, including parts of stored procedures, get the same optimization treatment.

    Having ONE optimizer means that you can make it run really well, and share query plans and cached information. Pretty cool :).

    The other important reason you have stored procedures is because if they are written in the main language, you can leverage the optimizer for query plans and caching of frequently-used or prototyped statements. That's part of the other reason for stored procedures. You can share queries and query prototypes with views and user queries, and have optmization that is better than what writing a procedure in X language can do.

    Now we've got Microsoft coming in with their CLR, and mySQL using Perl. This is going to lead to even more unmaintainable code, because you're going to have people coding business logic that can be optimized in the DDL/DML language used in a higher-level language that cannot be.

    Talk about a performance problem :).

    From a language and optimization perspective, you always try and use a derivative of the main DML/DDL language of the database, so that you can use the same optimizer for making the statements run faster and perform well.

    Anyone can write internal hooks to have a code interpretation engine in a SQL database. Oracle's been doing it for years, and so has Sybase. No one I know uses it because it doesn't provide the real advantages of stored subprograms in a database, which is to store frequently-used and prototyped query statements and aggregations in such a way so that they can be optimally retrieved versus just executed. When you add additional languages, you lose that. Oracle's Java Stored Procedures are nothing more than Java code that calls a different JDBC driver. I don't even want to think of what ADO.NET is going to do in SQL Server 9.

    While this seems like a good idea, remember that it's been out for a few years in two other products, and is coming out for another. It's not as big a deal as real SQL stored procedures, because it's not as optimal as they are due to their loose coupling (which describes it perfectly IMHO), and can't share in the same optimization techniques as user SQL queries.

    In other words, this isn't something to be too happy about, since it's something that people already have and don't use.
  • Re:Subselects? (Score:2, Interesting)

    by dorkstar (318427) on Friday August 24, 2001 @01:38PM (#2214218)
    Well, this probably won't help convince your boss, but IIRC subselects are mathematically unnecessary. You can flatten any query down to a single select and what you get is much more efficient. Read the real scoop in the first chapter of any database textbook.

    Actually, IIRC it's not even that hard to do it for 90% of queries...
  • Re:But wait... (Score:3, Interesting)

    by don_carnage (145494) on Friday August 24, 2001 @03:01PM (#2214768) Homepage
    Yes, but it's a nightmare trying to perform an extended update without subselects. I usually end up just writing a small Perl proggy to do it with DBI.
  • Re:But wait... (Score:3, Interesting)

    by bwt (68845) on Friday August 24, 2001 @03:26PM (#2214929) Homepage
    You know, most of the time someone using a subselect just lacks the imagination needed to do a join :)

    While some people will always do brain dead things, there are definitely many queries that you simply cannot write unless you do subqueries.

    Consider something as simple as finding all students whose IQ is above average.
  • Re:But wait... (Score:3, Interesting)

    by krow (129804) <brian@t[ ]ent.org ['ang' in gap]> on Friday August 24, 2001 @03:35PM (#2214984) Homepage Journal
    Oh very true. Sometimes you can't get around using subselects (there are a couple of places in Slashdot that are exactly in this situation).

    There are other things I would rather see though. For instance in Oracle you can represent Graph structures in the databases (quite cool). I would love to have this for comment storage. Right now it is quite a pain in the ass to generate the comment pages in threaded/nested mode.
  • Actually the code for the DB is very modularized. You just have to exchange one library and you can use a different databases. I have tinkered with this for PostgreSQL and Oracle but never for any other DB (while I have experience with Sybase and Informix, I have never used DB2).

    I have had people from IBM approach me about doing a DB2 port, but no one has every offered any code and I have enough to do as is.

    One of these days though I would love to setup slash on an IBM mainframe and actually benchmark it. I suspect it would run quite well (even with MySQL).
  • PERL????

    Jesus, PERL????

    You know, the strength of query languages is that you don't have to use (and in face, are usually punished for using) loops and cursors to make massive changes. Perl is the most loop oriented language on earth. And even if, underneath it all, the optimizer is turning your code into a loop anyway, it's goddamn doing it more efficiently than Perl ever would. This addition is NOT going to increase the likelihood of people migrating from sybase or other TSQL based databases to MySql...it's going to increase the number of hardliners who feel that MySQL is a pathetic ghost of "real" servers, and as such decrease the cadence of better open source solutions like PostGreSQL. MySQL and Perl...it's fast becoming a database for control freaks who don't believe in doing anything automatically, or allowing the machine to do our optimizations for us -- and that's what computers are all about, goddamnit!

    It is nice that there's finally a way to perform object operations on a server without performing the logic in scripted code, and it's nice that MySQL is trying to make a grab for usefulness beyond its INSERT, SELECT, DELETE simplicity. But Perl is not a standard language in the DB world...it's asking for DBAs and programmers used to TSQL and looking for a cheaper, freer alternative to gain new custom knowledge that is complex and no better then the knowledge they already have! All those linux sysadmins to have a little database are going to be overjoyed...but for the rest of us, this is totally useless, just like the rest of MySQL's features.
  • by c13v3rm0nk3y (189767) on Friday August 24, 2001 @04:49PM (#2215339) Homepage

    Boy, are you going to get flamed.

    I've had a love-hate thing with Perl since I first saw it. I appreciate it's power, but scratch my head over some of the design choices. And the syntax! Inscrutable.

    I do marvel at it's versatility, however. Perl. Is there anything it can't do? That being said, it's just too big for most of the work I do. If I need to hack a script together, I just reach for the Kornshell.

    I've used Perl to prototype, and at that it excels, but the maintenance required for anything else hasn't given me the warm fuzzies. You have to be a bit of a wizard to showcase the elegance buried in Perl, and I just can't take the time. I'm sponsored by my company to increase my Java and C kung-fu, and <code>use Perl;</code> is just not part of our culture.

    Anyway, to actually finish on topic, the db-powered app my company uses had ruled out mySQL, even on Linux, for lack of store procedures. The introduction of Perl is not going to change this significantly. Compared with Informix, mySQL rules, but it's not corporate-ready, at least for this corp.

  • by ajs (35943) <ajs&ajs,com> on Friday August 24, 2001 @05:21PM (#2215470) Homepage Journal
    Wow, can you imagine doing
    • UPDATE 'foo', map { ... } grep { ... } sort SELECT 'bar', ...
    This is the heart of the power of Perl, and if the interface is built right, it could be a huge boon to database work.

    Of course, done wrong it could be slow, difficult to maintain and immediately obsolete.

A LISP programmer knows the value of everything, but the cost of nothing. -- Alan Perlis

Working...