CF11 CFQUERY Perfomance improvements if in CFC and PROC

Does placing a CFQUERY in a CFC rather than in code bring about much of a performance boost? % improvement?  Does Cached Within still work if placed inside CFC?  I understand stored procedures are more efficient - but in this case it is a complex dynamic query so I dont want to put in Stored Procedure.

So is it now a better practice to put CFQUERY as a CFC?  I have a lot of 15 year old code CF  - so just wondering?

I have CF 11 SQL server 2012
Ian WhiteOwner and FounderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The CFQuery will not run faster in a CFC.   Best bet is to look at indexes and try to speed up the queries rather than move them around.   That will have the biggest impact no doubt.
Agreed.  It is a good practice IMO for organization, maintenance and reuse. However, it does not improve query performance.

In addition to indexes, using cfqueryparam in your queries also helps boost performance by allowing the db to cache the query execution plan. That improves performance because it saves the time/cost of generating a new plan each time your run the query.  

So again, the location of queries really has no impact - but things like indexing, and query parameters do.


stored procedures are more efficient

It depends.  Using cfqueryparam (or bind variables) will give you a lot of the same benefits of a stored procedure - primarily reuse of the execution plan.  But there are other reasons for using stored procs.  Storing all sql in the db makes for easier maintenance IMO.  Especially if you have a lot of complex logic.  Also for apps that have additional security layers, it's easier to handle permissions if all db access is through stored procs. Personally I prefer them, but in fairness - performance when using cfqueryparam is usually comparable due to the use of bind variables.

Does Cached Within still work if placed inside CFC?  

Yes, it does.
Ian WhiteOwner and FounderAuthor Commented:
Ok But what about cfquery in code V's cfquery inside a CFC (component) I understood it was more efficient due to being compliled.  I have to dynamically create query based on parameters entered or not entered by users and other factors so it does not lend itself to stored procedure.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Perhaps you're mixing the concept of CF and SQL compilation?  In terms of CF, all CF code is compiled. So unless you're talking about differences at the byte code level, there is really not too much difference.

For SQL, yes statements are also compiled in a sense.  When you execute a sql statement for the 1st time, the db first examines the sql, then compiles an execution plan for it, which is time consuming. The execution plan is stored in the db cache.  So the next time that sql is executed, the db engine just grabs the plan in cache.  It's faster because you save the compilation time.   But that's all at the database level.  CF's job is just to send sql to the db for handling.  The db engine does the rest.

In terms of stored procs vs cfquery's - plan caching applies to both stored procedures AND queries that use cfqueryparam (or bind variables). Both tend to increase performance by encouraging the db to cache execution plans, which speeds up execution time.

it does not lend itself to stored procedure

It all depends on the SQL.  There are some cases where using cfqueries is a little simpler. However, I've worked with plenty of apps that build reports based on lots of dynamic sql within stored procs.  

However, overall, dynamic sql usually isn't as "performant" as static queries - not matter what you use - simply because it is difficult to effectively cache something that is constantly changing.  Items move in and out of cache too frequently to be effective.  Also depending on how the dynamic sql is written, there is often an increased risk of SQL injection.  But in most cases you can use dynamic SQL within stored procedures, and many db's offer ways to do it safely using bind variables. For example SQL Server offers sp_executeSQL.  

So again, it all depends.  Both procs and cfqueries have advantages.  Usually you can get the same performance gains from using cfqueryparam.  In which case its not worth the time it would take to switch an *existing* app over to stored procedures. Not unless there's some other gain or reason.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian WhiteOwner and FounderAuthor Commented:
Ok but with Cold Fusion I use CachedWithin on highly used user searches - even dynamic ones, so this must have a gain with Cold Fusion caching?  I set a limit to the number of cached queries so they eventually roll off? CF will cache the same query multiple times depending on the search parameters.  So the most common query combination is cached by CF. I hope this is related.
Ian WhiteOwner and FounderAuthor Commented:
I have just read somewhere that CF components CFC's are complied to native java code - so would be more efficient.
Edit:  All CF code is compiled into native java code, so I'm not sure what you mean there... ;-)  If you mean something different than compile into byte code, can you post a link?  Though again, that has nothing to do with database queries.
Ian, note that even if CFC were faster, you are talking a nano second compared to tuning.  If your query takes 2 second to run, and you put it into a CFC, it will take 2 seconds to run or mayb 1.95.  The only way to make a big difference on something that takes that long is tuning, indexes, caching.   Location in code is so negligible.
I've got to take off, but just noticed your previous comment.  If you're talking about "CachedWithin" that's a totally different beast than DB query execution plan caching ...
Ian WhiteOwner and FounderAuthor Commented:
Thanks for your helpful explaination
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.