----- Original Message ----- From: "Steve Grosz" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, November 29, 2004 10:55 PM Subject: Stored Procedure?
> Ok, I'm new to MySql (or SQL in general). I'm curious what exactly a > stored procedure in MySql is, and what the benefit would be? > Stored procedures are new in MySQL 5.0 but very few people are running 5.0 yet. I think 5.0 is available in a pre-beta but I haven't heard anything about how stable it is. In other words, you may have to wait a while to use stored procedures unless you are really eager to be 'bleeding edge'. Stored procedures are very popular on databases that already have them, like DB2. Their main advantage is when they move a lot of the processing of a given task to the server from the client. The classic example goes something like this: Suppose you have a table with a million records, test scores from a widely taken exam for example. You need to find the median mark - NOT the average! - so your algorithm needs to read all million records, sort them into ascending or descending sequence by the test score, then read exactly half way through the sequenced list to find the middle record; that's the one that contains the median score. If that work were to happen on the client, the client would have to fetch a million records, sort them all, then read through half of those records until it found the middle record; then it would report on the median mark. There would clearly be a lot of network traffic involved in getting all those records to the client, sorting them and then reading through the sorted records. Let's say you moved the majority of the work to the server and simply invoked the program that did the work from the client. The client simply tells the program (which we call a stored procedure) at the server to read all the rows, sort them, read the first half of them and report on the median mark. In this scenario, the network traffic drops to almost nothing: there is the instruction that invokes the stored procedure and then the median mark returning from the stored procedure. Everything else takes place within the stored procedure ON THE SERVER. The server is often a particularly powerful computer with extra fast devices, more memory, etc. so it is able to do the work faster than the client would in many cases, even if network traffic wasn't an issue. The net result is that the same work gets done with far less network traffic. So, a stored procedure is, in essence, simply a program that runs on a server without a network between it and the database. The stored procedure is invoked by a client program and returns a result to the client. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
