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:185247
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
