Adaryl, Here's my $.02
Chapter 32 (Working With Stored Procedures), page 1057, in the book Web Application Construction Kit Fourth Edition (Ben Forta and Nate Weiss), there is a pretty good description about why performance may be enhanced by using stored procedures: "Depending on the situation, using stored procedures can often cause an application to perform better. For two basic reasons, the use of stored procedures will speed your application. First, most database systems do some type of precompilation of the stored procedure so that it runs more quickly when it is actually used. For instance, Microsoft SQL Server makes all its performance-optimizing decisions (such as what indexes and which join algorithms to use) the first time a stored procedure is run. Subsequent executions of the stored procedure do not need to be parsed and analyzed, which causes the procedure to be run somewhat more quickly than if you executed its SQL statements in an ad hoc fassion every time. Generally, the more steps the procedure represents, the more of a difference this precompilation makes. Oracle servers do something very similar." "Second, if you compare the idea of having one stored procedure verses several <CFQUERY> and <CFIF> tags in a template, the stored procedure approach is often more efficient because less communication is necessary between ColdFusion and the database server. " Ok - enough stuff from a book. There are dozens of things to consider when deciding upon whether or not to use stored procedures or query's directly in the cf code. One can make an argument that once a stored procedure is written against a table, everyone can re-use that stored procedure. If the company is big enough to have both a SQL team and front end team, then the duties can be broken down easier. Our entire application is written using <CFSTOREDPROC> calls exclusively, and all of the stored procedures are done in MS SQL Server. There is no <CFQUERY> tags. Some of our searches are accessing tables in excess of 18 million records. One advantage from a security standpoint is that we do not have to grant any access to the database other than the EXECUTE privilege on the stored procedure. No generic insert, update, delete rights are given to individual users for the tables. So in the event that someone did gain access to your database they could still just execute stored procedures. If all the rights were there they could do select, insert, delete statements directly against the tables. So database integrity and security are greatly enhanced. Chris --- Adaryl Wakefield <[EMAIL PROTECTED]> wrote: > See that i knew. I have been diving though my libary > all day for a better > explantion though. Care to elaborate if you have > time? The only security > issue that i am aware of is the url attack which can > be defended with > <cfqueryparam>. > A. > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, September 12, 2002 2:55 PM > Subject: Re: [KCFusion] deletes on database? > > > > > > Folks, > > > > One of the chief reasons for using stored > procedures is to make > > applications more secure. > > > > Girish > > > > > > > > "Daryl Banttari" > > <[EMAIL PROTECTED] To: > <[EMAIL PROTECTED]> > > > cc: > > Sent by: > Subject: Re: [KCFusion] > deletes on database? > > CF-List-owner@kcf > > usion.org > > > > > > 09/12/02 01:33 PM > > Please respond to > > > CF-List > > > > > > > > > > > > > > I (personally) find using stored procedures > instead of simple queries to > be > > a nuisance for anything but large, cross-platform > projects. Instead of > > replacing simple queries with stored procedures, > you can just use > > <cfqueryparam> to get the same performance benefit > (query plan reuse) in > > SQL > > Server or Oracle. > > > > --Daryl Banttari > > Macromedia > > > > ----- Original Message ----- > > From: "Adaryl Wakefield" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Thursday, September 12, 2002 12:58 PM > > Subject: Re: [KCFusion] deletes on database? > > > > > > > Hey why is it every time I post something it > generates an avalanche of > > > activity. :-) Its like you guys forget this > thing exist. > > > So (and anybody can field this one) I was > studying how to write stored > > > procedures in SQL Server last night I was > like..gee. thats a lot of > > freaking > > > work. So what would you say was the most > appropriate time to use stored > > > procedures vs. just putting it on the page. > > > A. > > > P.S. As a matter of fact Misty, no, I don't have > a job....I'm a > > consultant! > > > > > > ----- Original Message ----- > > > From: "Misty Woodward" <[EMAIL PROTECTED]> > > > To: <[EMAIL PROTECTED]> > > > Sent: Thursday, September 12, 2002 10:59 AM > > > Subject: RE: [KCFusion] deletes on database? > > > > > > > > > > In one of the projects I am working on now, > they use Oracle stored > > > functions only for database calls. When I first > started doing it, I > hated > > > it. Honestly, I still hate it. From a > development standpoint, as far as > > time > > > goes, i would say it takes longer than just > writing it directly into CF. > > > When Im coding in CF I just write out my CF > query and move on. When > > writing > > > Stored Functions your 8 line query turns into a > huge function where you > > have > > > to declare varaibles, check for variabls, create > the function, create > > your > > > IN Variables, write in your return values, etc. > The part that Stored > > > Functions shine in, is with code re-use. The > function I wrote is to be > > used > > > across 3 systems. Which means, I only have to > change it in one place and > > I > > > can manage all 3 sites with it. > > > > > > > > Misty > > > > > > > > ---------- Original Message > ---------------------------------- > > > > From: "Glenn Crocker" <[EMAIL PROTECTED]> > > > > Reply-To: [EMAIL PROTECTED] > > > > Date: Thu, 12 Sep 2002 10:50:12 -0500 > > > > > > > > >MessageI generally don't do any JOINs in my > CF code, instead using > > > > >queries/views to accomplish them. > (Sometimes, when a parameter needs > > to > > > be > > > > >way inside a JOIN, I'll put one in CF.) I > haven't done the full-on > > > stored > > > > >procedure architecture, but most of my > projects are just one or two > > > > >developers, so it's not a big team thing with > lots of turnover. > > > > > > > > > >-glenn > > > > > > > > > > -----Original Message----- > > > > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED]]On > > > > >Behalf Of Ryan Hartwich > > > > > Sent: Wednesday, September 11, 2002 10:28 > PM > > > > > To: [EMAIL PROTECTED] > > > > > Subject: RE: [KCFusion] deletes on > database? > > > > > > > > > > > > > > > Adaryl, > > > > > > > > > > Somewhere I interviewed or spoke with > someone about this topic. It > > was > > > my > > > > >understanding that the individual coders > being employed were > generally > > > not > > > > >allowed to do any form of > insert/update/delete into the database > > through > > > > >their code. They were however permitted to > write Select statements > to > > > > >tables and/or views. The coders were given > a set of API calls > > utilizing > > > > >XML services internally to do the direct DB > manipulation. The idea > > was > > > to > > > > >force data integrity and consistency by only > allowing data to be > > modified > > > > >through approved prebuilt modules. > > > > > > > > > > A permutation of this would be to not > permit inline DB calls in > your > > CF > > > > >code and to call all DB statements via stored > procedures. I have > > heard > > > this > > > > >speeds up development, improves reusability, > and quality. I'm a bit > > > > >skeptical of this however. I have spent a > few years writing code as > > the > > > > >primary developer and write my SQL code > directly inside of my CF > > pages. > === message truncated === __________________________________________________ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED]
