Slashdot Log In
MySQL Gets Perl Stored Procedures
Posted by
CmdrTaco
on Fri Aug 24, 2001 11:52 AM
from the well-isn't-that-special dept.
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
|
Log In/Create an Account
| Top
| 266 comments
| Search Discussion
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
Better stored proc languages... (Score:3, Interesting)
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.
Re:I would also like ... (Score:4, Insightful)
I wouldn't bitch about performance too much - if that's all you want, just ditch SQL entirely and use an embedded DB like Berkeley. Truth is, features/performance is a sliding scale, not a binary option. For some applications, being able to use stored procedures in a few different languages might be very helpful. For other applications, an SQL parser itself is unacceptable overhead.
Cram your Mod points... (Score:4, Insightful)
You don't know the first damn thing about database programming, do you? The stored procedure code isn't re-parsed every time it's run. The execution plan for the query is cached and *that* is run. The performance hit would only be seen the first time the SP was run, when the recompile occurs. Having multiple SP languages would be a very good thing.
No, not really, it is that kind of attitude that got MySQL into the position it's in today, everyone acknowledges it's fast, but nobody has any respect for it as a real database.
No no no, damnit. We need to get past these shitty Procedural SQL hacks. T/SQL and PL/SQL are crap, Why do you think Oracle is integrating Java and Microsoft is integrating ActiveX into the database engine? Because trying to do high-level programming in SQL is complete shit. Why would MySQL want to integrate a legacy language like PL/SQL?
Re:I would also like ... (Score:4, Informative)
Postgresql does this. You can use python, perl, TCL, and PL/PGSQL.
They are debating loading up Java but there seems to be some resistance from the hackers.
That's great. (Score:1, Offtopic)
Now if we could only get high school students [bbspot.com] to understand Perl and MySQL we'd be all set.
Rock on! (Score:1)
Java (Score:1)
TEH SEANS@!!@!@
Not a DB guru (Score:2)
Unenlightened
Call me a troll.... (Score:1)
Hmm...Not the only problem is.. (Score:1)
Subselects? (Score:3, Funny)
He liked MySQL until he heard that it couldn't do two things: stored procedures and subselects. He said "I don't see how it could be useful without those things." All of the database apps he's ever written use those.
It's great to see stored procedures being implemented. It would be even better if/when subselects are implemented. I could make a stronger case for moving some things over.
Any chance of it happening?
But wait... (Score:2)
This would have been great, fifteen years ago. (Score:2, Troll)
Who knows, maybe MySQL will one day be considered a real database product.Until then, though, those of use doing Real Work will continue to use Oracle, DB2, and SQL Server. Of course, these databases already have professional GUI development tools, spatial data modeling, XML table translation, and tons of other fun toys, so the MySQL developers better get to work!
Honestly, besides cheapo webhosts and poorly designed weblogs [slashdot.org], who uses MySQL?
DB2 rocks on GNU/Linux, by the way, and it's free as in beer. You should check it out.
I do hope this is the beginning and not the end... (Score:2)
Lee
Is it offical (Score:1)
Yes I also know that I can/should recompile from source but most people will simply install the binaries.
Hmmm....perl....haven't we learned from Oracle? (Score:4, Interesting)
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.
Cool! (Score:1)
Sheesh, could they have made a more braindead move?
Why not stored procs in SQL? (Score:1)
Or is being server-agnostic a Bad Thing now?
Mysql todo list (Score:3)
Looks like it might be a while. Better just get PostgreSQL [postgresql.org] in the meantime.
and why? (Score:1)
Correct me if I am wrong, but perl is interpreted language. So what is the point? It will take exactly the same time to execute the thing as I would have the proc outside of the database...
Just my 2C (canadian)
the truth is near... (Score:1)
sorry perl, you had your day, but you are now outclassed.
makefile (Score:1)
i love brian's comment in the "install" file tho. yea...you just need to re-configure perl, and if it's done wrong it will core on you...
From stories past... Slashdot funds MySQL? (Score:1)
I'm curious, Taco, what happened with this? It was before VA Itsux bought Bendover, so maybe the funding got nixed then? Or did they spend all of the money on designing the new logo? [mysql.com]
The crowd may not like this, but it's true (Score:2)
The syntax is a mess, and like many basic-esque languages it's very easy to get into namespace trouble. There are gotchas with strings and escape sequences. Memory is managed with a reference counting garbage collector, which means circular dependencies will create memory leaks; this isn't as serious with kludge maintenance and CGI scripts, but on a database it will be of signal concern. What passes for a language API is what I would call deliberately obscure (lots of one and two letter functions, a million operators, &c &c). On the whole, it's a complete horror show, and just as with Win32, I'm continually amazed at how many things get written against it.
I say stop the cycle of abuse. There are over a dozen free languages that would have been a 100% better choice.
One other reason (Score:2, Insightful)
1. Most database pre-parse the stored procedures and keep the cached parsed information in memory. Really complex SQL queries can take a significant amount of time to parse, and cutting down on that overhead can be a huge win for some applications (it was for one of our queries!).
2. Stored procedures can encapsulte logic that requires multiple SQL queries into one call. This saves the network overhead of making multiple trips to the database, which can potentially be huge (and even be REALLY huge if you open up a new connection for each SQL query and then shut the connection immediately).
I don't know if the Perl procedures remain parsed, but at the very least they should be able to accomplish #2. Personally though, I'm going to wait till mySQL supports some sort of Transact SQL like stored procedures. I don't see a justifiable reason for the overhead involved in running Perl on my database. That just strikes me as a bad idea (same goes for java).
There are very few cases for stored procedures (Score:1)
Can I add another box and double my query or
insert capability? No.
But, I can add another app server and theoretically double my ability to execute code. People who design enterprise applications to use stored procedures are giving up their ability to scale. The exception to this rule is when stored procedures perform aggregations, and time series analysis. However, I will go so far as to say that using a relational database to perform ad-hoc analysis is a stupid thing to do and a billion dollar industry has grown up around solving this problem in a better way (OLAP).
ALL enterprise applications I've seen that use stored procedures were failures because they could not scale. They had to be rewritten and redesigned from scratch.
As other posters have said, this is not much to be happy about.
PostgreSQL (Score:1)
Oracle Perl Stored Procedures? (Score:2)
Well, one can dream, can't they? :)
A crust of bread to a hungry populace... (Score:3, Interesting)
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.
Schwartzian Transforms take on a new level! (Score:3, Interesting)
- 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.
subselects so hard? (Score:1)
SQL sucks (Score:2)
SQL engines are slow and unreliable. Almost everytime I see a web site down (even Freshmeat) it's due to a database crash. SQL is a brain damaged query language. SQL tables have an horrible obsolete Cobol-like structure (every record must have a fixed len to be handled efficiently, types are fixed, etc) .
Sure, they can be useful for something.
But for 99% of the projects they are used for, they could be easily replaced with a simple indexing library like CDB, GDBM or BerkeleyDB (BDB itself is very powerful, it has a lot of nifty features, plus it's rock solid and damn fast) .
Or even flat files. I've seen so many people using complex SQL tables just to store 50 poor records. Just crazy. Do people know that filesystems can store data, too ? Does Squid need Oracle to store the cache ?
I never used SQL (although I coded large search engines and other stuff that stores and index a lot of data) . And I don't want to. BerkeleyDB achieve the same thing on a 386 than *SQL on a Thunderbird.
Umm... Doesn't this defeat the purpose? (Score:1)
PostgreSQL has it all ... (Score:1)
- PL/pgSQL (this is the counterpart to ORACLE PL/SQL)
- PL/Tcl (TCL Procedural Language)
- PL/Perl (yes, this is server-side perl implementation!)
Here the pointer to the corresponding manual entry [postgresql.org]
How can we comment? (Score:1)
How can we comment on how good or bad this is when (per the development web site) there is no benchmark as to how expensive this functionality is? If it makes queries execute at a tenth of the "normal" speed and also adds 15 megs onto each mysql instance, then that would certainly factor significantly into the "usefulness" equation.
maru
www.mp3.com/pixal
Language X possible through Perl Inline Module ? (Score:2)
Ought to work.. anybody tried using it [cpan.org]?
Welcome, lame reader... (Score:1)
<bart
Re:Lame (Score:1)
BTW as a SQL SERVER dba, I usually reach for my pistol when anyone asks me to use ASP( another buggy MS crap) on it.
Actually (Score:2)
Re:Perl Yuck! (Score:1)
Now, I know I'm responding to an AC post, which is admittedly not terribly bright since it's obviously a troll anyhow, but I feel I need to pop my 2 cents in here...
Back at my old "full time" job, we used MS SQL databases in production at a Windows NT-based NOC. Incidentally, this was for the BOA telephone banking system itself (yep, I was a coder on that team, it's mostly written in VB6). Anyhow, I used mySQL for personal purposes, and eventually got around to (of course) reproducing some of the system logic in C/Perl and mySQL. Guess what? On limited stress tests, my system held up fine. Of course, I wasn't about to try to evangelize my job away, but the point remains valid that it could have worked in production.
Fast forward to the present, where I run a small web hosting and web application design company (see sig fo mo). All our clients use mySQL and Perl for their solutions, most of which are quite robust and flexible. Not to mention fast; we run a pure Linux shop for that stuff and haven't had any competition anxiety with Windows shops.
The progess with things like stored procedures in mySQL and such may have been a bit slow, but it's getting there. Hey, last I checked
Re:Why MySQL ? (Score:1)
postgreSQL is much more feature rich but not perfect, and the documentation is seriously not in sync with latest release (forcing you to dig thru mailling list archives to find how to use this or this feature)