Further to this thread, in my own experience and in discussion with our Sybase and MS-SQL dba's, stored procedures are best used for data integrity checking and enforcement. They are, and can be, complex code blocks that many seek for performance improvements in lieu of looking at the user sql calls, hardware or other tuning options. Granted, the code is pre-compiled, but the overal performance benefit in any query situation is negligeable. Views can provide most of any other joined table data. The real benefit is with data integrity checking, where related table contents may cascade adds or deletes where needed for complex relationship maintenance. Writing and implementing this type of code at the application level would certainly increase time to execute and runs higher risks of disconnect or other session failure, necessitating transaction locking be enabled.
Another problem is that stored procs can be complex code blocks residing on the db that must be iteratively tested before use. Since managing the intregity of the data is part of the initial design phase, this code is 'carved in stone' as if it were stone, and becomes part of the db definition. Allowing applications people to change (as if often required as user requirements change) the stored procs in lieu of examining there own sql logic may a mistake. IMHO. -alan ----- Original Message ----- From: rudy <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 30, 2002 3:03 PM Subject: Re: [CFTALKTor] SQL - Returning Column Names > > Basically, a Stored Procedure is a piece of SQL code that resides in > > the RDBMS and has already been checked for syntax errors and been > > precompiled whereas when you submit a query through ODBC it has > > to be parsed for syntax errors, then compiled and run, thus a stored > > procedure would return results faster since it is already parsed and > > compiled. > > hi marc > > other than a couple of typos, what you wrote there is perfect > > >> Would there be any benefit to setting the application var (myList) > >> based on the stored procedure (the query in SQL)? > > > >I don't really understand that statement. > > yeah, me neither > > > nolan, the stored proc just finishes a bit sooner, is all > > if it takes 2 milliseconds to parse the sql, that's what you save on each > database call > > if it takes only another 2 milliseconds to fetch the data, then that's a > 50% improvement > > on the other hand, some database calls run for 600 milliseconds, so it's no > big deal > > note that no matter whether it's a stored proc or a dynamic query, > eventually the database has to run the sql, and that's typically where the > load is > > you know what's more important? maintainability > > imagine taking over somebody's cold fusion code, and all you see is some > variables being set, and data coming back -- heck you don't get to see the > tables at all, never mind using "select star" to grab the column names... > > > It might be good if you plan on putting ALL your apps SQL code in > > stored procedures, just for the sake of separting the SQL code from > > the CFM code or whatever logic is behind that. > > that actually makes sense ;o) > > but it's a beeatch to maintain, because making changes to the tables > (e.g. to add new functionailty) requires mods to all those stored procs > > > For simplicity, maybe a bit of lazyness, I would just use a CFQUERY > > me too > > especially the simplicity part > > > rudy > http://rudy.ca/ > > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "rudy" <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Alan Goldberg" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
