• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Speeding up code with arrays and loops

Hi All,

I'm having a few troubles with some code which is taking too long to load. I've become so bogged down in the code that perhaps I've missed something obvious which will make the whole thing load faster so I thought I'd post the algorithm here and see if anyone had some ideas on ways to make it faster.

Summary

The code creates a report which outputs in html and is ordered by transaction date (from earliest to latest). The catch is that each client has up to 20 different types of transactions and the report needs to be ordered by ANY of the transaction dates.

EG. Client 1 has 3 transactions: the 15th May, 17th May and the 3rd June. Client 2 has 4 transactions: the 10th May, 14th may, 16th may, 7th June.

The report outputs each of these transactions and how much they were for, in the order of earliest transaction to latest transaction.

I don't believe I can (or at least I don't know how) to use SQL to order the transactions and use a simple cfoutput query= because of the fact that each client has the 20 different types of transactions (eg. GeneralFees1Amount, GeneralFees1Date - GeneralFees2Amount, GeneralFees2Date - GlassFees1Amount, GlassFees1Date - GlassFees2Amount, GlassFees2Date - GlassFees3Amount, GlassFees3Date etc).

Because, of this we aren't ordering by a single field but by all the date fields. The report outputs on a month by month basis (one month at a time)

Detail

To achieve this I do the following:

Do a full query to extract the correct data for a month and year from the database
Do a loop through each day of this month and do a query of the query to get the data for this specific day.
Loop through each of the various transaction dates for each customer on this day and check if they are null or not, if they aren't null then they go into an array which has:

Array[month][day][inner-array-with-data]

So, this way the array does the sorting for me and I get to go through each customer to load the array. The inner array has things like the customer name, transaction amount, notes etc.

Now we have loaded the main array up we use the main array to output the report by looping through each day in the array and then looping through the inner arrays (transactions) stored in that day of the array. Then i grab the information stored in the inner array (which i could convert to a 6 item list if this would speed things up as its just simple text data) and output it on one line after another.

This works but it takes a LONG time because, first the array has to be built then it has to be looped through repeatedly to output the report.

Anyone have some ideas to speed up this report? Am I missing something obvious?

Cheers,
Brad
0
bradderick
Asked:
bradderick
  • 2
  • 2
1 Solution
 
PluckaCommented:
bradderick,

> This works but it takes a LONG time because, first the array has to be
> built then it has to be looped through repeatedly to output the report.

I don't understand why you loop through repeatedly

Regards
Plucka
0
 
bradderickAuthor Commented:
Perhaps it would be useful if i post the code, so thats what I've done:



<cfset TodayOutputDate = DateAdd('H', 17, Now())>

<!--- see if there is a year url variable, if not then default to this year --->
<cfif IsDefined("Form.FormYear")>
      <cfif IsNumeric(Form.FormYear)>
            <cfset GetYear = Form.FormYear>
      <cfelse>
            <!--- redirect straight to new-business.cfm --->
            <cflocation url="report.cfm">
      </cfif>
<cfelse>
      <!--- default to this year --->
      <cfset GetYear = DateFormat(TodayOutputDate, 'YYYY')>
</cfif>

<cfif IsDefined("Form.FormMonth")>
      <cfif IsNumeric(Form.FormMonth)>
            <cfset GetMonth = Form.FormMonth>
      <cfelse>
            <!--- redirect straight to new-business.cfm --->
            <cflocation url="report.cfm">
      </cfif>
<cfelse>
      <!--- default to this month --->
      <cfset GetMonth = DateFormat(TodayOutputDate, 'MM')>
</cfif>

<!--- set the m variable for the array later --->
<cfset m = GetMonth>

