Hmm, I find this hard to swallow and I would question your 'DBA' without
seeing the facts. We run daily extensive tests on speed etc and we have
found that SP's are as fast if not faster than CFQUERY in most cases. We
use SP's not necessarily for the speed increase - mainly for abstraction,
security and the way we can pass back multiple SQL blocks and we can allow
the SQL Server to perform data manipulation over using CF/Inline SQL - which
is not as efficient. For a simple SELECT name from TABLE, I think you are
probably going to see no different and indeed a CFQUERY may even be faster
(head on block).
Based on the comments - I ran a quick test with an SP which basically ran
the following (where iLanguageID and iEventID were @ variables passed in via
dbvarname):
Inline Call:
SELECT tblExAdminTextContent.vcContentName,
tblExAdminTextContentAlias.tContent
FROM tblExAdminTextContentAlias
INNER JOIN tblExAdminTextContent
ON tblExAdminTextContentAlias.iContentID =
tblExAdminTextContent.iContentID
WHERE iEventID = 100306
AND iLanguageID = 1
AND iLinkID IS NULL
Stored Procedure:
risp_ExAdminSiteLanguageDefaults 100306,1
The results were the duration were as following:
Inline SQL:
CPU: 16
Reads: 500 (fluctuates between 422-500)
Writes: 0
Duration: 13ms
Stored Procedure:
CPU: 0
Reads: 422 (fluctuates between 422-500)
Writes: 0
Duration: 0ms
Now, this is by no means a solid call as it can fluctuate between them both
taking 0 duration but never the SP taking any more than that and shows its
more efficient.
Obviously this is a test on a fairly simple proc but there is no way your
DBA can say that it's a performance killer and to be honest with you if he
is an MSCDBA then it's a shocking call to make.
Client vars do perform updates internally using SP's for EVERY CALL if you
do not switch that method off via the Client Vars section - maybe this is
what your DBA was seeing? This does indeed cause 3 SP's to run for every
.CFM thread; turn the updates off if you don't need last visit and hit count
updated constantly.
HTH
Neil
-----Original Message-----
From: Nathan Strutz [mailto:[EMAIL PROTECTED]
Sent: 21 December 2004 20:32
To: CF-Talk
Subject: Re: CFStoredProc bug?
Michael Dinowitz wrote:
> Thank you for pointing that out. It looks like I missed that. So
basically,
> the order of cfprocparams being passed is all that matters and no
parameter
> can be missed when writing cfprocparam tags.
>
> Is there any performance (i.e. binding) savings to using the CFSTOREDPROC
> tag vs. a CFQUERY with CFQUERYPARAMs? Is the CFSTOREDPROC call as
efficient?
> Better? What's the advantage? Anyone from MM want to comment?
> Thanks
>
I've seen some strange behavior with <cfstoredproc>, and our DBA has
requested that we don't use it. If you trace your database (tested on
sql2k), you will see cfstoredproc creates and compiles a procedure,
calls that procedure a number of times (depending on how many recordsets
you are expecting), then destroys the temp proc. For this reason, you
will have degraded performance on <cfstoredproc> than you will calling a
stored procedure from a <cfquery> block.
We've seen the same behavior with client variables, so we don't use them.
It's also my understanding that <cfquery> with <cfqueryparam> values can
be as fast as stored procedures. The benefit, of course, comes when the
SQL statements you are running are large enough to effect bandwidth
between the web and database servers. You'll get some delay when you
have to write 200 lines of SQL to your DB, vs 3 or 4 using a stored
procedure.
-nathan strutz
http://www.dopefly.com/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Silver Sponsor - RUWebby
http://www.ruwebby.com
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188507
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
