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.
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)
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:
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?