Maybe make it into some kind of scheduled job that runs every X hours or something and populates a table with the new values rather than doing real-time queries. Then the 2 minutes isn't as big of a deal. It's probably still a good idea to make it a stored procedure and then just execute that however often you want to populate the temporary table with the newest values. Then your code just does a select * from temporaryTable.
John -----Original Message----- From: Mark W. Breneman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 4:02 PM To: CF-Talk Subject: Mambo SQL query help.... Please I just inherited a project that has a very very large SQL count query. Now when I say very large I mean very large. What I have posted here is only 4 blocks of the 35 total blocks of SQL code in this one query. The total query takes about 120 seconds to run and often takes down the CF server. This query is made up of 203 in line sub queries and only returns a single row of values. Currently this query is not a stored procedure it is just a standard cfquery. So my question is where do I get started rewriting this query. This report page is on an administrative website where the traffic is very low. But never the less 2 mins is far too long to wait for a simple report. First off I can see that the yes, No and NA should be converted to a number. The DISTRICT also needs to be converted to a number. Then the whole thing needs to be converted into a stored procedure. Is there an EZ way to write this as a stored procedure. Currently the query is made by a Cfloop list that changes the query based on what options the users pick. What do I do next? Is there an EZer way to get this data then in line queries? What can I do first to get the biggest bang for my $. IOW is there something I can do quickly to get 40% shorter query run time? THANKS! Here is a small sample of the Query: SELECT count(*) as totalRecords, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q1R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q1R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND ONE_INVITED = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q1R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q1R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q1R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND ONE_INVITED = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q1R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q2R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q2R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND TWO_ATTEND = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q2R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q2R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q2R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND TWO_ATTEND = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q2R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q3R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND THREE_STEPS = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q3R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND THREE_STEPS = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q3R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q3R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND THREE_STEPS = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q3R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND THREE_STEPS = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q3R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q4R1, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q4R1Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q4R1No, (SELECT count(*) FROM CheckListData WHERE review_no = 1 AND FOUR_INVOLVE = 'na' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q4R1NA, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q4R2, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'yes' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q4R2Yes, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'no' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q4R2No, (SELECT count(*) FROM CheckListData WHERE review_no = 2 AND FOUR_INVOLVE = 'na' AND schoolyear = 2003AND cesadivision = 4 AND STUDENT_DISABILITY = 'EBD' AND STUDENT_AGE_AT_IEP<15 AND DISTRICT = 'Black River Falls') as tot_Q4R2NA FROM CheckListData WHERE (recordid <> 0) AND (schoolyear = 2003) AND (cesadivision = 4) AND (STUDENT_DISABILITY = 'EBD') AND (STUDENT_AGE_AT_IEP < 15) AND (DISTRICT = 'Black River Falls') Mark W. Breneman -Cold Fusion Developer -Network Administrator Vivid Media [EMAIL PROTECTED] www.vividmedia.com <http://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:185250 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
