Ask a Question related to Coldfusion - Advanced Techniques, Design and Development.
-
jwdba #1
Help Improve script
I would appreciate any help you cf gurus could provide. I have this complex
report that runs slow (about 1 1/2 - 2 minutes). Do you have any suggestions
of how to improve the script? Any help is greatly appreciated.
Thank you,
James Watson
<cfquery datasource="MyDSN" name="students">
select distinct spriden_pidm pidm, spriden_id id, spriden_first_name fname,
spriden_last_name lname, sfrstcr_term_code term
from sfrstcr, spriden
where sfrstcr_pidm = spriden_pidm
and spriden_change_ind is null
and sfrstcr_term_code = '#url.term#'
and sfrstcr_levl_code = '50'
order by lname
</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Financial Aid Memo</title>
<link href="../reports.css" rel="stylesheet" type="text/css">
</head>
<body>
<p class="pagetitles">Financial Aid Memo</p>
<table width="750" border="0" class="pagetext">
<tr class="pagetext_ul">
<td>ID</td>
<td>Name</td>
<td>Tuit</td>
<td>Fees</td>
<td>Majr1</td>
<td>Majr2</td>
<td>Adj Hrs</td>
<td>EFC</td>
<td>PELL</td>
<td>SEOG</td>
<td>LEAP</td>
<td>GED</td>
</tr>
<cfloop query="students">
<cfset spidm = #students.pidm#>
<cfquery datasource="MyDSN" name="tuition">
select sum(tbraccd_amount) tamt
from tbraccd
where tbraccd_term_code = '#url.term#'
and tbraccd_pidm = #spidm#
and tbraccd_detail_code = 'TUIT'
</cfquery>
<cfquery datasource="MyDSN" name="fees">
select sum(tbraccd_amount) famt
from tbraccd
where tbraccd_term_code = '#url.term#'
and tbraccd_pidm = #spidm#
and tbraccd_detail_code in ('INAC', 'MISR', 'ACTI')
</cfquery>
<cfquery name="majors" datasource="MyDSN">
select nvl(sgbstdn_majr_code_1, '-') majr1, nvl(sgbstdn_majr_code_2, '-')
majr2
from sgbstdn a
where sgbstdn_term_code_eff=
(select max(sgbstdn_term_code_eff)
from sgbstdn b
where b.sgbstdn_pidm = a.sgbstdn_pidm
and b.sgbstdn_term_code_eff <= '#url.term#')
and sgbstdn_pidm = '#spidm#'
</cfquery>
<cfquery name="ahours" datasource="MyDSN">
select rorenrl_finaid_adj_hr ahours
from rorenrl
where rorenrl_pidm = '#spidm#'
and rorenrl_term_code = '#url.term#'
</cfquery>
<cfquery name="efc" datasource="MyDSN">
select rcvappl_pgi efc
from rcvappl
where rcvappl_curr_rec_ind = 'Y'
and rcvappl_pidm = '#spidm#'
and rcvappl_aidy_code = '#url.aidyear#'
</cfquery>
<cfquery name="pell" datasource="MyDSN">
select sum(rprauth_amount) pell
from rprauth
where rprauth_pidm = '#spidm#'
and rprauth_term_code = '#url.term#'
and rprauth_fund_code = 'PELL'
</cfquery>
<cfquery name="seog" datasource="MyDSN">
select sum(rprauth_amount) seog
from rprauth
where rprauth_pidm = '#spidm#'
and rprauth_term_code = '#url.term#'
and rprauth_fund_code = 'FSEOG'
</cfquery>
<cfquery name="leap" datasource="MyDSN">
select sum(rprauth_amount) leap
from rprauth
where rprauth_pidm = '#spidm#'
and rprauth_term_code = '#url.term#'
and rprauth_fund_code = 'LEAP'
</cfquery>
<cfquery name="ged" datasource="MyDSN">
select sum(rprauth_amount) ged
from rprauth
where rprauth_pidm = '#spidm#'
and rprauth_term_code = '#url.term#'
and rprauth_fund_code = 'ZHGED'
</cfquery>
<cfoutput>
<tr>
<td>#students.ID#</td>
<td>#students.LNAME#, #students.FNAME#</td>
<td>#tuition.tamt#</td>
<td>#fees.famt#</td>
<td>#majors.MAJR1#</td>
<td>#majors.MAJR2#</td>
<td>#ahours.AHOURS#</td>
<td>#efc.EFC#</td>
<td>#pell.PELL#</td>
<td>#seog.SEOG#</td>
<td>#leap.LEAP#</td>
<td>#ged.GED#</td>
</tr>
</cfoutput>
</cfloop>
</table>
</body>
</html>
jwdba Guest
-
PHP Script to improve Google Ranking
Hi, I have been trying for ages to get my website to show on Google to no avail. My friend tells me that what matters is the websites that link... -
In what way do I have to improve???
Hi Folks, as you may have noticed, I'm pretty new here.... Hello to all of you..... I 'll do my very best to help where I can (will need a lot of... -
FreeHand MX better improve
hi all freehand lover, with adobe coming out its "creative suite" soon. I think fh users are going to dwindle further. MM is not giving lot of... -
need comment to improve
Hi there, This is my second website made with Flash..,, Any suggestions would help my design in the future.... Feel free to make your comment...... -
How to improve SQL
Hi All, Can you explain me how to improve this sql statement. The "top" command showed this SQL program took about 50% CPU resource. When it run... -
dempster #2 Re: Help Improve script
This probably takes so long because you do 9 more queries for each record found
in the initial query. So if you listed 100 students, you'd be doing 900 queries
to your data source in order to construct your report.
The problem may be in your database structure. Is there any way to consolidate
some of the tables and then change your query using SQL JOINS? Eliminating the
multiple queries would help speed it up.
-Paul
Paul Dempsey
Dickinson College
dempster Guest
-
jorgepino #3 Re: Help Improve script
if you have a big database You may also use stored procedures to speed up the
process
Specially if you sql server is in a separated Machine.
I also agree with Paul, you should check the strature of the database and use
Database JOINS
jorgepino Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
