List:General Discussion« Previous MessageNext Message »
From:Udikarni Date:January 7 2004 8:02pm
Subject:Re: Bet the Business
View as plain text  
The fundamental reason for using stored procedures is performance.

Stored procedures are compiled code. This means the database has reviewed the SQL, came up
with the most efficient plan of action (often sorting through thousands of permutations
when multiple table joins are concerned), and (given the right directives from the DBA)
has this plan of action cached in memory, waiting to be called by name (optionally with
parameters).

When you issue your SQL directly from the application or business tier rather than use a
stored procedure - you are issuing what's called dynamic SQL. The process of coming up
with a plan of action for this SQL repeats itself every time the SQL is issued and is
very expensive.

In sophisticated transaction system it's common to see a 1000-fold improvement in the
number of transactions per second processed - when switching from dynamic SQL to stored
procedures. This is a big deal.

On a small application with simple SQL and not much action - it's less of an issue. On an
airline reservation or amazon type situation - stored procedures are an absolute must,
and yes, they tie you to the specific database at hand because every database's stored
procedure language is different (that's one way for the vendors to make it hard to
switch). No pain no gain.
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