List:General Discussion« Previous MessageNext Message »
From:Mike Date:January 7 2004 5:47am
Subject:Re: Bet the Business
View as plain text  
>So what are the justifications? What makes a wise stored procedure 
>and an unwise stored procedure?

Use them when there is a real benefit to using stored procedures. I will give one example
when I would have used them:

With a CRM solution I once wrote, I wanted to get a list of n randomly selected customers
to issue a survey to. The generation and retreival of the customers was fairly painless,
you simply add ORDER BY RAND() to your query with a LIMIT n clause. The catch is that I
also wanted to add a row to a log table for each of these people to show that thay were
going to be part of this particular random mailing. Now of course at this point these
people were going to be on the client side. The solution is to SELECT the random people
into a temporary table, then mangle the table to be able to INSERT it into the log table.

If I would have had access to stored procedures, I would have had the stored procedure
retrieve the people and both return them to the client app AND INSERT a row to the log,
saving me time and trouble.

One thing to remember is that every stored procedure you execute steals resources that
would otherwise go to handling queries, so you do not necesscarily see a performance
boost by using stored procedures for everything, in fact a MySQL server handling a lot of
stored procedures could very well show poorer performance than a proper n-tier
application.

Regards,
Mike Hillyer
www.vbmysql.com
Thread
Bet the BusinessIan O'Rourke6 Jan
  • Re: Bet the BusinessDaniel Kasak6 Jan
  • Re: Bet the BusinessJochem van Dieten6 Jan
  • Re: Bet the Businessrobert_rowe6 Jan
    • Re: Bet the Businessian.orourke6 Jan
      • Re: Bet the BusinessMatt Davies6 Jan
  • Re: Bet the BusinessMichael Bacarella6 Jan
  • Re: Bet the Businessrobert_rowe6 Jan
    • Re: Bet the BusinessIan O'Rourke6 Jan
    • Re: Bet the BusinessMartijn Tonies6 Jan
  • ..mysql question bank.. Needed..Amanullah7 Jan
  • Re: Bet the BusinessMike7 Jan
    • Re: Bet the Businessian.orourke7 Jan
  • Re: Bet the Businessrobert_rowe7 Jan
  • Re: Bet the Businessrobert_rowe7 Jan
RE: Bet the BusinessDan Greene6 Jan
Re: Bet the BusinessUdikarni7 Jan