> Why it is better in your opinion to use stored procedures for insert/update/deletes?
The quick answer is that stored procedures are pre-complied execution plans. To run in the database engine, every SQL statement is compiled into something the database people call execution plans. These plans are the code that change and read data. Whenever a query is run that changes the structure of tables, efficient query plans result in the best performance of the database. A rule I follow is that if any changes are made that result in any table index being changed then use stored procedures. Insert and deletes always change indexes, most of the time updates do too. SQL passed in a cfquery has to be complied into a query plan on the fly every time. (Yes some db engines can remember a previous ad hoc query but the DB still has to prove the previous is the same.) Whereas, when using stored procedures, the CF/DB interface needs only to pass values and not the query and the values. Likewise to insert, updates and deletes, any select query that uses un-parameterized joins should be a made a database view. A view is a stored query, that is pre-complied execution plan. Rarely should a cfquery include join statements. Joseph At 02:47 PM 10/18/2004, you wrote: >Joseph, > >I was just curious why it is better in your opinion to use stored >procedures for insert/update/deletes? > >Aaron > > >On Mon, 18 Oct 2004 14:41:22 -0600, Joseph Flanigan ><[EMAIL PROTECTED]> wrote: > > The technique of assigning a SQL query to variable and then resolving the > > variable in the cfquery is called the folded string technique. Since > > cfqueryparam is a sub-tag of cfquery it cannot be used in a folded string > > outside the cfquery. The function of cfqueryparam is to do data type > > binding between CF and the database. So forget trying to used cfquaryparam > > outside a cfquery. There are other approaches that work better. > > > > 1. Don't use cfquery for inserts, use stored procedures. Inserts, updates > > and deletes always work best using stored procedures. But that is not > > always an option. > > > > 2. Put the query in a cffunction and pass parameters to the query. By > > wrapping the query inside a cffunction and passing the insert values as a > > structure into to function uses pass-by-reference, there is minimum moving > > of variable data and it still uses cfqueryparam for data binding. > > > > 3. Use SQL variables to build folded strings. If you still want to use > > folded strings, then build the string using SQL variables. For data > > binding, declare the SQL variable and its data type then set the SQL > > variable to the CF variable value. > > > > Below are examples of both the folded string and the cffunction using your > > table example. I like the cffunction approach. It is what my CFSQLTool > > generates. In the tool is wizard for running stored procedure that uses a > > variation on folded strings. So both techniques work. > > > > Joseph > > > > <!--- folded string technique ---> > > > > <cfsavecontent variable="DansQuery"> > > <cfoutput> > > DECLARE @product int > > SET @product = #product# > > > > DECLARE @c_productDesc varchar(50) > > SET @c_productDesc = '#c_productDesc#' > > > > DECLARE @active bit > > SET @active = #active# > > > > INSERT INTO tblProducts > > ( > > product , > > c_productDesc , > > active > > ) > > VALUES > > ( > > @product, > > @c_productDesc, > > @active > > ) > > </cfoutput> > > </cfsavecontent> > > > > <cfquery name="qtblProducts_INS" datasource="JosephTest" maxrows=-1 > > > #PreserveSingleQuotes(DansQuery)# > > </cfquery> > > > > <!--- cffunction technique ---> > > <cffunction name="Create" returntype="boolean"> > > <cfargument name="theParams" type="struct" default="#this#" > > required="yes" > > > <cfargument name="DSN" type="string" default="#this.DSN#"> > > <cfset var qtblProducts_INS = "" > > > > > <cfquery name="qtblProducts_INS" datasource="#Arguments.DSN#" > > maxrows=-1 > > > BEGIN TRANSACTION > > INSERT INTO tblProducts > > ( > > [product], > > [c_productDesc], > > [active] > > ) > > VALUES > > ( > > <cfqueryparam value="#theParams.$product#" > > cfsqltype="CF_SQL_INTEGER" >, > > <cfqueryparam value="#theParams.$c_productDesc#" > > cfsqltype="CF_SQL_varchar" >, > > <cfqueryparam value="#theParams.$active#" > > cfsqltype="CF_SQL_bit" NULL="#isNull(theParams.$active)#" > > > ) > > > > COMMIT TRAN > > </cfquery> > > <cfreturn TRUE> > > </cffunction> > > > > At 08:33 AM 10/18/2004, you wrote: > > >I have a variable that contains the following: > > > > > >Insert into tblProducts (product,c_productDesc,active) > > >Values > > ><cfqueryparam cfsqltype='CF_SQL_INTEGER' value='new Product name'>, > > ><cfqueryparam cfsqltype='CF_SQL_VARCHAR' value='new Product desc'>, > > ><cfqueryparam cfsqltype='CF_SQL_BIT' value='1' > > > > > >I want to execute this string inside a cfquery tag. 1st problem is, > > >when I am building the string and looping over an array, if I try to > > >use < and > in the string, my string ends up being empty. If I replace > > >that with @lt; and @gt;, the string is built OK, as displayed abouve, > > >but get an ODBC error on the @. > > > > > >I have played around with evaluating and DE, but cannot get past this > > >point. Is this possible? > > > > > >---------------- > > >Dan O'Keefe > > > > > >---------- > > >[<http://www.houseoffusion.com/lists.cfm/link=t:4>Todays Threads] > > >[<http://www.houseoffusion.com/lists.cfm/link=i:4:181752>This Message] > > >[<http://www.houseoffusion.com/lists.cfm/link=s:4>Subscription] > > >[<http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=1553.1473. > 4>Fast > > >Unsubscribe] [<http://www.houseoffusion.com/signin/>User Settings] > > >[<https://www.paypal.com/cgi-bin/webscr?amount=&item_name=House+of+Fusi > on&business=donations%40houseoffusion.com&undefined_quantity=&cmd=_xclick>Donations > > >and Support] > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Sams Teach Yourself Regular Expressions in 10 Minutes by Ben Forta http://www.houseoffusion.com/banners/view.cfm?bannerid=40 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181798 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
