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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
