The brief for this project was simple - daily average prices from the country's Fresh produce markets had to be stored indefinitely to build up a history and be queryied with answers displayed in graphical format via a web-based interface.
Neither software or hardware acquisition was possible - there was no budget. The solution had to share space on an existing webserver until it had covered ROI (consulting fees) and could support itself.
A table was built and filled with 2 000 000 random records. After some initial tweaking of the table type and indexes used a query yielded a return in around 5 seconds. This included the failry complex grouping and where clause necessary to render a meaningful graph. This was deemed to be adequate and development proceeded.
Unfortunately one query wasn't sufficient to produce a graph that was sufficiently meaningful for the end user - at least 3 were required and had to run sequentially, so the turn-around-time was now 15 seconds (+ rendering time and transmission time - although these were neligible). - In fact it was determined that 3 was a bare minimum when during beta testing the need for even more was identified
The need for greater performance was obvious and at least limited funding was now available.
The system now runs from 2 tables, one mounted on a RAM drive (for queries only) and one on disk (to persist between reboots). The import software has been ammended to update both tables. The 5 second query time is now a fraction of a second.
Thanks must go to Andrew Ilgner, Dennis Bartlett and Ryan Kayser who have provided ad-hoc assistance on various phases of the project