----- Original Message ----- From: "Wolfram Kraus" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 30, 2004 10:04 AM Subject: Re: Stored Procedure?
> Heyho! > > [EMAIL PROTECTED] wrote: > > 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? > That would be the optimal solution for MySQL 5.x ;-) > > > 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. > My original posting was a little bit short, sorry for that! I know what > SPs are, I only wanted to point out that you don't need SPs to get the > median without heavy calculations on the client. > The definition of user levels/roles is another story. Btw: Rhino was > missing/hiding the part with hiding complexity from users in his > excellent explanation. > You're absolutely right; I failed to mention the benefits of making the users lives easier by letting the administrators do the "heavy lifting" via stored procedures. In truth, I simply didn't think of that benefit at the time (it was late and I was overdue for bedtime ;-) but I probably would have omitted it any way simply because the original question didn't make me think of those issues. I was mostly just focusing on what a stored procedure was since that is what the questioner seemed to want. Shawn was absolutely right to add the benefits of hiding the complexity from users. > > > >>> 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. > > Do you mean that you could run the same code that was in the stored procedure from the command line, therefore eliminating the need for an SP? Well, yes, that is true but how would you get the result to a client program? Or would you force users to sign on to the server to execute the code from the server's command line? Assuming stored procedures are implemented similarily in MySQL to the way they are in DB2, a stored procedure would work from both the server's command line *and* a client program. That means you simply build your stored procedure once and can handle both scenarios. If you want to invoke it from a client program, you simply call it, passing the necessary parameters and then handle the result within the client program. If you prefer to execute it right at the server, you can do that too with the same call statement you used from the client program, except that you hard code the values; then, the operating system displays the result of the stored procedure on the console. > > > > 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. > Point taken, nice example ;-) > I am not really an DBA, I am more like a db-user (not in your way of > definition) ;-) As I said above: definition of user levels/roles are a > complete different thing. > > > > >>> Mike > >>> > >> > >> Wolfram > >> > > > > > > Shawn Green Database Administrator Unimin Corporation - Spruce Pine > > > > Wolfram > Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
