just for fun, ran this code:
------------------------start------------------------------
<cfset starttime = Now ()>
<cfloop index="i" from="1" to="1000" step="1">
<cfquery name="TestQuery1" datasource="#sc_datasource#">
Select object_name from all_objects where object_id = #i#
</cfquery>
</cfloop>
<cfset midtime = Now()>
<cfloop index="i" from="1" to="1000" step="1">
<cfquery name="TestQuery2" datasource="#sc_datasource#">
Select object_name from all_objects where object_id =
<cfqueryparam value="#i#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
</cfloop>
<cfset endtime = Now()>
<cfoutput>
starttime: #starttime#<br>
midtime: #midtime#<br>
endtime: #endtime#
</cfoutput>
------------------------end------------------------------
results:
starttime: {ts '2005-02-15 18:52:28'}
midtime: {ts '2005-02-15 18:52:56'}
endtime: {ts '2005-02-15 18:53:05'}
example output from debug:
TestQuery1 (Records=0, Time=27ms)
SQL =
Select object_name from all_objects where object_id = 1
TestQuery1 (Records=0, Time=24ms)
SQL =
Select object_name from all_objects where object_id = 2
TestQuery1 (Records=1, Time=30ms)
SQL =
Select object_name from all_objects where object_id = 3
TestQuery1 (Records=1, Time=26ms)
SQL =
Select object_name from all_objects where object_id = 4
TestQuery1 (Records=1, Time=30ms)
SQL =
Select object_name from all_objects where object_id = 5
etc etc
then
TestQuery2 (Records=0, Time=27ms)
SQL =
Select object_name from all_objects where object_id = :1
Query Parameter Value(s) -
Parameter #1 = 1
TestQuery2 (Records=0, Time=8ms)
SQL =
Select object_name from all_objects where object_id = :1
Query Parameter Value(s) -
Parameter #1 = 2
TestQuery2 (Records=1, Time=9ms)
SQL =
Select object_name from all_objects where object_id = :1
Query Parameter Value(s) -
Parameter #1 = 3
TestQuery2 (Records=1, Time=8ms)
SQL =
Select object_name from all_objects where object_id = :1
Query Parameter Value(s) -
Parameter #1 = 4
TestQuery2 (Records=1, Time=8ms)
SQL =
Select object_name from all_objects where object_id = :1
Query Parameter Value(s) -
Parameter #1 = 5
see how the execute time drops for subsequent iterations of query 2?
btw: running oracle 9.
/t
>-----Original Message-----
>From: Nathan Strutz [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, February 15, 2005 7:50 PM
>To: CF-Talk
>Subject: Re: CFQUERYPARAM in CFC?
>
>You typically won't see any cfqueryparam performance gains on
>low-usage
>queries and servers. In these cases, the reason for using them is
>typically security, standardization and best practices.
>
>Under heavy loads, cfqueryparam (sql bind variables actually) can help
>your database cache queries, helping them not recompile each time.
>Performance like this is usually up there with stored procedures, as
>they pretty much boil down to the same things.
>
>Of course there are still those arguments about are ad-hoc sql
>statements faster than stored procs, etc. Basically, stored procedures
>are about equal to ad-hocs using bind vars. Plain-jane ad-hoc will
>reduce your performance and scalability under heavy loads.
>
>(but i'm not really an expert)
>
>-nathan strutz
>http://www.dopefly.com/
>
>
>
>Michael T. Tangorre wrote:
>>>From: Michael Dinowitz [mailto:[EMAIL PROTECTED]
>>>Personally, I see no reason for a CFQUERYPARAM being used
>>>inside a CFC. It adds extra overhead and the protection that
>>>it provides should be provided instead by the CFARGUMENT tag.
>>>Does anyone see a reason for it in such a case? Data binding?
>>
>>
>> Interesting you bring this up. I have a small forums
>application where I was
>> not using queryparam to start (just to get going.. I always
>go back and add
>> them in). When I went back in and added the tag to all the
>query/procs I saw
>> no difference in terms of performance, what kind of numbers
>are you seeing
>> performance wise in terms of degradation?
>>
>>
>>
>>
>>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194822
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
