I am trying to redesign some of our data tables now that we
are moving an application over to Flex. Cuurently Flex/Coldfusion
runs too slow if it has to deal with our raw data. So I am bouncing
around some ideas.
The Flex application is a dashboard. On the dashboard is a
column chart, a pie chart, a data grid, and some text details. The
dashboard is currently linked to tables that can range from 2000 -
100,000 records (each table is accessed separately).
The column chart show aggregate (usually a max. sum during a
given time period) data from the tables. The pie chart is a
demographic of the users of the data in the column chart. The data
grid will show the details (actual line from the tables) of a
selected column from the column chart.
I want the application to run quickly and currently if I have
CF crunch the numbers for the charts while the app is loading the
process can take up to 3 minutes. By the way the data is flat, not
dynamic. So currently I was thinking of creating a table that would
hold the aggregate data. Since the data never changes once it
happens there is never a need to update it from the application.
Each line in that table would represent one column in the column
In addition to this aggregate data table I would create an
intermediate table that would link the PK from the aggragate data
table to the PKs of the raw data table. This would them provide a
link back to the raw lines needed for the datagrid.
We have a system similar to this, also a dashboard. We just
have SQL generate some XML files, which sit in the folder with the
Flex .SWF file, Flex loads these XML files as HTTPService. There is
no direct link between Flex and SQL, but as in your case the data
for these is something which isn't really dynamic so it works out
(the data is generated one per day).
We have another system which queries data from a platform
called Magic eDev which is dynamic.