Dov,
You bring up a lot of good points.
Here is a trimmed down example of one of my queries in a method:
<cfquery name="local.ReturnStruct.Query">
SELECT DISTINCT MedicationID, Medication, MedicationC
<cfif not arguments.DistinctMedication>
, BrandNameID, BrandNameManufacturer, BrandName
</cfif>
FROM (long from statement)
WHERE 1=1
<cfif isDefined('arguments.MedicationID')>
AND MedicationID in (<cfqueryparam value="#arguments.MedicationID#" >)
</cfif>
<cfloop index="local.Keyword" list="#arguments.Keywords#" delimiters=" ">
AND (Medication like '%#local.Keyword#%'
OR BrandNameManufacturer like '%#local.Keyword#%'
OR BrandName like '%#local.Keyword#%')
</cfloop>
<cfif arguments.DistinctMedication>
ORDER BY Medication
<cfelse>
<cfif len(arguments.OrderBy)>
ORDER BY #arguments.OrderBy#
</cfif>
</cfif>
LIMIT #local.ReturnStruct.StartRow-1#,#local.ReturnStruct.MaxRows#
</cfquery>
Now keep in mind that the real query is much longer with more IF's and
arguments. But in this case, for example, I could break up the method so
that it only takes a MedicationID and make another method to search the
keywords. But then I would duplicate 90% of the query...
Thoughts?
Baz
-----Original Message-----
From: Dov Katz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 6:39 AM
To: CF-Talk
Subject: Re: Big Gateways
I'd say there's one issue with your logic.... If all you mean by conditional
processing is a bunch of extra IF's and CFSWITCH's etc, then the speed
impact of those compared to database queries should be negligible.
For example, if you have 10 different paths through your big cfc method,
based on your arguments, but each path is as tight as it can be (no
redundant queries, file access etc... only what you need for that path, etc)
then you wouldnt be adding any speed by breaking it up.
That said, however, the larger and more complex your single method becomes,
the less maintainable it will be, and more difficult to debug. In fact, my
last resort for long debugging sessions in cases like this IS to break a
large method up.
So, the answer is, no it probably won't speed things up if there are no
steps you can eliminate (plain IFs and Switches are not speed killers
compared to extra redundant query or file I/O), but YES, you should split
things up in certain places...
Example: let's say you have something like this:
function bigMethod(arg1, arg2, .... argn){
set sql="";
if (this or that) sql+="something";
else { more stuff }
switch (arg3){
case a:
sql+= something else...
...
...
}
if (arg4){ return QUERY x with sql string}
if (arg5) Return query y with sql string...
}
To break things up, you should think about 2 things. The methods you are
offerring consumers of your CFC , as well as private utility methods, etc.
First break up internal processing into private methods a little bit. This
will help you create building blocks. For example....
If you always need to format your query results, make a private method
called formatResults(Qry).
If you always need to build dynamic sql (Even if the FROM tables might
differ) create buildDynamicColumnList(arg-list) and
buildDynamicCriteria(arg-list)
If you need to add session/application/permissions, create a
addPermissions(userid) to return custom SQL to append to any query. (Ex:
and UserID=#session.userid#)
If you always need to dynamically select tables, make a selectTables(args)
function
Then your complicated function will look like this:
bigFunction (args) {
set sql=" SELECT " & buildDynamicQuery(args) & " FROM "&
selectTables(args)
& " WHERE " & buildDynamicCriteria(args) & " AND "&
addPermissions(session.userid);
<CFQUERY name="foo"> #sql...#</cfquery>
return #formatResults(foo)#
}
This is just an example off the top of my head. Something along these lines
will make the internal code easier to follow, easy to maintain, and also
allow you to have multiple smaller public methods which dont have code that
needs lots of changes when a database table is renamed, etc.
Hope this makes some sense (Too early for what I wrote to make complete
sense, I'm sure)
-Dov
>Hi,
>
>I have a gateway CFC that returns queries for medications. Medications are
>complex things that have many properties and require many tables to store
>all the info. As well, there are hundreds of different ways to query the
>meds.
>
>My question is how do people break up their methods in these cases?
>
>In one extreme you can have 1 method that accepts many arguments, does some
>conditional processing and returns a query based on your arguments. In the
>other extreme you can have hundreds of methods that take no arguments and
>return a specific query for your specific situation. The advantage of the
>former case is that it's easy to maintain and make updates since there is
>only one query. But because of all the conditional processing, every query
>will run slower. In the latter case, your queries run very fast and serve
>one simple, logical purpose, but you have hundreds to write and maintain
>with a lot of code duplication. Like if I needed to change the FROM clause,
>I'd have to do it for every single one.
>
>Does anyone have a good rule of thumb of how to break up their queries?
>
>Cheers,
>Baz
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229012
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
