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.

Re:Not a DB guru

[msheppard]

SP's let you delgate a lot of the processing that should be done on the database (performance/encapsulation being good reasons to do this) to the database. And your biz layer doesn't have to do it and deal with the interface overhead. Things like complex sorting, or reference other tables.

Given that ideal: Most stored procedures are just very complex select statements anyway.

Re:Better stored proc languages...

[bzhou]

Why is the suggestion of T-SQL funny? It's just an alternate, and IMHO a better-than-perl alternate. It's more compatible with core SQL and more compatible with the calling layer. I don't think people using Python or JDBC will be too fond of the myperl solution. T-SQL started from Sybase, not everything MS using is bad. But kudo and congrats to the myperl author anyway, at least people start to feel the need of stored procedures.

I would also like ...

[Da VinMan]

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? Why can't I use xxx? It's widely rumored that Microsoft is doing this same thing for the next version of SQL Server, so this really isn't such a radical idea. The trick is to devise an abstraction within MySQL that represents all stored procedure capabilities, and then interface each target language to that layer.

I agree that having a Transact-SQL equivalent will be key to consideration by serious database users, but it's just a starting point.

Re:I would also like ...

[ethereal]

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...

[bwoodring]

> Why in the HELL would you want another layer in there? For flexibility?

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.

> You need performance, period.

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.

> If they actually wanted MySQL to be used by people who knew what they were doing, they would've integrated in PL/SQL.

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?

One other reason

[Dalroth]

A lot of you people are forgetting two other critical reasons why stored procedures are good.

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).

Re:I would also like ...

[Dasein]

A vast majority of the performance gains to be had from stored procedure programming comes from two sources -- precompilation and elimination of network round trips.

Neither of these options are precluded by a correct abstract interface.

There's no doubt that such an abstract interface would hurt performance, but I would venture to say that you would give back much less than 1% of the stored procedure benefits by doing this. You can do a large number of JNI-Like calls in the ~8ms required to complete a network round trip. Add compilation and query optimization and you have a large number Vs. a very small number.

To be able to provide a migration path for both sers of MS SQL Server/Sybase, Oracle, and DB/2 seems compelling even though and such migration path is likely to be an 80/20 proposition.

Not a bad idea, in my view.

Re:Why not stored procs in SQL?

[Anonymous Coward]

Because SQL isn't a programming language and lacks even the basic conditional and loop control structures.