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:
We call stored procedures using cfqueryparam:
cfquery datasource=DSN name=SomeQuery
EXEC StoredProcName
@ParamName = cfqueryparam cfsqltype=cf_sql_integer
value=#session.value#
/cfquery
On Thu, Oct 30, 2008 at 10:49 AM, Marie Taylore [EMAIL PROTECTED]wrote:
Question... the more I read about
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
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
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,
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
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
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
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,
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
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
: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
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 cf-talk@houseoffusion.com
An open question
/
-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
:
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
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
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
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
18 matches
Mail list logo
