I'm wondering if someone can help me make these stored procedures more efficient. What is happening now is it's taking over a minute to process and spiking out the cpu on the performance monitor....
<CFPARAM NAME="GRADE" DEFAULT=""> <CFPARAM NAME="CLINK" DEFAULT=""> <!---Parameters can be either Grade or by teacher's class. The StudentID is returned to retrieve grades for individual students.---> <CFSTOREDPROC PROCEDURE="proGetStudentsQAll" DATASOURCE="#Request.maindsn#"> <CFPROCPARAM TYPE="IN" DBVARNAME="@SCHOOLNUM" CFSQLTYPE="CF_SQL_VarCHAR" VALUE="#CLIENT.SCHOOLNUM#"> <CFPROCPARAM TYPE="IN" DBVARNAME="@GRADE" CFSQLTYPE="CF_SQL_VarChar" VALUE="#GRADE#"> <CFPROCPARAM TYPE="IN" DBVARNAME="@CLINK" CFSQLTYPE="CF_SQL_INTEGER" VALUE="#CLINK#"> <CFPROCPARAM TYPE="OUT" DBVARNAME="@StudentID" CFSQLTYPE="CF_SQL_VarChar" VARIABLE="StudentID"> <CFPROCRESULT NAME="StudentPrint"> </CFSTOREDPROC> <!---Puts out student demographic info retrieved from 1st stored procedure---> <CFOUTPUT QUERY="StudentPrint" GROUP="StudentID"> #FIRSTNAME#, #LASTNAME#, #SCHOOLNAME#, #TotAbs#, #TotTardy#, #TeacherName#<BR> <!---Send StudentID to stored procedure to retrieve grades---> <CFSTOREDPROC PROCEDURE="proGetGradesQAll" DATASOURCE="#Request.maindsn#"> <CFPROCPARAM TYPE="IN" DBVARNAME="@StudentID" CFSQLTYPE="CF_SQL_VarChar" VALUE="#StudentID#"> <CFPROCRESULT NAME="Quarter1Print" RESULTSET="1"> <CFPROCRESULT NAME="Quarter2Print" RESULTSET="2"> <CFPROCRESULT NAME="Quarter3Print" RESULTSET="3"> <CFPROCRESULT NAME="Quarter4Print" RESULTSET="4"> </CFSTOREDPROC> <!---Show grades for 1st quarter---> <CFLOOP QUERY="Quarter1Print"> #CourseID# #QuarterGrade#<BR> </CFLOOP> <!---Show grades for 2nd quarter---> <CFLOOP QUERY="Quarter2Print"> #CourseID# #QuarterGrade#<BR> </CFLOOP> <!---Show grades for 3rd quarter---> <CFLOOP QUERY="Quarter3Print"> #CourseID# #QuarterGrade#<BR> </CFLOOP> <!---Show grades for 4th quarter---> <CFLOOP QUERY="Quarter4Print"> #CourseID# #QuarterGrade#<BR> </CFLOOP> </CFOUTPUT> It was suggested that I put it all in one stored procedure. But when I tried that 2 things happened- CPU use went to 100% while it was processing and secondly the grades would not format out correctly- only sending out the 1st student's grades for each student. (ie grades for Ann Ax were on all the report cards, regardless of student name) Any ideas? The only thing I can think is that by repeatedly sending the StudentID to the 2nd stored proc sucks all the resources-makes the spikes. But knowing the problem and how to fix it are two different things. any help would be greatly appreciated. ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