<body>
<cfinclude template="inc-menu.cfm">
<br>
<table border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>
            
            <STRONG>Report <cfoutput>as at #DateFormat(TodayOutputDate, 'dd mmmm yyyy')#</cfoutput></STRONG><br><br>
            <!--- what years to output? --->
            <cfset FirstYear = 2004>
            <cfset ThisYear = DateFormat(TodayOutputDate, 'YYYY')>
            <!--- drop down of years --->
            <cfoutput>
            <form style="display:inline; " name="theyear" action="report.cfm" method="post">
                  <select name="FormMonth">
                              <option value="1" <cfif GetMonth EQ 1>SELECTED</cfif>>January</option>
                              <option value="2" <cfif GetMonth EQ 2>SELECTED</cfif>>February</option>
                              <option value="3" <cfif GetMonth EQ 3>SELECTED</cfif>>March</option>
                              <option value="4" <cfif GetMonth EQ 4>SELECTED</cfif>>April</option>
                              <option value="5" <cfif GetMonth EQ 5>SELECTED</cfif>>May</option>
                              <option value="6" <cfif GetMonth EQ 6>SELECTED</cfif>>June</option>
                              <option value="7" <cfif GetMonth EQ 7>SELECTED</cfif>>July</option>
                              <option value="8" <cfif GetMonth EQ 8>SELECTED</cfif>>August</option>
                              <option value="9" <cfif GetMonth EQ 9>SELECTED</cfif>>September</option>
                              <option value="10" <cfif GetMonth EQ 10>SELECTED</cfif>>October</option>
                              <option value="11" <cfif GetMonth EQ 11>SELECTED</cfif>>November</option>
                              <option value="12" <cfif GetMonth EQ 12>SELECTED</cfif>>December</option>
                  </select>
                  <select name="FormYear">
                        <cfloop from="#FirstYear#" to="#ThisYear#" index="i">                  
                              <option value="#i#" <cfif i EQ GetYear>SELECTED</cfif>>#i#</option>
                        </cfloop>
                  </select>
                  <!--- onChange="document.theyear.submit()" --->
                  <input type="submit" value="Submit Form">
            </form>
            </cfoutput>
             - <input type="button" value="Print Page" onClick="window.print()">
      </td>
  </tr>
</table>

