I'm redesigning the website and DB at work. Here's how it works.

I changed the DB so all the data is in one table, instead of 2-3 previously. I also changed the unique id to a no unique ids. Now the ids are the gid combined with the year field. This will give you 1 row. This way any table can be updated or reloaded from scratch without any unique id issues.

The problem is when attempting to generate of the results table. There's 26 different substances. Here's a sample: CO2, N2O, CH4, HFC, PFC, SF6. HFC and PFC make up of many sub-substances.

Show Substance:
Identify which substance to display (if a selection was made).

Convert to CO2:
Convert each substance to co2. In my Substances table I have a gwp field which is the multiplier to convert to co2.

HFC and PFC
I need to add all sub-substances to get PFC and then HFC. There is a field in the Substances table that holds the category in which a pollutant is (pfc, hfc, total).

Calculate row total:
calculate the total of a row of substance (n2o + co2 + pfc...)

Calculate grand total:
calculate the total of a column (co2 + co2...)

I got most of this done. The way I did it was with a dev array which is a 3 dimension array that holds the substance name, gwp multiplier, raw data, data converted to co2, show sub (true/false). I then create a 2d array that mirrors an excel table. All I need to do at this point is calculate the row and grand total and then display. I got all this done but the load time is pretty high. When attempting to load 350 rows it hangs there for way too long. Less than 20 rows is fine. To display the data I create about 8 arrays, use a cfcomponent, use about 10 functions., many loops.

I'm sure there is a better way of building this. I built this system using object oriented programming.

Would using a structure instead of 8 array help or maybe 1 huge array?

Its seems a pretty complex collection of information. My first question would be, how often does this information change? If rarely, then I'd consider putting your arrays and structures in the application scope. I've done this recently with an XML based online course free trial application. Performance is very good.

So I'll leave it with that question first, then explore the details once I know more about the data turnover.

Caching right?

How would it work if the data displayed is always different? I currently have 4 years available (2004-2007). In total I have about 1500 rows of data. Are you asking me to cache the whole 1500 rows and then only display the required rows depending on the search entries? How would that work? The first time it loads before caching will take ages.

I haven't yet tried caching but I'm curious to see if putting the query inside a structure instead of looping the query many many times.

How do I go about implementing caching?

I tried putting <cfcache action="cache" /> as the first line on my results.cfm page. The performance doesn't seem to be improved and no .tpm files seems to be created.