RE: Big Gateways
Dov, Great stuff! Gonna implement it.. Cheers, Baz -Original Message- From: Katz, Dov B (IT) [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 10, 2006 9:14 AM To: CF-Talk Subject: RE: Big Gateways OK, so if this is what the query looks like (but larger), its probably pretty efficent, but if you want to break things up, I'd say to do it by grouping ideas together... Group 1: Brand, Manufacturer, and Medication name Group 2: Types of Medical Conditions Group 3: Active Ingredients, Allergy concerns Group 4: Price, Generic Equivalents, or something.. Your private variables/methods could be: Private variable selectStatement="select FROMx join y Everything in the from statement WHERE 1=1" Private function runQuery(String queryString){ #local.SelectStatement# #queryString# return foo; } Private _GetMedsByName(keywords){ . return qryString; } Private _GetMedsByCondition(keywords){ . return qryString; } Private _GetMedsByIngredients(keywords){ . return qryString; } Private _GetMedsByPrice(keywords){ . qryString; } Then you can make public methods like: GetMedsByName(keywords){ return runQuery(_GetMedsByName(keywords); GetMedsByCondition(keywords){ return runQuery(_GetMedsByCondition(keywords); } GetMedsByIngredients(keywords){ return runQuery(_GetMedsByIngredients(keywords); } GetMedsByPrice(keywords){ return runQuery(_GetMedsByIngredients(keywords); } Stuff like this helps you make a rich API for users to query... Then you can make your existing method be: GetMedsByKeywords(keywords){ return runQuery(qryString); } This is all assuming you still want to break it up... No redundancy if you do it this way, since everything reusable is reused. dov -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 10, 2006 8:41 AM To: CF-Talk Subject: RE: Big Gateways Dov, You bring up a lot of good points. Here is a trimmed down example of one of my queries in a method: SELECT DISTINCT MedicationID, Medication, MedicationC , BrandNameID, BrandNameManufacturer, BrandName FROM (long from statement) WHERE 1=1 AND MedicationID in () AND (Medication like '%#local.Keyword#%' OR BrandNameManufacturer like '%#local.Keyword#%' OR BrandName like '%#local.Keyword#%') ORDER BY Medication ORDER BY #arguments.OrderBy# LIMIT #local.ReturnStruct.StartRow-1#,#local.ReturnStruct.MaxRows# 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 nee
RE: Big Gateways
OK, so if this is what the query looks like (but larger), its probably pretty efficent, but if you want to break things up, I'd say to do it by grouping ideas together... Group 1: Brand, Manufacturer, and Medication name Group 2: Types of Medical Conditions Group 3: Active Ingredients, Allergy concerns Group 4: Price, Generic Equivalents, or something.. Your private variables/methods could be: Private variable selectStatement="select FROMx join y Everything in the from statement WHERE 1=1" Private function runQuery(String queryString){ #local.SelectStatement# #queryString# return foo; } Private _GetMedsByName(keywords){ . return qryString; } Private _GetMedsByCondition(keywords){ . return qryString; } Private _GetMedsByIngredients(keywords){ . return qryString; } Private _GetMedsByPrice(keywords){ . qryString; } Then you can make public methods like: GetMedsByName(keywords){ return runQuery(_GetMedsByName(keywords); GetMedsByCondition(keywords){ return runQuery(_GetMedsByCondition(keywords); } GetMedsByIngredients(keywords){ return runQuery(_GetMedsByIngredients(keywords); } GetMedsByPrice(keywords){ return runQuery(_GetMedsByIngredients(keywords); } Stuff like this helps you make a rich API for users to query... Then you can make your existing method be: GetMedsByKeywords(keywords){ return runQuery(qryString); } This is all assuming you still want to break it up... No redundancy if you do it this way, since everything reusable is reused. dov -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 10, 2006 8:41 AM To: CF-Talk Subject: RE: Big Gateways Dov, You bring up a lot of good points. Here is a trimmed down example of one of my queries in a method: SELECT DISTINCT MedicationID, Medication, MedicationC , BrandNameID, BrandNameManufacturer, BrandName FROM (long from statement) WHERE 1=1 AND MedicationID in () AND (Medication like '%#local.Keyword#%' OR BrandNameManufacturer like '%#local.Keyword#%' OR BrandName like '%#local.Keyword#%') ORDER BY Medication ORDER BY #arguments.OrderBy# LIMIT #local.ReturnStruct.StartRow-1#,#local.ReturnStruct.MaxRows# 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(u
RE: Big Gateways
Dov, You bring up a lot of good points. Here is a trimmed down example of one of my queries in a method: SELECT DISTINCT MedicationID, Medication, MedicationC , BrandNameID, BrandNameManufacturer, BrandName FROM (long from statement) WHERE 1=1 AND MedicationID in () AND (Medication like '%#local.Keyword#%' OR BrandNameManufacturer like '%#local.Keyword#%' OR BrandName like '%#local.Keyword#%') ORDER BY Medication ORDER BY #arguments.OrderBy# LIMIT #local.ReturnStruct.StartRow-1#,#local.ReturnStruct.MaxRows# 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); #sql...# 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
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); #sql...# 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:229003 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