<CFQUERY NAME="Qry_ClientDetails" DATASOURCE="#DS#">
      SELECT *, S.FullName AS StaffName,
            month(c.ValuationFeesDate) as ValuationFeesDateMonth,
            month(c.PropertySearchFeesDate) as PropertySearchFeesDateMonth,
            month(c.LegalFeesDate) as LegalFeesDateMonth,
            month(c.CompanySearchFeesDate) as CompanySearchFeesDateMonth,
            month(c.OtherCostsDate) as OtherCostsDateMonth,
            month(c.Fees4Date) as Fees4DateMonth,
            month(c.Fees5Date) as Fees5DateMonth,
            month(c.Fees6Date) as Fees6DateMonth,
            month(c.Fees7Date) as Fees7DateMonth,
            month(c.Fees8Date) as Fees8DateMonth,
            month(c.Fees9Date) as Fees9DateMonth,
            month(c.Fees10Date) as Fees10DateMonth,
            month(c.PartPaymentDate1) as PartPaymentDate1Month,
            month(c.PartPaymentDate2) as PartPaymentDate2Month,
            month(c.PartPaymentDate3) as PartPaymentDate3Month,
            month(c.PartPaymentDate4) as PartPaymentDate4Month,
            month(c.PartPaymentDate5) as PartPaymentDate5Month,
            month(c.PartPaymentDate6) as PartPaymentDate6Month,
            month(c.PartPaymentDate7) as PartPaymentDate7Month,
            month(c.PartPaymentDate8) as PartPaymentDate8Month,
            month(c.PartPaymentDate9) as PartPaymentDate9Month,
            month(c.PartPaymentDate10) as PartPaymentDate10Month,
            month(c.DateOfPrincipalAdvance) as DateOfPrincipalAdvanceMonth,
            day(c.ValuationFeesDate) as ValuationFeesDateDay,
            day(c.PropertySearchFeesDate) as PropertySearchFeesDateDay,
            day(c.LegalFeesDate) as LegalFeesDateDay,
            day(c.CompanySearchFeesDate) as CompanySearchFeesDateDay,
            day(c.OtherCostsDate) as OtherCostsDateDay,
            day(c.Fees4Date) as Fees4DateDay,
            day(c.Fees5Date) as Fees5DateDay,
            day(c.Fees6Date) as Fees6DateDay,
            day(c.Fees7Date) as Fees7DateDay,
            day(c.Fees8Date) as Fees8DateDay,
            day(c.Fees9Date) as Fees9DateDay,
            day(c.Fees10Date) as Fees10DateDay,
            day(c.PartPaymentDate1) as PartPaymentDate1Day,
            day(c.PartPaymentDate2) as PartPaymentDate2Day,
            day(c.PartPaymentDate3) as PartPaymentDate3Day,
            day(c.PartPaymentDate4) as PartPaymentDate4Day,
            day(c.PartPaymentDate5) as PartPaymentDate5Day,
            day(c.PartPaymentDate6) as PartPaymentDate6Day,
            day(c.PartPaymentDate7) as PartPaymentDate7Day,
            day(c.PartPaymentDate8) as PartPaymentDate8Day,
            day(c.PartPaymentDate9) as PartPaymentDate9Day,
            day(c.PartPaymentDate10) as PartPaymentDate10Day,
            day(c.DateOfPrincipalAdvance) as DateOfPrincipalAdvanceDay
      FROM ClientDetails C
            INNER JOIN Staff S
                  On C.StaffID = S.StaffID
      WHERE (year(C.ValuationFeesDate) = #GetYear# AND month(C.ValuationFeesDate) = #m#)
            OR (year(C.PropertySearchFeesDate) = #GetYear#  AND month(C.PropertySearchFeesDate) = #m#)
            OR (year(C.LegalFeesDate) = #GetYear#  AND month(C.LegalFeesDate) = #m#)
            OR (year(C.CompanySearchFeesDate) = #GetYear#  AND month(C.CompanySearchFeesDate) = #m#)
            OR (year(C.OtherCostsDate) = #GetYear#  AND month(C.OtherCostsDate) = #m#)
            OR (year(C.Fees4Date) = #GetYear#  AND month(C.Fees4Date) = #m#)
            OR (year(C.Fees5Date) = #GetYear#  AND month(C.Fees5Date) = #m#)
            OR (year(C.Fees6Date) = #GetYear#  AND month(C.Fees6Date) = #m#)
            OR (year(C.Fees7Date) = #GetYear#  AND month(C.Fees7Date) = #m#)
            OR (year(C.Fees8Date) = #GetYear#  AND month(C.Fees8Date) = #m#)
            OR (year(C.Fees9Date) = #GetYear#  AND month(C.Fees9Date) = #m#)
            OR (year(C.Fees10Date) = #GetYear#  AND month(C.Fees10Date) = #m#)
            OR (year(C.PartPaymentDate1) = #GetYear#  AND month(C.PartPaymentDate1) = #m#)
            OR (year(C.PartPaymentDate2) = #GetYear#  AND month(C.PartPaymentDate2) = #m#)
            OR (year(C.PartPaymentDate3) = #GetYear#  AND month(C.PartPaymentDate3) = #m#)
            OR (year(C.PartPaymentDate4) = #GetYear#  AND month(C.PartPaymentDate4) = #m#)
            OR (year(C.PartPaymentDate5) = #GetYear#  AND month(C.PartPaymentDate5) = #m#)
            OR (year(C.PartPaymentDate6) = #GetYear#  AND month(C.PartPaymentDate6) = #m#)
            OR (year(C.PartPaymentDate7) = #GetYear# AND month(C.PartPaymentDate7) = #m#)
            OR (year(C.PartPaymentDate8) = #GetYear# AND month(C.PartPaymentDate8) = #m#)
            OR (year(C.PartPaymentDate9) = #GetYear# AND month(C.PartPaymentDate9) = #m#)
            OR (year(C.PartPaymentDate10) = #GetYear# AND month(C.PartPaymentDate10) = #m#)
            OR (year(C.DateOfPrincipalAdvance) = #GetYear# AND month(C.DateOfPrincipalAdvance) = #m#)
</CFQUERY>


<cfloop query="Qry_ClientDetails">
      
      
      <!--- create the array --->
      
      <cfset BankArray = #ArrayNew(3)#>

            
      <!--- loop through the months --->
      
             <cfset ThisMonth = createDate(getYear, m, 1)>
             <cfset numOfDaysInThisMonth = daysInMonth(ThisMonth)>
             <cfset MonthName = monthAsString(m)>
            
             <cfquery name="Qry_GetThisMonth" dbtype="query">
                    SELECT *                     
                    FROM Qry_ClientDetails
              </cfquery>
                                                 
             <!--- loop through the days --->       
             <cfloop from="1" to="#numOfDaysInThisMonth#" index="d">
                   
                  <cfquery name="GetThisDay" dbtype="query">
                    SELECT ClientID,
                      FullName,
                      StaffName,
                      ValuationFees,
                        PropertySearchFees,
                        LegalFees,
                        CompanySearchFees,
                        OtherCosts,
                        Fees4Amount,
                        Fees5Amount,
                        Fees6Amount,
                        Fees7Amount,
                        Fees8Amount,
                        Fees9Amount,
                        Fees10Amount,
                        PartPayment1,
                        PartPayment2,
                        PartPayment3,
                        PartPayment4,
                        PartPayment5,
                        PartPayment6,
                        PartPayment7,
                        PartPayment8,
                        PartPayment9,
                        PartPayment10,
                        PrincipalAdvance,
                        ValuationFeesDate,
                        PropertySearchFeesDate,
                        LegalFeesDate,
                        CompanySearchFeesDate,
                        OtherCostsDate,
                        Fees4Date,
                        Fees5Date,
                        Fees6Date,
                        Fees7Date,
                        Fees8Date,
                        Fees9Date,
                        Fees10Date,
                        PartPaymentDate1,
                        PartPaymentDate2,
                        PartPaymentDate3,
                        PartPaymentDate4,
                        PartPaymentDate5,
                        PartPaymentDate6,
                        PartPaymentDate7,
                        PartPaymentDate8,
                        PartPaymentDate9,
                        PartPaymentDate10,
                        DateOfPrincipalAdvance,
                        ValuationFeeNotes,
                        PropertySearchNotes,
                        LegalFee1Notes,
                        LegalFee2Notes,
                        LegalFee3Notes,
                        Fees4Notes,
                        Fees5Notes,
                        Fees6Notes,
                        Fees7Notes,
                        Fees8Notes,
                        Fees9Notes,
                        Fees10Notes,
                        PartPaymentNotes1,
                        PartPaymentNotes2,
                        PartPaymentNotes3,
                        PartPaymentNotes4,
                        PartPaymentNotes5,
                        PartPaymentNotes6,
                        PartPaymentNotes7,
                        PartPaymentNotes8,
                        PartPaymentNotes9,
                        PartPaymentNotes10
                    FROM Qry_GetThisMonth
                    WHERE
                      ValuationFeesDateDay = #d#
                        OR PropertySearchFeesDateDay = #d#
                        OR LegalFeesDateDay = #d#
                        OR CompanySearchFeesDateDay = #d#
                        OR OtherCostsDateDay = #d#
                        OR Fees4DateDay = #d#
                        OR Fees5DateDay = #d#
                        OR Fees6DateDay = #d#
                        OR Fees7DateDay = #d#
                        OR Fees8DateDay = #d#
                        OR Fees9DateDay = #d#
                        OR Fees10DateDay = #d#
                        OR PartPaymentDate1Day = #d#
                        OR PartPaymentDate2Day = #d#
                        OR PartPaymentDate3Day = #d#
                        OR PartPaymentDate4Day = #d#
                        OR PartPaymentDate5Day = #d#
                        OR PartPaymentDate6Day = #d#
                        OR PartPaymentDate7Day = #d#
                        OR PartPaymentDate8Day = #d#
                        OR PartPaymentDate9Day = #d#
                        OR PartPaymentDate10Day = #d#
                        OR DateOfPrincipalAdvanceDay = #d#
                        
              </cfquery>

                  <cfoutput query="GetThisDay">
                        
                        <cfset CheckListNames = "ValuationFees,PropertySearchFees,LegalFees,CompanySearchFees,OtherCosts,Fees4Amount,Fees5Amount,Fees6Amount,Fees7Amount,Fees8Amount,Fees9Amount,Fees10Amount,PartPayment1,PartPayment2,PartPayment3,PartPayment4,PartPayment5,PartPayment6,PartPayment7,PartPayment8,PartPayment9,PartPayment10,PrincipalAdvance">
                        
                        <cfset CheckListAmountsArray = #ArrayNew(1)#>
                        
                        <cfset CheckListAmountsArray[1] = #GetThisDay.ValuationFees#>
                        <cfset CheckListAmountsArray[2] = #GetThisDay.PropertySearchFees#>
                        <cfset CheckListAmountsArray[3] = #GetThisDay.LegalFees#>
                        <cfset CheckListAmountsArray[4] = #GetThisDay.CompanySearchFees#>
                        <cfset CheckListAmountsArray[5] = #GetThisDay.OtherCosts#>
                        <cfset CheckListAmountsArray[6] = #GetThisDay.Fees4Amount#>
                        <cfset CheckListAmountsArray[7] = #GetThisDay.Fees5Amount#>
                        <cfset CheckListAmountsArray[8] = #GetThisDay.Fees6Amount#>
                        <cfset CheckListAmountsArray[9] = #GetThisDay.Fees7Amount#>
                        <cfset CheckListAmountsArray[10] = #GetThisDay.Fees8Amount#>
                        <cfset CheckListAmountsArray[11] = #GetThisDay.Fees9Amount#>
                        <cfset CheckListAmountsArray[12] = #GetThisDay.Fees10Amount#>
                        <cfset CheckListAmountsArray[13] = #GetThisDay.PartPayment1#>
                        <cfset CheckListAmountsArray[14] = #GetThisDay.PartPayment2#>
                        <cfset CheckListAmountsArray[15] = #GetThisDay.PartPayment3#>
                        <cfset CheckListAmountsArray[16] = #GetThisDay.PartPayment4#>
                        <cfset CheckListAmountsArray[17] = #GetThisDay.PartPayment5#>
                        <cfset CheckListAmountsArray[18] = #GetThisDay.PartPayment6#>
                        <cfset CheckListAmountsArray[19] = #GetThisDay.PartPayment7#>
                        <cfset CheckListAmountsArray[20] = #GetThisDay.PartPayment8#>
                        <cfset CheckListAmountsArray[21] = #GetThisDay.PartPayment9#>
                        <cfset CheckListAmountsArray[22] = #GetThisDay.PartPayment10#>
                        <cfset CheckListAmountsArray[23] = #GetThisDay.PrincipalAdvance#>
                        
                        <cfset CheckListDatesArray = #ArrayNew(1)#>
                        
                        <cfset CheckListDatesArray[1] = #GetThisDay.ValuationFeesDate#>
                        <cfset CheckListDatesArray[2] = #GetThisDay.PropertySearchFeesDate#>
                        <cfset CheckListDatesArray[3] = #GetThisDay.LegalFeesDate#>
                        <cfset CheckListDatesArray[4] = #GetThisDay.CompanySearchFeesDate#>
                        <cfset CheckListDatesArray[5] = #GetThisDay.OtherCostsDate#>
                        <cfset CheckListDatesArray[6] = #GetThisDay.Fees4Date#>
                        <cfset CheckListDatesArray[7] = #GetThisDay.Fees5Date#>
                        <cfset CheckListDatesArray[8] = #GetThisDay.Fees6Date#>
                        <cfset CheckListDatesArray[9] = #GetThisDay.Fees7Date#>
                        <cfset CheckListDatesArray[10] = #GetThisDay.Fees8Date#>
                        <cfset CheckListDatesArray[11] = #GetThisDay.Fees9Date#>
                        <cfset CheckListDatesArray[12] = #GetThisDay.Fees10Date#>
                        <cfset CheckListDatesArray[13] = #GetThisDay.PartPaymentDate1#>
                        <cfset CheckListDatesArray[14] = #GetThisDay.PartPaymentDate2#>
                        <cfset CheckListDatesArray[15] = #GetThisDay.PartPaymentDate3#>
                        <cfset CheckListDatesArray[16] = #GetThisDay.PartPaymentDate4#>
                        <cfset CheckListDatesArray[17] = #GetThisDay.PartPaymentDate5#>
                        <cfset CheckListDatesArray[18] = #GetThisDay.PartPaymentDate6#>
                        <cfset CheckListDatesArray[19] = #GetThisDay.PartPaymentDate7#>
                        <cfset CheckListDatesArray[20] = #GetThisDay.PartPaymentDate8#>
                        <cfset CheckListDatesArray[21] = #GetThisDay.PartPaymentDate9#>
                        <cfset CheckListDatesArray[22] = #GetThisDay.PartPaymentDate10#>
                        <cfset CheckListDatesArray[23] = #GetThisDay.DateOfPrincipalAdvance#>
                        
                        <cfset CheckListNotesArray = #ArrayNew(1)#>
                        
                        <cfset CheckListNotesArray[1] = #GetThisDay.ValuationFeeNotes#>
                        <cfset CheckListNotesArray[2] = #GetThisDay.PropertySearchNotes#>
                        <cfset CheckListNotesArray[3] = #GetThisDay.LegalFee1Notes#>
                        <cfset CheckListNotesArray[4] = #GetThisDay.LegalFee2Notes#>
                        <cfset CheckListNotesArray[5] = #GetThisDay.LegalFee3Notes#>
                        <cfset CheckListNotesArray[6] = #GetThisDay.Fees4Notes#>
                        <cfset CheckListNotesArray[7] = #GetThisDay.Fees5Notes#>
                        <cfset CheckListNotesArray[8] = #GetThisDay.Fees6Notes#>
                        <cfset CheckListNotesArray[9] = #GetThisDay.Fees7Notes#>
                        <cfset CheckListNotesArray[10] = #GetThisDay.Fees8Notes#>
                        <cfset CheckListNotesArray[11] = #GetThisDay.Fees9Notes#>
                        <cfset CheckListNotesArray[12] = #GetThisDay.Fees10Notes#>
                        <cfset CheckListNotesArray[13] = #GetThisDay.PartPaymentNotes1#>
                        <cfset CheckListNotesArray[14] = #GetThisDay.PartPaymentNotes2#>
                        <cfset CheckListNotesArray[15] = #GetThisDay.PartPaymentNotes3#>
                        <cfset CheckListNotesArray[16] = #GetThisDay.PartPaymentNotes4#>
                        <cfset CheckListNotesArray[17] = #GetThisDay.PartPaymentNotes5#>
                        <cfset CheckListNotesArray[18] = #GetThisDay.PartPaymentNotes6#>
                        <cfset CheckListNotesArray[19] = #GetThisDay.PartPaymentNotes7#>
                        <cfset CheckListNotesArray[20] = #GetThisDay.PartPaymentNotes8#>
                        <cfset CheckListNotesArray[21] = #GetThisDay.PartPaymentNotes9#>
                        <cfset CheckListNotesArray[22] = #GetThisDay.PartPaymentNotes10#>
                        <cfset CheckListNotesArray[23] = "">
                        
                        <cfset LoopCounter = 0>
                        <cfloop list="#CheckListNames#" index="i">
                              
                              <cfset LoopCounter = LoopCounter + 1>
                              
                              <!--- set value to be checked - the evaluate code will actually retrieve the data in GetThisDay.whatever (for example, GetThisDay.ValuationFees) --->
                              <cfset CheckValue = #evaluate("GetThisDay.#i#")#>
                              
                              <cfif CheckValue NEQ 0 AND CheckValue NEQ "">
                                    <cfif CheckListDatesArray[LoopCounter] NEQ "">
                                          <cfif day(CheckListDatesArray[LoopCounter]) eq d AND month(CheckListDatesArray[LoopCounter]) eq m AND year(CheckListDatesArray[LoopCounter]) eq GetYear>
                                          
                                                <!--- there is something in this fee so put details into array --->
                                                <!--- find what type of fee this is first --->
                                                <cfif FindNoCase("PartPayment", i)>
                                                      <!--- this is a partpayment --->
                                                      <cfset FeeType = 3>
                                                <cfelseif FindNoCase("PrincipalAdvance", i)>
                                                      <cfset FeeType = 1>
                                                <cfelse>
                                                      <cfset FeeType = 2>
                                                </cfif>
                                                
                                                <cfset ListDetails = ArrayNew(1)>
                                                <cfset ListDetails[1] = #GetThisDay.FullName#>
                                                <cfset ListDetails[2] = #GetThisDay.StaffName#>
                                                <cfset ListDetails[3] = #FeeType#>
                                                
                                                <cfset ListDetails[4] = #CheckListAmountsArray[LoopCounter]#>
                                                <cfset ListDetails[5] = #DateFormat(CheckListDatesArray[LoopCounter], 'dd/mm/yyyy')#>
                                                <cfset ListDetails[6] = #CheckListNotesArray[LoopCounter]#>
                                                
                                                <cfset tempVar = #ArrayAppend(BankArray[m][d], ListDetails)#>
                                                
                                          </cfif>
                                    </cfif>
                              </cfif>
                              
                        </cfloop>
                        
                  </cfoutput>
                  
             </cfloop>
      
      
</cfloop>

<cfset TheMonthName = monthAsString(m)>
<cfset RowDisplay = "RowOne">

      <table width="100%"  border="0" cellspacing="0" cellpadding="6">
      <tr>
            <td colspan="7" align="right" bgcolor="#C9FBC4"><strong><cfoutput>#TheMonthName# - #GetYear#</cfoutput></strong></td>
      </tr>
      <cfif IsDefined("BankArray")>
            <tr>
                  <td><strong>Advisor</strong></td>
                  <td><strong>Full Name</strong></td>
                  <td><strong>Entry Type</strong></td>
                  <td><strong>In</strong></td>
                  <td><strong>Out</strong></td>
                  <td align="right"><strong>Transaction Date</strong></td>
                  <td><strong>Notes</strong></td>
            </tr>
            <cfloop from="1" to ="#ArrayLen(BankArray[m])#" index="OutputOuterIndex">
                  <cfloop from="1" to ="#ArrayLen(BankArray[m][OutputOuterIndex])#" index="OutputInnerIndex">
                  <cfif IsArray(BankArray[m][OutputOuterIndex][OutputInnerIndex])>
                        <cfset TempInnerLoopArray = BankArray[m][OutputOuterIndex][OutputInnerIndex]>
                        <tr bgcolor="<cfif RowDisplay EQ "RowOne">white<cfset RowDisplay = "RowTwo"><cfelse>##E6FFE4<cfset RowDisplay = "RowOne"></cfif>">
                              <td>#TempInnerLoopArray[2]#</td>
                              <td>#TempInnerLoopArray[1]#</td>
                              <td>
                                    <cfswitch expression="#TempInnerLoopArray[3]#">
                                          <cfcase value="1">Principal Advance</cfcase>
                                          <cfcase value="2">Costs and Disb</cfcase>
                                          <cfcase value="3">Payment</cfcase>
                                    </cfswitch>
                              </td>
                              <td>
                                    <cfif #TempInnerLoopArray[3]# EQ 3>
                                          $#NumberFormat(TempInnerLoopArray[4], '9,999.99')#
                                          <cfset TotalBankIn = TotalBankIn + TempInnerLoopArray[4]>
                                    </cfif>
                              </td>
                              <td>
                                    <cfif #TempInnerLoopArray[3]# NEQ 3>
                                          $#NumberFormat(TempInnerLoopArray[4], '9,999.99')#
                                          <cfset TotalBankOut = TotalBankOut + TempInnerLoopArray[4]>
                                    </cfif>
                              </td>
                              <td align="right">#TempInnerLoopArray[5]#</td>
                              <td>#TempInnerLoopArray[6]#</td>
                        </tr>
                  </cfif>
               </cfloop>      
          </cfloop>
      <cfelse>
            <tr>
                  <td colspan="7">No Data Available</td>
            </tr>
      </cfif>

</table>

<!--- <cfdump var="#BankArray#"> --->


Does that help?

Thanks,
Brad
0
 
PluckaCommented:
bradderick,

Sorry not really.

What you can do is a <cfdump var="#now()#" />

after each section.

Then have a look at the page, to see all the times. This will help you establish which bit is the problem.

Regards
Plucka
0
 
ExpertAdminCommented:
I have not gone through the code you posted, but I wanted to point out that it is ALMOST NEVER quicker to loop through query results than to let the database do the filtering and/or ordering for you. If can figure out a way to structure your CFQUERY criteria to return the results already proccessed by the database, you are bound to get better results.

In a situation like this, I usually go into my database and create a query that will return the results I need, then go back to CF and call that query (for example, as a stroed procedure). This lets the database do what it is designed to do. Looping through results bypasses all of your db's ability to streamline execution.

Most of the time when I see code that is looping (especially when there are nested loops), it is an idication that the proper select syntax is not being used. Nearly all subloops can be accounted for on the database side by using JOIN syntax in your query.

Hope this helps.

M@
0
 
bradderickAuthor Commented:
Hi Guys,

Thanks for the comments. It looks like ExpertAdmin's comment is the most useful and it has helped me to speed the process up.

Much appreciated and sorry about the delay in getting back to you.

Cheers,
Brad
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now