news <news@stripped> 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/mysql?unsub=1
>
