Svein Erling Tysvaer wrote:

> Lester Caine wrote:
>
>>Yep - now have 10 years worth of data on some sites, but they do not=20
>>want to archive yet :( The one I'm playing with today has 220,000=20
>>tickets with 600,000 transactions!
>
>
> Hey, that sounds like a scaled down version of my world. Multiply by 5
> and you get the number of years and number of records in the two tables
> I most often access. Though that still doesn't mean I create any similar
> reports...
>
> Just another thing that came to my mind. Have you considered adding a
> few fields (invinsible to the user) into your TICKET/TRANSACTIONS tables
> that you populate through triggers? I mean, an index on a TimeStamp
> column is pretty useless if your report conserns a particular time part
> or day of year (should we close down Christmas Eve, how many tickets
> have we sold on average Christmas Eve in the last 10 years?), whereas it
> would be simple to get this at the time of insert/update of each record
> and put it into separate fields which could have useful indexes. Though
> again, indexes usually aren't useful if you're interested in all records...

A few extra fields have grown over the years, and TRANSACTION table ONLY
gets populated via a trigger anyway. But I have a stored procedure in
progress that seems to be doing the job here, and then I can decide if
the PERFORMANCE table simply gets populated at the same time :) Disk
space is not a problem, and I'm not seeing any problem with Firebird
performance.

--
Lester Caine - G8HFL
-----------------------------
L.S.Caine Electronic Services - http://home.lsces.co.uk
Model Engineers Digital Workshop -
http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Treasurer - Firebird Foundation Inc. - http://www.firebirdsql.org/index.php