news <[EMAIL PROTECTED]> wrote on 11/30/2004 07:58:18 AM: > Michael J. Pawlowsky wrote: > > Because you will be downloading 500,000 rows... And I don't really > > think that was the point. Who cares what the example is. Personally I > > was quite impressed with great explanation. > I second that it was a great explanation! Please don't get me wrong! > And furthermore I will only download one row two times: > select count -> one row > select ... limit count/2, 1 -> one row
Who's to say that his SP can't use your method and work just that much faster? The point was that stored procedures can "automate" complex processes and that the end user of the database doesn't necessarily need to know how the process works (or how to code that process in SQL) if they know how to call the procedure and interpret its results. Stored procedures provides a method for a DBA to efficiently provide DB users with results they would normally need to code by hand to achieve. How many database users do you know that understand how to correctly compute a median value or generate a cross tab query? If you, the DBA, write stored procedures or UDFs to perform these and other "complex" tasks (relatively speaking) then you have simplified the end user's data access in some significant ways. There are much more complex things you can do with SPs than just computing medians but it made an EXCELLENT example. > > > I would have simply said a chunk of code that runs on the server that > > the client can call. And this guy took the time to put together a > > really good reply with an example. > I don't say that the example is bad, I only said that in MySQL you can > do this without a SP. Yes, but as I mentioned above, that would require some modest SQL skills from the user writing the query. Not all users are as comfortable with SQL as we are as administrators. Even if you give them some "cut-and-paste" code that did this function, they would still need use it properly. This is especially difficult for those users who rely on visual query builders (GUI interfaces) to automate their SQL generation. But, If I give them the name of a stored procedure that reliably computes what they need then the time I spend helping those who don't want to learn SQL to write useful queries goes down considerably. > > > > > Mike > > > Wolfram > Shawn Green Database Administrator Unimin Corporation - Spruce Pine > > > > Wolfram Kraus wrote: > > > >> [...] > >> > >>> 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. > >>> > >>> > >> [...] Pardon my ignorance, but why can't you do this (in MySQL) > >> with a "select count ..." and afterwards a "select ... order by... > >> LIMIT"? All the work is done on the server, too. No need for a SP > >> here. > >> > >> Wolfram > >> > >> > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
