Geoff Worboys wrote:

>>I think I need to create a table with a set of records for
>>each time slot, where each record is a ticket active at that
>>time. Losing the date information in the process and giving
>>me something that can then be 'turned through 90o and
>>displayed as performance'.
>
>>Anybody playing with similar queries/reports - any ideas!
>
> I imagine that the separate timeslots table is considered
> necessary in order to make reporting performance acceptable.
> If that is the case then I have a similar(ish) problem with
> a high volume transaction table.

Yep - now have 10 years worth of data on some sites, but they do not
want to archive yet :( The one I'm playing with today has 220,000
tickets with 600,000 transactions!

> My solution at the moment is a sort of cache table (my report
> requirements are quite different to yours) but what I have
> been considering for a future version is an "index table"
> system rather than the duplication of data I am doing now.
>
> In your case I imagine each index entry would point to the
> ticket and transaction record applicable at that instance.

Now that is the lateral thinking I was looking for. I've got over one
hurdle already, and by processing each record in TICKET against the time
slots rather than trying to process each time slot - things are a lot
easier.

> It seems (from what I understand in your email) that the ideal
> would be to create the index table so that there is minimal
> duplication of data. Each index would mark ticket transition,
> rather than try to mark the state of all tickets at every
> interesting point in time. A join would provide a very direct
> access to the actual detail from the original data.

That already exists, it's just the primary key on the TRANSACTION table
( I think ;) )

> Lets say that your minimum report interval was 15 minutes:
> Without trying to think through the detail, I wonder if it may
> be possible to create an index on the "index table" (that has
> been created as a sort of transition log) that can report
> counts per interval by creating an indexed field that is
> generated from the timestamp based on 15 minute intervals.
> That is; if there is a field that increments with each 15
> minute interval then you have various group-by and distinct
> report possibilities that can be optimised by index.

That would be an approach except I may need to vary the time intervals,
from report to report. Otherwise building the index in 'real time' is
another option.

> The _hope_ being that you can restrict the rows actually
> accessed by a given report by explicitly directing such access
> through the index table.
>
> Just a thought, it may or may not help.

Just the sort of thought provoking ideas I was hoping for Geoff
and I already have a couple of temporary/permanent tables Set - just a
matter of working out HOW to populate them :)

--
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