Thread: Stored Procedures
I am starting to develop an application and I am considering to use stored procedures. Is there a performance penalty for implementing many DB operations with stored procedures? For example, if my app has 50 tables, is it a good idea to write store procedures for inserting, deleting and updating data for all of them? What if, additionally, I include stored procedures for information retrieval (SELECT statements)? What would be a good criteria to decide how far to go with stored procedures?
With respect,
Jorge Maldonado
On Mon, 28 Feb 2011 14:00:24 -0600, JORGE MALDONADO <jorgemal1960@gmail.com> wrote: > I am starting to develop an application and I am considering to use stored > procedures. Is there a performance penalty for implementing many DB > operations with stored procedures? For example, if my app has 50 tables, is > it a good idea to write store procedures for inserting, deleting and > updating data for all of them? External orders have more overhead: time of transmission, then compilation and execution (AFAIK, both possibilities are left in cache after 1st processing), stored procs jump the 1st step. The main advantages of stored procs are (IMHO): * With good permissions, stored procs rights & users management, nobody can issue external orders to do the same, * Users can't override your security (in order to avoid OID dependency, I use my own users system in which I can revoke one in 2 clicks - by disabling the CONNECT permission.) > What if, additionally, I include stored > procedures for information retrieval (SELECT statements)? For this one, I'm not sure: I kept complicated SELECTs that return a single row as a result stored, while keeping simple SELECTs on the client side. > What would be a > good criteria to decide how far to go with stored procedures? The complexity of what can be done with them and the invisibility of sensitive procedures because of user's rights (or not) upon them, tables & columns. Every big proprietaries databases also have them. HTH JY --