Extending Allen's proposal to add reuse of the query data, as Steven was heading toward in his note, you may want to recognize something if you're calling these methods often, and perhaps from lots of different users of the application. You may want to consider some form of query caching.
There are a couple of ways to do it, built into CFQUERY, in its CachedWithin and CachedAfter attributes. Check out the docs for those. What they do is say that for a set amount of time (or as of a time), the results of a first execution of the given SQL statement (with any passed in variable value) will be stored in memory and reused on subsequent requests. It can make a huge performance difference, depending on various things. It's worth noting as well that one doesn't ALWAYS want to use caching. If you have a large amount of data that would come back from a query, that could be more burdensome on the memory of CF than is worth the effort. I should add, too, that sadly in CF as soon as you add CFQUERYPARAM to a CFQUERY, you can't use those caching attributes. It's a real bummer (which BlueDragon, an alternative CFML engine, has resolved). Perhaps we can hope CF will in the future. Still, the security (and database server) benefits of CFQUERYPARAM may trump the loss of performance from query caching, so don't pull its use just to get caching. These are not always obvious choices. In fact, if you're running your CFQUERY inside of a CFC (as here), since you could use CFARGUMENT to provide the protection against "bad data", so you might well consider removing the CFQUERYPARAM. In that case, since your caching would reduce the number of real queries to the database, the database server plan caching benefit wouldn't be as important. This all may be too much for you to take in now, Anthony, but it seems an opportune moment to throw the idea out for you or others. Finally, still another possibility for caching could come in recognition of the fact if you will frequently call this CFC in page after page (and for user after user), you could go a step farther and consider storing the CFC instance in a shared variable scope (like session, application, or server). That really IS too much for you to worry about as you get started, but it's something to keep in mind should performance suffer in your change to using a CFC. It's certainly smart (for many reasons) to go to encapsulating your queries into a reusable component (whether just a function you CFINCLUDE, or a function in a CFC, which is generally preferred). It's just that there are really a lot of little avenues down which that new functionality can go. Some will be more interesting and beneficial than others. Seems you're better off being told up front before someone hits you over the head for something you just didn't know about. Don't let it overwhelm you. Keep up the faith, and good luck as you enter the world of CFCs. /Charlie http://www.carehart.org/blog/ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, February 02, 2007 1:44 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] CFC and reuse query Before anyone attacks you on this...you shouldn't do something like this: <cfquery> select blah from blah where #col# = #val#</cfquery> - big security risk...plus, using cfqueryparam (in most cases) will help a database store the query plan. But, you could do something like this: <cffunction name="GetOSData" returntype="query" output="false"> <cfargument name="osName" required="true" type="string" /> <cfquery datasource="#APPLICATION.dsn#" name="qryOS"> SELECT * FROM stats WHERE os = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.osName#"> </cfquery> <cfreturn qryOS /> </cffunction> Then, on your page you could do something like this: <cfscript> myObject = createObject("component", "yourCFCName"); qryWindows = myObject.GetOSData("windows"); qryLinux = myObject.GetOSData("linux"); //etc.... </cfscript> Hope that helps... Allen -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Anthony Mathis Sent: Friday, February 02, 2007 1:27 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] CFC and reuse query Let me try to explain what I trying to accomplish a little bit better. I'm not sure if cfcs can do this. Documentation seems to make me think that they can. In my test.cfm I have the following: <cfoutput>#qryWINDOWS.recordcount#</cfoutput> <cfoutput>#qryLINUX.recordcount#</cfoutput> I'm using the following query from a cfinclude action.cfm (contains multiple queries). <!---Query for Windows OS---> <cfquery datasource="#APPLICATION.dsn#" name="qryWINDOWS"> SELECT * FROM stats WHERE os = 'WINDOWS' </cfquery> <!---Query for Linux OS---> <cfquery datasource="#APPLICATION.dsn#" name="qryLINUX"> SELECT * FROM stats WHERE os = 'LINUX' </cfquery> How can I accomplish this without using mulitiple queries for LINUX, MAC, UNIX, etc.., using a cfc? I'm new to the cfcs and after reading the documentation, It looks like the above is possible, plus I can cut down on my code. I would assume I could use variable(s) as follows: <cfquery datasource="#APPLICATION.dsn#" name="qryOS"> SELECT * FROM stats WHERE #var1# = #var2# </cfquery> How would I pass #var1# and #var2# from my cfm to the cfc? Any examples would be great. How I display the return recordcount would be great as well. By the way, the output takes place on the same cfm (test.cfm). ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=gin.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
