Stored Procedures in MySQL 5
The other day I got pointed in the direction of a new feature in MySQL 5 — stored procedures. Having been previously available in other database systems such as Oracle and DB2, the introduction of this feature sees MySQL AB push their product into the enterprise market. I’d never encountered stored procedures before (so forgive me if my understanding is a little wooly), but they sound very useful.
Stored procedures are compiled SQL statements on the server-side, complete with logic and return values. These procedures can then be easily invoked from standard scripts and programs, removing a lot of complexity from the client-side and, theoretically, improving the performance of the application by moving the logic to the server-side. I say theoretically because I’m not totally convinced this will be a meaningful improvement — perhaps for enterprise applications, but maybe not too noticeable in your average dynamic website.
A simple example of a stored procedure might be an SQL statement which queries a table for old records. The ‘expired’ records are then removed, together with all references in linked tables. Finally a log table is updated with the results of the procedure.
God knows what the syntax of may look like (I haven’t figured that much out yet), but you get the idea.
Posted 8 years, 1 month ago
Hi Phil
Stored Procedures are a form of a compiled SQL Query. Each time an SQL Query is passed to a database, such as MySQL, it has to go through a number of steps before one receives the data:
1. Syntax Check
2. Compilation
3. Execution
By storing an SQL Query in the form of a stored procedure, stages 1 and 2 have already been completed.
In the case of Microsoft SQL Server, a stored procedure can be "prepared" which means that second time round when the Query is run, it potentially will be faster in execution. It will be interesting to see if MySQL 5 supports this.
I teach SQL Server 7/2000 development and find that even though the database server supports them, many developers don’t use them and prefer to hard code the Query in the form/program/web page. Hmmmmm.
Microsoft SQL Server stored procedures just like other database server types rely on TSQL (Transact SQL) which is a language in its own right. It combines SQL with many useful keyword/functions from manipulating strings to using loops, conditional statements etc.
Sad as it may sound, they are great.
I cannot wait to get my hands on MySQL 5 as I think it will certainly give Microsoft a run for their money.
Sounds like a training session!
BB
Berb Brown · 8 years, 1 month ago
Cheers Robert! It’s not often someone leaves a comment that’s longer than the post ;-)
I’m looking forward to checking out MySQL 5 too - hopefully it’ll simplify things (with stored procedures) and improved performance overall. Though when our favourite ISP will upgrade is unknown... ;-)
Phil · www · 8 years, 1 month ago
Christ, someone wrote a comment longer than mine!
I hope MySQL emulates MSSQL by including the random ‘bug’ that boils down to how you name your stored procedures.
Apparently you reduce performance by prefixing a procedure with ‘sp_’, if the database it accesses is NOT the master database. Nice, eh?
(http://www.databasejournal.com/features/mssql/article.php/1565961 , #5)
Andrew · www · 8 years, 1 month ago
Hee Hee
Couldn’t help myself. When I wrote the post, I had just finished teaching MS SQL Server Development and was in full flow.
Soz. :-)
Berb Brown · 8 years, 1 month ago
All,
The performance gain by parsing and compiling the stored procedure on the db server is one aspect of the advantage of stored procedures. Another is the ability to encapsulate the SQL from the application programming language.
The way this benefits me is that I can build my stored procedures ahead of time to return exactly what I am looking for.
Whether that be a cursor or recordset based on some predefined query parameters, or an automatically generated key value in response to an insert or some other DML, I no longer have to worry about concatenating strings and escaping quotes and other special characters.
I simply pass parameters to stored procedures that handle all of the selects, inserts, updates and deletes and report back with results to the business tier when they have finished. It is the true definition of reusable encapsulated code.
In my professional opinion, MySQL is a great tool, but ultimately unusable for my needs, until it fully supports stored procedures. Then it is a real option.
James.
James Laughlin · www · 8 years ago
I agree with your comments James.
MySQL is a great tool, but I find it very frustrating having to embed SQL into a page.
Coming from a VB/VB.NET background, it actually goes against the grain having to embed the SQL queries.
I cannot wait until the day that MySQL 5 supports stored procedures in any shape or form. It must be better than building your SQL queries in strings.
BB
Berb Brown · 8 years ago
I think for the average website it may seem stored procedures don’t offer you a great deal. In that environment data isn’t really that valued.
Its in applications that they will show you their true value where data is king. I have the latest version of MySQL loaded and have been playing with stored procedures for a few weeks, pretty basic when compare with the big boys but its a start.
I’m also currently developing a MySQL stored procedure development web site, by no means finished but your welcome to take a look
www.mysqldevelopment.com
Andrew Gilfrin · www · 7 years, 5 months ago
Excellent
Saroja · 6 years, 2 months ago