Re: cfqueryparam vs cfstoredproc?
Ok, makes sense. We use a CFC here that I built a long time ago that builds insert/update queries based upon the database's meta data. It puts in the cfqueryparams and does data validation prior to that. While the cfqueryparams were put in for those very reasons, the seen benefit by anyone using it is the fact they no longer have to write those queries. They just pass in typically the form structure, an action flag and the table name then it does the rest. I could see them still liking an SP that did it then the wrapper for the SP would need all the appropriate checks on the data coming in to hopefully avoid the possibility of a SQL injection attack. On Thu, Oct 30, 2008 at 2:57 PM, Adrian Lynch <[EMAIL PROTECTED]>wrote: > EXEC()ing a string won't produde the same execution plan as the base SQL > (<---<< a guess) and you lose cfqueryparam and cfprocparam's biggest > benefit, protecting against injection. > > Adrian > > -Original Message- > From: Aaron Rouse > Sent: 30 October 2008 19:52 > To: cf-talk > Subject: Re: cfqueryparam vs cfstoredproc? > > > I do you feel it would defeat the point? > > On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch > <[EMAIL PROTECTED]>wrote: > > > Exactly, which kinda defeats the point I feel. > > > > I've got a few ways that I might try but for now I'm back to writing SPs. > > > > If anyone's interested, I have the full DAO code here: > > > > http://adrianlynch.co.uk/post.cfm?postID=21 > > > > Adrian > > Building a database of ColdFusion errors at http://cferror.org/ > > > > -Original Message- > > From: [EMAIL PROTECTED] > > Sent: 30 October 2008 18:28 > > To: cf-talk > > Subject: RE: cfqueryparam vs cfstoredproc? > > > > > > exec() > > or sp_executesql > > > > You would need to pass in the arguments as a list to the procedure and > > then do the looping and building of a dynamic query with SQL. Then > > execute what you have created. > > > > Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you > > will have to take additional steps to paramaterize it. (requires > > sp_executesql) > > > > FYI: My advice assumes MS SQL. > > > > ~Brad > > > > Original Message > > Subject: RE: cfqueryparam vs cfstoredproc? > > From: "Adrian Lynch" <[EMAIL PROTECTED]> > > Date: Thu, October 30, 2008 1:06 pm > > To: cf-talk > > > > An open question then... > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314633 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
EXEC()ing a string won't produde the same execution plan as the base SQL (<---<< a guess) and you lose cfqueryparam and cfprocparam's biggest benefit, protecting against injection. Adrian -Original Message- From: Aaron Rouse Sent: 30 October 2008 19:52 To: cf-talk Subject: Re: cfqueryparam vs cfstoredproc? I do you feel it would defeat the point? On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch <[EMAIL PROTECTED]>wrote: > Exactly, which kinda defeats the point I feel. > > I've got a few ways that I might try but for now I'm back to writing SPs. > > If anyone's interested, I have the full DAO code here: > > http://adrianlynch.co.uk/post.cfm?postID=21 > > Adrian > Building a database of ColdFusion errors at http://cferror.org/ > > -Original Message- > From: [EMAIL PROTECTED] > Sent: 30 October 2008 18:28 > To: cf-talk > Subject: RE: cfqueryparam vs cfstoredproc? > > > exec() > or sp_executesql > > You would need to pass in the arguments as a list to the procedure and > then do the looping and building of a dynamic query with SQL. Then > execute what you have created. > > Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you > will have to take additional steps to paramaterize it. (requires > sp_executesql) > > FYI: My advice assumes MS SQL. > > ~Brad > > Original Message > Subject: RE: cfqueryparam vs cfstoredproc? > From: "Adrian Lynch" <[EMAIL PROTECTED]> > Date: Thu, October 30, 2008 1:06 pm > To: cf-talk > > An open question then... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314632 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
erf ... I meant "Why do you feel it would defeat the point?" On Thu, Oct 30, 2008 at 2:51 PM, Aaron Rouse <[EMAIL PROTECTED]> wrote: > I do you feel it would defeat the point? > > > On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch <[EMAIL PROTECTED]>wrote: > >> Exactly, which kinda defeats the point I feel. >> >> I've got a few ways that I might try but for now I'm back to writing SPs. >> >> If anyone's interested, I have the full DAO code here: >> >> http://adrianlynch.co.uk/post.cfm?postID=21 >> >> Adrian >> Building a database of ColdFusion errors at http://cferror.org/ >> >> -Original Message- >> From: [EMAIL PROTECTED] >> Sent: 30 October 2008 18:28 >> To: cf-talk >> Subject: RE: cfqueryparam vs cfstoredproc? >> >> >> exec() >> or sp_executesql >> >> You would need to pass in the arguments as a list to the procedure and >> then do the looping and building of a dynamic query with SQL. Then >> execute what you have created. >> >> Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you >> will have to take additional steps to paramaterize it. (requires >> sp_executesql) >> >> FYI: My advice assumes MS SQL. >> >> ~Brad >> >> Original Message >> Subject: RE: cfqueryparam vs cfstoredproc? >> From: "Adrian Lynch" <[EMAIL PROTECTED]> >> Date: Thu, October 30, 2008 1:06 pm >> To: cf-talk >> >> An open question then... >> >> ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314631 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
I do you feel it would defeat the point? On Thu, Oct 30, 2008 at 2:19 PM, Adrian Lynch <[EMAIL PROTECTED]>wrote: > Exactly, which kinda defeats the point I feel. > > I've got a few ways that I might try but for now I'm back to writing SPs. > > If anyone's interested, I have the full DAO code here: > > http://adrianlynch.co.uk/post.cfm?postID=21 > > Adrian > Building a database of ColdFusion errors at http://cferror.org/ > > -Original Message- > From: [EMAIL PROTECTED] > Sent: 30 October 2008 18:28 > To: cf-talk > Subject: RE: cfqueryparam vs cfstoredproc? > > > exec() > or sp_executesql > > You would need to pass in the arguments as a list to the procedure and > then do the looping and building of a dynamic query with SQL. Then > execute what you have created. > > Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you > will have to take additional steps to paramaterize it. (requires > sp_executesql) > > FYI: My advice assumes MS SQL. > > ~Brad > > Original Message > Subject: RE: cfqueryparam vs cfstoredproc? > From: "Adrian Lynch" <[EMAIL PROTECTED]> > Date: Thu, October 30, 2008 1:06 pm > To: cf-talk > > An open question then... > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314630 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam vs cfstoredproc?
Exactly, which kinda defeats the point I feel. I've got a few ways that I might try but for now I'm back to writing SPs. If anyone's interested, I have the full DAO code here: http://adrianlynch.co.uk/post.cfm?postID=21 Adrian Building a database of ColdFusion errors at http://cferror.org/ -Original Message- From: [EMAIL PROTECTED] Sent: 30 October 2008 18:28 To: cf-talk Subject: RE: cfqueryparam vs cfstoredproc? exec() or sp_executesql You would need to pass in the arguments as a list to the procedure and then do the looping and building of a dynamic query with SQL. Then execute what you have created. Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you will have to take additional steps to paramaterize it. (requires sp_executesql) FYI: My advice assumes MS SQL. ~Brad Original Message ---- Subject: RE: cfqueryparam vs cfstoredproc? From: "Adrian Lynch" <[EMAIL PROTECTED]> Date: Thu, October 30, 2008 1:06 pm To: cf-talk An open question then... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314629 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
exec() or sp_executesql You would need to pass in the arguments as a list to the procedure and then do the looping and building of a dynamic query with SQL. Then execute what you have created. Good luck. Dynamic SQL isn't nearly as easy in SQL than CF. Also, you will have to take additional steps to paramaterize it. (requires sp_executesql) FYI: My advice assumes MS SQL. ~Brad Original Message Subject: RE: cfqueryparam vs cfstoredproc? From: "Adrian Lynch" <[EMAIL PROTECTED]> Date: Thu, October 30, 2008 1:06 pm To: cf-talk An open question then... ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314623 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam vs cfstoredproc?
An open question then... I have a function that takes optional arguments for each of the columns in a table like this: INSERT INTO [comment] ( #separator# [column1] #separator# [column2] ) VALUES ( #column1# #separator# ) SELECT SCOPE_IDENTITY() [commentID] This allows me to do an insert with any combination of columns using named arguments: I have similar ones for updating, selecting and deleting. So, how best to replicate this using stored procedures? The place I'm working at the moment won't allow cfquery so I can't use my beautiful code generator :O( Adrian Building a database of ColdFusion errors at http://cferror.org/ -Original Message- From: Craigsell Sent: 30 October 2008 17:45 To: cf-talk Subject: Re: cfqueryparam vs cfstoredproc? My 2 cents I use ORACLE stored procs exclusively (using a CFSTOREDPROC tag) and have found them to be great. There are things I can do in stored procs that would be difficult to do in a CFC. I can easily have multiple datasets returned in one call. And the CFPROCPARAM gives me the same benefits as CFQUERYPARAM. I'm a big believer in doing database things on the database and display stuff in the web server. I'll confess though that I don't use CF much anymore except for CFCs-- most everything I do is in Flex. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314622 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
My 2 cents I use ORACLE stored procs exclusively (using a CFSTOREDPROC tag) and have found them to be great. There are things I can do in stored procs that would be difficult to do in a CFC. I can easily have multiple datasets returned in one call. And the CFPROCPARAM gives me the same benefits as CFQUERYPARAM. I'm a big believer in doing database things on the database and display stuff in the web server. I'll confess though that I don't use CF much anymore except for CFCs-- most everything I do is in Flex. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314621 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
Something of note... If you are using cfqueryparam all of the variables need to be bound. Leaving any variable "hanging out there" will not allow you to take advantage of the execution plan - even if it's a constant. This query SELECT col1,col2 FROMusers WHERE active = 1 AND userName = Will not pop the exec plan cache because the server will need to evaluate the "1" after active to type it as an int. To make it work it would need to be written as: SELECT col1,col2 FROMusers WHERE active = AND userName = Of course the top query is quite safe from injection.. It just has no chance of hitting the cache. In addition, server configuration issues on the SQL server will determine how effective it is at hitting the cache. By default it does a pretty good job, but it can need adjusting... Say when there are a few hundred databases for example. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2008 11:28 AM To: cf-talk Subject: Re: cfqueryparam vs cfstoredproc? Interesting... I thought the same thing until I ran these tests. I analyzed the results with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion, and watched them execute through SQL Profiler, all of them showed better execution times when I removed the CFQUERYPARAMs. Now, this was on a limited subset of my queries. I would still argue that using it is far better than not. Most of the Queries I have used it in I did see a performance improvement in. =] ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314620 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam vs cfstoredproc?
Interesting, indeed. Wonder if there's an issue of table scan vs index and how the initial execution plans are getting cached. Definitely something to keep your eye on! >Interesting... >I thought the same thing until I ran these tests. I analyzed the results >with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion, >and watched them execute through SQL Profiler, all of them showed better >execution times when I removed the CFQUERYPARAMs. Now, this was on a limited >subset of my queries. I would still argue that using it is far better than >not. Most of the Queries I have used it in I did see a performance >improvement in. > >=] ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314618 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
Interesting... I thought the same thing until I ran these tests. I analyzed the results with ColdFusion debugging output, the Server Monitor in CF8 Ent, SeeFusion, and watched them execute through SQL Profiler, all of them showed better execution times when I removed the CFQUERYPARAMs. Now, this was on a limited subset of my queries. I would still argue that using it is far better than not. Most of the Queries I have used it in I did see a performance improvement in. =] On Thu, Oct 30, 2008 at 9:18 AM, Jason Fisher <[EMAIL PROTECTED]> wrote: > Alan, > > SQL Server will create an execution plan for each query that gets run, > where the plan is specific to the final Query definition. From SQL Server's > perspective, these are 2 different queries, so each gets its own plan: > > Query with both names: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = @param1 > >AND Fname = @param2 > >AND Lname = @param3 > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = @param1 > >AND Lname = @param2 > > So, every query I run with only a Last Name filter will re-use that 2nd > query plan, and that increases performance. Without using CFQUERYPARAM at > all, every instance of the query is 'new' and that should (in theory) kill > your performance, not boost it. In other words, the following queries would > have the same plan with params but are each 'new' and distinct without > params: > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = '1234' > >AND Lname = 'Smith' > > Query with last name only: > >SELECT ID, FName, LName, Email > >FROM SomeTable > >WHERE > >IsActive = 1 > >AND > >ClientCode = '1234' > >AND Lname = 'Johnson' > > I'd be curious to see what the Analyzer had to say on the DB server side > about the query plans for running several hundred unique queries vs several > hundred recurrences of a few plans. > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314616 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
Alan, SQL Server will create an execution plan for each query that gets run, where the plan is specific to the final Query definition. From SQL Server's perspective, these are 2 different queries, so each gets its own plan: Query with both names: >SELECT ID, FName, LName, Email >FROM SomeTable >WHERE >IsActive = 1 >AND >ClientCode = @param1 >AND Fname = @param2 >AND Lname = @param3 Query with last name only: >SELECT ID, FName, LName, Email >FROM SomeTable >WHERE >IsActive = 1 >AND >ClientCode = @param1 >AND Lname = @param2 So, every query I run with only a Last Name filter will re-use that 2nd query plan, and that increases performance. Without using CFQUERYPARAM at all, every instance of the query is 'new' and that should (in theory) kill your performance, not boost it. In other words, the following queries would have the same plan with params but are each 'new' and distinct without params: Query with last name only: >SELECT ID, FName, LName, Email >FROM SomeTable >WHERE >IsActive = 1 >AND >ClientCode = '1234' >AND Lname = 'Smith' Query with last name only: >SELECT ID, FName, LName, Email >FROM SomeTable >WHERE >IsActive = 1 >AND >ClientCode = '1234' >AND Lname = 'Johnson' I'd be curious to see what the Analyzer had to say on the DB server side about the query plans for running several hundred unique queries vs several hundred recurrences of a few plans. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314615 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
Let's hope you don't ever need to handle more than one result set. :)
Also, that requires you get the return code manually as well.
~Brad
Original Message
Subject: Re: cfqueryparam vs cfstoredproc?
From: "morgan l" <[EMAIL PROTECTED]>
We call stored procedures using cfqueryparam:
EXEC StoredProcName
@ParamName =
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314614
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfqueryparam vs cfstoredproc?
Original Message Subject: cfqueryparam vs cfstoredproc? From: "Marie Taylore" <[EMAIL PROTECTED]> > I realize with stored procedures you have a lot more power in terms of SQL > scripting, This is not really true. You can put anything you want in a cfquery block. temp tables, CTE's, sp_commandshell, you name it. Procedures to not implicitly allow for any additional functionality other than the possibility of being called easily from other parts of your database. > but for basic queries, is CFQUERYPARAM just as fast as (or faster > than) running CFSTOREDPROC? There are no significant performance differences between running the same piece of sql as a paramaterized cfquery, or as a stored procedure. The biggest difference, is the amount of text that gets sent over the wire to the SQL server. "select * from ..." vs "execute sp_etc" > Stored Procedures - can contain advanced SQL & procedural code. Like above, your stored proc can't do anything your inline query can't > Encapsulate code outside of you application for a layer of abstraction. Now, you're talking. This is, in my opinion, one of the most useful features of stored procs. This is most readily apparent if your app does not use some form of data abstraction layer like DAOs. Additionally, if you have business logic in your SQL, (which is common though I recommend against it) placing that logic in a proc would make it possible for another process (Java, .NET, etc.) to reuse it at the database level. I have worked on applications where the basic API was comprised of hundreds of stored procedures full of business logic. > CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries, > providing speedier and more secured code. You can "encapsulate" much the > same way a stored procedure does with CFCs. Speedier than an ad-hoc query perhaps. Watch out for blanket statements about paramaterized performance though: http://www.codersrevolution.com/index.cfm/2008/7/26/cfqueryparam-its-not-just-for-security-also-when-NOT-to-use-it Wrapping data access in a CFC is my preferred method of abstracting and reusing SQL in my application, however that is really a CFC vs Proc thing and not a cfqueryparam vs proc conversation. > Would love to hear from others on the advantages/disadvantages of each I like stored procs for reusability and organization (SQL code is easier to read and edit in a SQL IDE as opposed to a CF IDE). I however, do not use them for security nor performance. ~Brad ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314612 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfqueryparam vs cfstoredproc?
Marie, In my experience with SQL Server there is zero notable performance difference between well-formed SQL in a stored proc and the same well-formed SQL in a CFQUERY with CFQUERYPARAM: both gain from the built-in performance tuning of the data server. Also, note that you can run nearly any code directly between the CFQUERY tags that you could put in a stored proc. Yes, you can do multi-statement (just end each with ; like normal) and you can do cursors and you can declare database vars, all within a query in CFQUERY. As to which is better, in my opinion that depends primarily on who's responsible for writing the database calls. As a developer who also writes and optimizes the DB calls, I love having all my code in one searchable library = my CFML pages. If I need to change a data structure, then all changes, both code and DB side, can be found in a single codebase search and changed all at once and checked into one version control system (my CFM repository). On the other hand, if you have a separate person / group doing the DB work, it probably makes more sense to let them live in the stored proc world, especially if they're more comfortable there. HTH ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314610 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
The only issue I have run into with CFQUERYPARAM is that is can degrade performance on dynamic queries. This is an inherent issue in what CFQUERYPARAM does, it essentially makes your queries into stored procs, if you actually watch the traffic flow through a MS SQL Server for example, you will see your app is actually calling what appear to be stored procs. The performance issue manifest when your query has dynamic bits, such as the following example: SELECT ID, FName, LName, Email FROM SomeTable WHERE IsActive = 1 AND ClientCode = AND Fname = AND Lname = The first time CF processes this query, it creates some sort of memory based or temporary stored proc for it based on the structure of the query as it was run in this instance. It builds up an image of the query based on the CFQUERYPARAMS used. Now if the next time the query is executed one or more of the IF statements has a different result, thus including or excluding one or more, then CF has to recompile the temporary stored proc. This can cause a small performance loss. In some performance tuning I did on an app earlier this year, I had 1 query that was executed several thousand times in a long looping process. Nearly every time it was hit it was different and forced a recompile. I removed all of the CFQUERYPARAMs from it and it's average execution time went from 350ms to 10ms. Obviously, if this is a query that only executes occasionally, the difference between 350ms and 10 ms is nothing to worry about, but over millions of executions a day, it adds up. =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314609 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfqueryparam vs cfstoredproc?
We call stored procedures using cfqueryparam: EXEC StoredProcName @ParamName = On Thu, Oct 30, 2008 at 10:49 AM, Marie Taylore <[EMAIL PROTECTED]>wrote: > Question... the more I read about CFQUERYPARAM the more it seems it > mitigates many of the problems that using stored procedures also solves. I > realize with stored procedures you have a lot more power in terms of SQL > scripting, but for basic queries, is CFQUERYPARAM just as fast as (or > faster > than) running CFSTOREDPROC? > > For a CFSTOREDPROC vs CFQUERYPARAM "debate" what would be the "better > thans" > on each side of the argument? > > A few I can think of off the top of my head would be: > > Stored Procedures - can contain advanced SQL & procedural code. > Encapsulate > code outside of you application for a layer of abstraction. > > CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries, > providing speedier and more secured code. You can "encapsulate" much the > same way a stored procedure does with CFCs. > > Would love to hear from others on the advantages/disadvantages of each > > Thanks! > > Marie > > keywords for searching: cfprocparam vs cfqueryparam, cfqueryparam vs > cfprocparam, cfprocresult, cfquery, cftransaction, cfupdate, cfinsert > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314608 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfqueryparam vs cfstoredproc?
SQL in your CF code can be made dynamic more easily. The SP equivalent involves a SQL string and EXEC(). Not pretty. Adrian Building a database of ColdFusion errors at http://cferror.org/ -Original Message- From: Marie Taylore Sent: 30 October 2008 15:49 To: cf-talk Subject: cfqueryparam vs cfstoredproc? Question... the more I read about CFQUERYPARAM the more it seems it mitigates many of the problems that using stored procedures also solves. I realize with stored procedures you have a lot more power in terms of SQL scripting, but for basic queries, is CFQUERYPARAM just as fast as (or faster than) running CFSTOREDPROC? For a CFSTOREDPROC vs CFQUERYPARAM "debate" what would be the "better thans" on each side of the argument? A few I can think of off the top of my head would be: Stored Procedures - can contain advanced SQL & procedural code. Encapsulate code outside of you application for a layer of abstraction. CFQUERYPARAM - allows non-DBAs to take advantage of pre-compiled queries, providing speedier and more secured code. You can "encapsulate" much the same way a stored procedure does with CFCs. Would love to hear from others on the advantages/disadvantages of each Thanks! Marie keywords for searching: cfprocparam vs cfqueryparam, cfqueryparam vs cfprocparam, cfprocresult, cfquery, cftransaction, cfupdate, cfinsert ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314607 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
