Slashdot Mirror
MySQL Gets Perl Stored Procedures
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.
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.
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.
Your right to not believe: Americans United for Separation of Church and
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?
" if they put hooks into a generic MySQL facility which allows *any* programming language to serve as a SP language in the server. Why can't I use Python?"
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.
War is necrophilia.