Skip to main content
Participant
July 29, 2009
Question

cfqueryparam performance for static values?

  • July 29, 2009
  • 1 reply
  • 1112 views

I use cfqueryparam in pretty much all of my queries, but it got me thinking about performance.  I usually have a mixture of dynamic and static values in the queries, but I also tend to wrap everything in a cfqueryparam.  I know it's not necessary, but i am trying to figure out if it actually can hurt performance.

Example:

SELECT *

FROM myTable

WHERE id = <cfqueryparam value="#form.id#" /> AND

             active = <cfqueryparam value="1" />

vs.

SELECT *

FROM myTable

WHERE id = <cfqueryparam value="#form.id#" /> AND

             active = 1

Would putting the static value of "1" inside a cfqueryparam affect performance?  Will the DB cached the execution plan more efficiently if I just hard-code the static values and not use cfqueryparam?

I am using CF8 on MS SQL Server 2008.

Thanks,

Bryan

This topic has been closed for replies.

1 reply

July 29, 2009

You could try turning on debugging in CF and checking the query times for each version you have. It may be a good idea to use the cfqueryparam cfsqltype attribute too. I believe that sets up the data type before CF hands it off to SQL Server and could help save milliseconds.

Participant
July 29, 2009

Thanks.  I will take a look at the query times.  I know that cfqueryparam usually causes a longer delay the first time the query is run, but then should be faster after that.

I always use cfsqltype in my queries, just failed to put it in these examples.  Good point though.

Bryan

Inspiring
July 29, 2009

How do you know that  "cfqueryparam usually causes a longer delay the first time the query is run"?