John, I never thought of running separate SQL statements VS the inlin. I'm not sure if that would be faster or not. I may mock up a quick test of that.
Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 -----Original Message----- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:36 PM To: CF-Talk Subject: RE: Mambo SQL query help.... Please Not really, unless you did stored procedures or something. I guess you could also use a view for each type of data you want to look up. I'm not really sure what that would do for you performance-wise but it may help organize things some. I think that you're going to take a while because of all the data you're checking against and all of the queries you're doing. I don't really know that there is a way around it besides caching. <cfqueryparam> should improve performance some without having to go to a stored procedure. Out of curiosity, have you tried making each query separate? Maybe that would help by eliminating the subqueries? I'm not a SQL guru so that could be dead wrong, but it may be worth a try. At least that way, you could use CF to cache some of the queries and then if someone changes the search criteria, only the query with new criteria will need to access the database. John -----Original Message----- From: Mark W. Breneman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 4:31 PM To: CF-Talk Subject: RE: Mambo SQL query help.... Please John, I thought about that and also just caching the results, but that will not work in my case. I fear that I did not explain this very well. This is a dynamic query that can get the results for a different years, different ages and different school districts. The user can pick several items from several pull down list in a form. Any other ideas? Thanks. Mark W. Breneman Here is the full CFquery tag: <cfquery name="getData" datasource="#database#"> SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q2R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q3R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND THREE_STEPS = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q3R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND THREE_STEPS = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q3R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q3R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND THREE_STEPS = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q3R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND THREE_STEPS = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q3R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q4R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q4R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q4R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'na' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q4R1NA, (SELECT count(*) FROM CheckListData WHERE review_no = 2 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q4R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q4R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q4R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'na' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q4R2NA <!--- NA ---> <cfloop index="i" list="FOURA_STEPS,FOURB_STEPS,EIGHT_NOTICE,THIRTEEN_STATEMENT,FOURTEEN_I NSTR UCTION,FOURTEEN_SERVICES,FOURTEEN_COMMUNITY,FOURTEEN_DEVELOP,FOURTEEN_LI VING ,FOURTEEN_VOCATION,FIFTEEN_ACTIVITIES,SIXTEEN_PROMOTE,SEVENTEEN_INTERAGE NCY, EIGHTEEN_REVIEWED,NINETEEN_INCLUDE,TWENTYA_FAILURE,TWENTYB_FAILURE">, (SELECT count(*) FROM CheckListData WHERE review_no = 1 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND #i# = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND #i# = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND #i# = 'na' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1NA, (SELECT count(*) FROM CheckListData WHERE review_no = 2 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND #i# = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND #i# = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND #i# = 'na' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2NA </cfloop> <!--- Yes no ---> <cfloop index="i" list="FIVE_PARENTNOTICE,SIX_NOTICE,SEVEN_NOTICE,NINE_NOTICE,TEN_NOTICE,E LEVE N_INCLUDE,TWELVE_STATEMENT">, (SELECT count(*) FROM CheckListData WHERE review_no = 1 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND #i# = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND #i# = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND #i# = 'yes' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND #i# = 'no' <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)#) as tot_Q#i#R2No </cfloop> FROM CheckListData WHERE recordid <> 0 <cfif form.schoolyear is not "all">AND schoolyear = #form.schoolyear#</cfif><cfif form.cesadivision is not "all">AND cesadivision = #form.cesadivision#</cfif> #PreserveSingleQuotes(disabilitySQL)# #PreserveSingleQuotes(ageSQL)# #PreserveSingleQuotes(districtSQL)# </cfquery> Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com 608.270.9770 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185264 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
