Professional Web Applications Themes

Help Improve script - Coldfusion - Advanced Techniques

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; cht=iso-8859-1"> <title>Financial Aid Memo</title> <link ...

  1. #1

    Default 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; cht=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

  2. #2

    Default 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
    inson College

    dempster Guest

  3. #3

    Default 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

Similar Threads

  1. How to Improve DownloadS
    By Coldguru in forum Macromedia ColdFusion
    Replies: 8
    Last Post: June 20th, 09:57 PM
  2. PHP Script to improve Google Ranking
    By vuyisile leslie tshuma in forum PHP Development
    Replies: 1
    Last Post: July 6th, 08:50 AM
  3. In what way do I have to improve???
    By XPiRE in forum Macromedia Flash
    Replies: 5
    Last Post: February 6th, 07:34 PM
  4. How to improve SQL
    By Wayne T in forum Informix
    Replies: 7
    Last Post: September 19th, 08:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139