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.
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.
Now if we could only get high school students to understand Perl and MySQL we'd be all set.
Bring on the GUI apps. Guess now I've got yet another reason to learn Perl
We dance to all the wrong songs.
--Refused.
But what about the java? The java support on Oracle is pretty damn nice, and damn it, I don't think it's "crazy" to expect the same kind Oracle-quality from mysql...
TEH SEANS@!!@!@
One of the many things I hate. thingsihate.org
Could someone enlighten me as to the usefulness of stored procedures? Are they significantly faster? Are they easier to use than the straight jdbc/dbi api?
Unenlightened
Gnumeric should follow MySQL's lead, and
dump GB (vb clone) and just use Perl for
functions.
...but you have to wonder about a product that is made *easier* by adding *Perl*.
324006
My experience has been that someone programming an app doesn't have to learn all the inticacies of Oracle, etc, to get work done. The dba writes procerdures and other people just call that procedure instead of writing huge ugle SQL queries.
Best Slashdot Co
plus, you guys are horrible coders too.
Perl stored procs - as if Oracle or Sybase stored procedure languages were not convoluted enough! Bravo!
whether to write application logic as Perl in MySQL, or as SQL in Perl..
Why is it that many people who claim to support standards have such atrocious spelling and grammar?
What kind of a moron would use perl, which awards stupidity and promotes bad coding, and has to GUESS whether what you gave it is a regex or not?
Oracle's Java support is terrible. Why would you want to slow your server down to a crawl?
Gee, Perl stored procedures... that will certainly make MySQL enterprise-ready... oh, except for that locking business it can't seem to get around, and the speed, and the instability, and the bugginess (but that's typical in OSS "projects")... oh well, back to MS SQL Server - fast, reliable, easy to use, all the functionality a business could need, beats the pants off any "free (if your time is worth nothing)" software solutions any day.
How about making it robust and reliable first? You know, so that it it doesn't corrupt itself to shit at the first sign of trouble.
Why on earth did they use perl and not something a little more maintainable such as python? Just another good reason for me not to use a MySQL toy database.
t sql lacks many obvious features
how bout a 'for each row' operator
stupid cursors
<bart
sigh. MySQL seems to be losing it. Instead of focusing on implementing missing features in a way that is at least half-way compatible with the other database systems out there (db2, oracle, mssql, etc.), they go off and implement some half-assed freak solution. The more MySQL diverges from the defacto standards set by $$$$-ware databases, the less relevant it becomes.
From the web page:
=====
At the moment most calls to modules causes mysql to core (Something is up with the loader). Keep in mind that this is still experimental.
=====
Java stored procs? Jeeze, that's as bad as Perl stored procs. When can we expect tcl/tk stored proc support?
This is in the damn database, people. You don't want to be able to mangle files or make gifs bounce around on the screen.
sheesh.
Ok, so it is a neat hack. Sounds like an architecturally stupid idea.
My boss (Windows NT admin) and I were just discussing MySQL. We're running a number of small databases with Oracle on NT (with a University License), but we started talking about MySQL when I mentioned Slashdot was powered by it. Our web server and my workstation are Linux in NT land, and I try to plug Linux wherever I can. My boss is even learning Perl so he can code for our web server.
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?
WoohooO! Now, if they can only work out sub-queries, then I'd be 100% happy! Oh yeah...and get something like SQL*Loader cause I hate doing it the other way!
Wooden armaments to battle your imaginary foes!
Only a moron would use an open source database for an enterprise-wide application.
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, who uses MySQL?
DB2 rocks on GNU/Linux, by the way, and it's free as in beer. You should check it out.
--
I like to watch.
Yes, people, for the first time in three years I got a woody. This makes my dinky stand on end. Only Perl, combined with the greatness of MySQL, and then both being open source software, can turn me on enough to get a boner. Good job, boys and girls! Jolly good show!
Having stored proceedures in any language is better than not having them. The advantages of them are that they can be readily used by other programs, and they don't have to be compiled to be run, so they are faster. Since perl is an interpreted language I'd suspect that the latter benefit is lost. So what I'm hoping for is the future inclusion of stored proceedures written in SQL itself like what is offered in other DBMS systems.
Lee
Muslim community leaders warn of backlash from tomorrow morning's terrorist attack.
Yes I also know that I can/should recompile from source but most people will simply install the binaries.
MySQL was found dead in a shabby skidrow hotel room today, it would have been 9. Authorities stated that the cause of death was choking on a foreign object. Apparently Perl stored procedures had been shoved down its throat, and in it's already feeble state, MySQL succumbed. You may not have ever used MySQL, but it was the anemic aircooled volkswagon engine powering such web sites such as slashdot, truly an American icon.
"Hey! that's just Richard Nixon's enemies list with his name crossed out and yours written on top."
I had MySQL "running" (shit man, it was slow!) on a 900Mhz Intel Pentium 4 Linux box. I just got a nice, shiny, brand new 386 (4MB of RAM!) running Windows 3.1. Man alive! SQL Server running on the 386 kicked the poop out of MySQL. Baby! Then, while I wasn't expecting it, MySQL grabbed my winkie! Damn!
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.
O.K. lots of comments here like:
Perl stored procedures, IMHO, are an abomination
But can anyone cogently argue why not?
One reason is obviously non-standard and compatibility. However, all the XX-SQL syntax's are mutually incompatible too, right?
On the other hand Perl is a supreme text parsing language and most database functions are text handling of arrays and record hashes. Perl is very fast, mature and stable.
Just interest in more experienced thoughts.
The reality is that few (none that I know) development shops will give up their preferred programming languages in favor of these more proprietary languages. SO, the value of isolating business rules in the database is not realized. BUT, the dweebs come home to roost again, as they insist on doing SOME of their coding in stored procedures under the guise of the previously mentioned excuses as well as any other forms of obfuscated logic that they may employ. And why? Because it's their idea of fun, whether they know it or not. And the cost? One more language in use, another skill set needed, more cross-training, another MAJOR blow to portability across SQL databases and the increased vendor-specific dependency that comes with it.
Score one for the database publishers. Score one for the geeks (they get their vice). Loss goes to The Company as their costs escalate unnecessarily.
<bart
We use it quite a lot with Perl. Yea, we use Oracle too but MySQL usage is growing by leaps and bounds.
Sheesh, could they have made a more braindead move?
"I don't know that atheists should be considered citizens, nor should they be considered patriots." - George Bush
and they don't have to be compiled to be run, so they are faster
Compiling makes code slower? wow. How fast must C-Code be if you interpret it.
So what I'm hoping for is the future inclusion of stored proceedures written in SQL itself like what is offered in other DBMS systems.
You can't implement stored procedures in SQL, at least not very effectivly, as SQL usually lacks condtions (ok, most SQL variants have at least something like that), and loops.
No, I think, Perl is a quite good idea, it's rather easy to learn (if you can code) and it has lots of features.
Michael Bergbauer (michael.bergbauer@gmx.net)
I'll probably get modded down for daring to ask this, but am I missing something here? Why stored procs in Perl and not in, say... SQL?
Or is being server-agnostic a Bad Thing now?
Easy does it!
This comment has been submitted already, 276865 hours , 59 minutes ago. No need to try again.
Looks like it might be a while. Better just get PostgreSQL in the meantime.
Anyone? Is it still called Berkeley DB? Or is it now Sleepycat DB?
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.
Please mod this post only if you think others should/n't read this. I have enough ego^H^H^Hkarma. Thanks!
The biggest advantage of stored procedure is that it is compiled and can be executed immediately after load into memory and binding variables to it. You also can nicely pin stored procedures into a memory so it will not get booted out and it will perform pretty well.
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)
I n c o n c e i v a b l e !
I agree that the point of SPs is performance. But nothing I described would really preclude that. I wouldn't have used Perl either, but that doesn't mean it's a bad start. For now anyway, it's just a hack. They're not caching execution plans, etc. anyway, so this debate is almost entirely worthless.
On that note, you could afford to be nice you know, it wouldn't cost you a dime. Besides, I tried to re-butt by looking at the logic you employed above, and their really isn't anything other than a couple assertions. We could both come up with an entire list of social/political and technical ways of how to improve on the idea, but the tone of your message is completely insulting, so why should I waste my time?
You're not nearly as great yet as you would like to be. Your post gives that away more clearly than meeting you would.
Please mod this post only if you think others should/n't read this. I have enough ego^H^H^Hkarma. Thanks!
more people are beginning to realize that between python and php, perl is useless. not to mention the fact that the code syntax was stupid to begin with.
sorry perl, you had your day, but you are now outclassed.
so uh, anyone mind writing a makefile for this?
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...
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?
--
I like to watch.
PERL is not a good language, and probably an especially bad choice for a stored procedure langauge.
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.
We're on the road to Tycho.
I've looked through the Slash 2 source code and poked around it some and plan to write some plugins. They didn't do a bad job at all. Sure, Python would probably be a better choice, but Slash is better than 85% of Perl code out there.
Maybe you're talking about a different implementation of looping through a rowset, but you can do this...
DECLARE csrName CURSOR FOR SELECT * FROM #table1
OPEN csrName
FETCH NEXT FROM csrName INTO @Field
WHILE @@FETCH_STATUS = 0
BEGIN
--do something
PRINT @Field
FETCH NEXT FROM csrName INTO @Field
END
DEALLOCATE csrName
it's got all the shit you're griping about, is more stable under load than mysql, and is also faster for many real-world dataset/query combinations. just my 0.02USD
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?
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).
In my experience, PostgreSQL solved the performance problems with version 7 -- the "Postgres is slow" meme sticks around from PostgreSQL 6, which was slow.
(Which is interesting, because then, MySQL had a feature disadvantage and PostgreSQL a speed disadvantage, and it seems likely that the speed disadvantage was the easy one to remedy.)
Relational databases are slow and unscalable.
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.
Palm sized (not necessarily Palm OS) full color, voice over IP, 32MB RAM, running Java, wireless Internet. Cool!
it was sybase 6. They just embrace and extended it.
Free Unix? Free Windows. http://www.reactos.com
PostgreSQL has had Perl, Tcl/Tk, C, and hooks for other languages for some time now.
Slashdot went down the other day and lost a few hours of comments due to a database error. Transactions and ACID are FAR more important. If you want to use an open source database, go with a REAL database like PostgreSQL or Red Hat Database.
Well, one can dream, can't they? :)
Here you have a Mysql vs PostgreSQL comparison. :)
Enjoy it
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.
Hey freaks: now you're ju
I don't think that word means what you think it does.
and it's a worthy goal. Unfortunately, there's two major problems with them:
1. Too many people use them for procedural programming anyway. There's just things you can't do otherwise, so the capability has to be there. But the capability is often abused.
2. They're too proprietary. I'm not up on ANSI SQL standards enough to know by how much each one deviates and whether the ANSI standard provides a complete enough standard for SP operations, but it occurs to me that MySQL could stand to gain a lot by exactly toeing the ANSI standards line. Just a thought I guess.
Please mod this post only if you think others should/n't read this. I have enough ego^H^H^Hkarma. Thanks!
- 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.
If anything, then Java.
Oracle even moves PL/SQL (which isn't bad either) to Java. I'm sure Java will be the de facto SP language in the future for Oracle and DB2 (which have more than 60% of the market together). Just drop a JVM in the database core, and write a special JDBC driver for this.
No need to implement and maintain a fully new language.
I'm sorry, but all this talk of MySQL's lack of subselects has got me all worked up. Could someone explain to the rest of us why the suckers are so hard to implement? It always seemed to me like it would be intuitively simple -- just execute the subselect, dump the result set into a temporary table, replace the subselect text in the main statement with the name of the temporary table, execute the main statement, delete the temporary table, etc. Is there any reason why it can't be this simple?
Postgres home page
Remember that what's inside of you doesn't matter because nobody can see it.
Most people are using SQL engines just to store basic records, that sometimes even don't need any sort of indexation.
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.
{{.sig}}
Why would one use MySQL, while a powerful open-source DBMS has been available for years, and features stored procedures in any language (thru shared objects) ? AFAIK, it has always beat MySQL in benchmarks, too.
:)
Want a true solution for an OO-capable DBMS ? http://www.postgresql.org/
deepmind
Perl is a Scripting language ( read interpreted) Stored procedures, by default are SQL statements which are compiled by the server and used as db objects... so, doesn't this defeat the purpose of having a (speedy, compiled) SQL statement?
If you do what you always did, you get what you always got.
I also can't understand why it takes so long for them to add subselects. It's easy to implement and a great much-needed feature. Why is it taking so long ?
I enjoy seeing MySQL becoming a mature database, but I do not exactly know what this hype is all about!? PostgreSQL supports server-side programming for a long time now (the same way it supported transactions long before!). At the moment it supports three different (and working) implementations:
- 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
Basically, it says that MySQL is faster and more stable, and that PostgreSQL has transactions. Which is more important? How about a database that doesn't crash when you put a tiny bit of load onto it. I'll stick to MySQL, thank you, and the addition of Perl as a language for stored procedures will make it even better and more useful.
Even Slashdot wants to hide some things
I bet that's the reason why.
I mean the code is all there for anyone to change/add to.
Link.
What's wrong with this? Just code the sublayer correctly, and you're on your way...
Ok, so all they ever learned me at collidge was procedural programming, no OOP
Need a Linux consultant in New Orleans?
Over the years memory leakage (and usage) has been alot bigger problem with Java then with Perl. Nevertheless, on the whole after 5 years of programming with Perl I have never encounted any significant bugs with a stable release.
lots of one and two letter functions
Be a little more specific . . . Here is list from the reference:
abs, chr, cos, do, die exp, int, hex, kill, lc, log. m//, new, not, oct, pos, pop, ref, s///, sin, sub, tell, tie, vec
All these are clear english words or are derived from Unix or regexp. Anyone with a Unix or C background would immediately recognize most if not all.
a million operators
At least, probably more.
Apparently, I need to become more familiar with PostgreSQL. I looked at using it for a client a while back, but I quickly backed off of it when I realized that it's not a viable option for Windows OSs (there is a port of it for Windows, but I guess it's not very well tested or trusted). That is still an issue, but being able to do SPs with it would be nice.
On the other hand, MySQL is coming along nicely on Windows, so I might just wait to see what they come up with. I probably won't get clients to use either anyway and I don't know how hard I would push at this point, but I'm definitely going to keep these options in mind for clients that hate using proprietary products (which is a tough thing when you start talking about database technology!).
Please mod this post only if you think others should/n't read this. I have enough ego^H^H^Hkarma. Thanks!
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
The problem is that this seperates related logic. when doing maintenance; one may then have to hunt down the SP and switch between a SP editor and their application code.
It is often a better arrangment to keep related logic together. But, if the developers don't know SQL (which makes them not very good developers IMO), then SP's may simplify their lives.
Real programmers only use SP for speed, not better code management.
Table-ized A.I.
Ought to work.. anybody tried using it?
When I read your post I was a little surprised, when I've used cursors they've been extremely
efficient. What types of things were you doing, with how much data? Granted I'm not using millions of records, but I don't think cursors are appropriate for such large data sets.
In my test I'm running a SQL 7 on a 700mhz (or something close) P3 with 512k.
I open a local fast_forward cursor, select 5 fields (integer & 4 varchar(50)'s ) into variables,
loop through all of the records and print the integer at the end. Here are my result times:
1000 : Under 1 second
10000 : 1 second
201000 : 10 seconds
Maybe I'm missing something or I don't use cursors the way the rest of the industry does, but I can deal with those performance levels.
ADO, OLE DB, ODBC and DB-Library all expose the functionality of cursors through recordsets. From my experience recordset operations have to be one of the most common operations of database applicaions. How often do you open a recordset and loop through the records? Sound like the functionality of a cursor? Is it a coincidence that there are arguments to OpenRecordset methods (such as adOpenForwardOnly, adOpenKeyset) that map almost directly to arguments that are passed when creating T-SQL cursors. These API's create cursors when you use OpenRecordset. So basically from your statement you can conclude that one of the most common data operations performed against an MSSQL database from a client application is absolutely horrible in performance. I'm not buying it. Neither are the millions of applications and developers using ADO and ODBC recordsets with a SQL server database - I think somebody might have noticed.
Now since we know it's not cursors that are slow, perhaps it's just the database product itself? Nope, it's the fastest tested database in the world in several areas.