Chris asked:
Hi Ray. Sorry to bother you, but I can't find the answer I am looking for anywhere. The thing is. I have only just recently got into using CFC's and what with using Fusebox and all the different methods and opinions. It's a bit of a jungle trying to find the right path. My only question however, is, do I have to declare an argument for hardwired values, like the bit value below...(code edited by ray...)
<cfqueryparam value="#Arguments.firstName#" cfsqltype="cf_sql_varchar" maxlength="50" />,
<cfqueryparam value="1" cfsqltype="cf_sql_bit" />
Welcome to the wonderful world of CFCs and no longer having the "one right way" to do things. This is something I've talked about a lot on this blog and it's a common feature amongst other bloggers as well. As you already know, your code works. But your wondering if this is the best way to do it or if it should be an argument. I'm going to give you my opinion along with a few ideas to consider.
First off - the code you have there is absolutely fine. You do not need to make 1 an attribute. However, by itself it may be confusing. Imagine the coder that looks at your query a few years from now. Their first thought may be that something is wrong here. Perhaps the hard coded value was used for debugging and forgotten in production. You could quickly add a comment that says something along the lines of: "This value is hard coded to 1 due to our business requirement that blah blah blah."
Another option would be to consider making it a variable. In the code I edited out above his cfquery tag made use of variables.dsn, a variable that can be used amongst all the methods of the CFC. If any other method needs this particular hard coded value then it would make sense to create a variable for it. This would also allow you to give it a meaningful name, like topScore or globalMaxRequests or somesuch.
You could also (and yeah, I did warn there wasn't one true way, so forgive me for rambling on) consider using an external configuration for this value. There are many ways of doing this - either via a simple XML configuration file or even a datebase of "Site Settings". This doesn't really gain anything, especially if "1" as a value never changes, but it seems like "never" in our field has a much shorter time frame than the real world.
To summarize - I'd just consider two things when using a hard coded value like this:
- Documentation - will it be clear to others why a hard coded value is used?
- Repetition - even if every value is 1, if there is any chance you will have to use it more than once and any slim chance in the future it may change - consider making it a variable you can change in one place.
Hope this helps!
Archived Comments
Also, you could consider setting this as a default value in the database is this is an insert and remove it from the ColdFusion code entirely!
You know - that brings up an interesting point. It's a bit OT, but I'm allowed. ;)
Do folks ever struggle with how much logic to put in the DB? I don't mean SQL - you can see SQL, but stored procs? Or do people just assume that any logic in the DB is something that web app shouldn't be worried about anyway?
I know I truncated his code above - but maybe it was useful to see the hard coded value? Like if the column was 'maxHits', (ok, dumb idea), it may be useful to know that this hard coded value is being applied to the logic.
I don't "struggle" with the question of how much logic to put in the db, though it's still a good question. I just have clear feelings about it.
My opinion is: unless you have a DBA working on your app, don't worry about putting any logic in the database (except for default values). Leave the logic in the application code where your expertise is. That way, the app is de-coupled from the database and it's a bit more portable. For me, the trouble of coupling your app with the database and working with triggers/stored procedures just isn't worth the incremental speed increase it provides.
Personally, I don't think the DB is used enough. I often see developers writing loops or something to build other queries where everything could have been condensed down into a single stored proc. It's always good to share performance loads with the DB. You see these high powered DB Servers sitting there just storing data and it never gets used for any of the data processing. It just seems like a waste to me.
I have heard varying opinions on using <cfqueryparam> on constant values. By making it a parameter, doesn't that affect how SQL Server (and possibly other DBMS's) generates execution plans? What are the impacts to DB performance if you were to remove the <cfqueryparam> on that one field?
I've gone full circle on the "logic in the database" question. At one point I migrated to having tons of logic in the database and it just made the app's overall logic more confusing to me.
I prefer to not put logic in storedprocs unless there is really something meaty going on, which is rare in my apps. Same with select queries. I prefer to keep my queries in the application layer UNLESS it's a really complicated query (many joins or conditions) or a heavily reused query, in which case I will rely on views to benefit from the database's ability to process those faster and correspondingly simplify the query that needs to go in the application. But even then, if it's a big query that's used in just one spot, I'll prefer to keep it in the application code.
Sorry for the rambling there.
I personally prefer putting on DB's shoulders anything that is related to DB (each tool should be used what it has been designed for). It doesn't mean i would move each and every query to stored procedures, but when they are quite complicated (and sometimes just big enough), stored procedures make perfect sense. This will make the application run much better and faster.
As others have mentioned, I will tend to go to stored procedures if I have a particularly complex query. This is usually not a performance consideration, since parameterized queries in ColdFusion are pretty efficient. It's mainly because I have some very nice tools for working with SQL Server (the database I happen to use mostly) that beat the tools in CF Builder. I have one project that I work on that uses stored procedures heavily and so I'm comfortable working with them. Most of my queries just go in cfquery tags though.
Carl,
I was about to comment about the cfqueryparam as well, but you beat me to it...
There is absolutely no benefit to using a cfqueryparam on a constant. There is no risk of SQL injection since the value isn't inputed by a user, and there will be no gain in performance related to the execution plan compilation.
Let me explain the second part.
Basically, SQL server has 3 types of caches. data, proc, and another very small one you don't need to worry about. The data cache stores often accessed data so it can be pooled out of memory instead of doing expensive io. The proc cache stores execution plan so it doesn't have to re-calculate what's the best algorithm to run a given query every single time. The bit that makes all the difference is how SQL server knows that a query has run before. it's really simple really, it goes like this: hash(SQLstatement). So...
1. SELECT t.a from myTable t WHERE t.b = '#somecfvariable#:' for every different variable input, the query text on the DB server side is effectively different, and SQL server see it as a different query, which means it will recalculate an execution plan. That's a waste of cpu and a waste of memory (since several plans for the same query will be cached.)
2. SELECT t.a from myTable t WHERE t.b = <cfqueryparam ... value="#somecfvariable#" />: will translate to "WHERE t.b = ?" on the DB server side, and will result in a similar hash every time, no matter the input.
3. SELECT t.a from myTable t WHERE t.b = 1: well, same as above. 1 doesn't change, neither will the hash, thus the server won't ever have to recompile the plan until it goes out of cache.
Cheers,
Tof
@Tof,
Thanks for the explanation. Thanks for confirming my suspicions.
@Tof, so following a portion of Ray's logic, and your explanation, if we're GOING to variablize a constant, it's better, from a performance standpoint to use the cfqueryparam rather than a simple substitution (column='#somevariable#')?
Recalling back to my CS days, the implication of using a constant value would mean that you have an imperative to drop in a comment so the next person down the line understands why. (Does ANYONE comment anymore besides the "I was here and created this" header?)
@Brian
No. if the constant doesn't ever change (which is the definition of a constant), there's no point using a queryparam, no matter whether it's a variable or not.
@Brian: Yes, people do comment. Hopefully. ;)