Thanks Raymond that has set my mind on another track. A table of counts is
exactly what I was doing. But it would have to have 500 different items on
each of 200 days. So I was thinking in terms of a matrix, but I suppose it
could be a linear table of 100000 records one for each item for each day
ahead.
I shall have to think further.
[By the way I never see my own messages in this group, only the replies, is
that normal ?]
Dave
--
'Probable impossibilities are to be preferred to improbable possibilities.'
Aristotle
"Raymond Kennington" <raymondk@...> wrote in message
news:3D9A3B9F.9E0118B3@......

> Dave Bullar wrote:
> >
> > Thanks Ann, Svein and Martin.
> >
> > Yes I am probably trying too hard to squeeze a quart into a pint pot.
> > I have a system which works but I am trying to speed it up !
> >
> > I have a system for 'reserving' items that are 'booked' ahead.
> > Every time a 'booking' arrives I place a small record into a table

called

> > 'brief' which has
> > columns (recordid, itemid, startdate, enddate)
> > eg this might be record no 20023 stockline 1352 from 12/1/03 to

23/1/03.

> >
> > to find out whether I can accept a booking for item of stockline1352

from

> > 9/1/03 to 25/1/03 I have to find the Maximum number already booked

during

> > this period and compare it with my stock holding.
> > The 'brief' table has at most about 12000 records.
> > To do this I do
> >
> > maxval = 0;
> > for thedate := required_start to required_end
> > Select count(*) from brief
> > where thedate >= brief.startdate
> > and thedate <= brief.enddate
> > and brief.stockline = :required_stockline
> > if count > maxval then maxval = count;
> >
> > Avail = stocklevel - maxval;
> > The above to be done for each item on a 'page' of say 100 items.
> > The customer wants to see the availbility of all items on a page over a
> > proposed date range.
> >
> > with 100 items to display the avaiability of
> > and say 14days to investigate
> > I have to do 1400 'select(count)' actions.
> >
> > These are notoriously slow. It takes two seconds sometimes and this is
> > considered too slow.
> >
> > So I think about other ways. One is to keep a table of 'availability'
> > This having a record for each stockline, and a column for each date for

say

> > 200 days ahead.
> > So the table looks rather like a spreadsheet !
> > Then when I take a booking, all the days concerned get the row/column
> > concerned incremented by 1.
> >
> > This puts a bigger burden at the time of entering the data (makng a

booking)

> > but should be faster at retrieving the data.
> >
> > I still need to find the MAX accross a range of dates = a max across a

range

> > of columns.
> > If I had a column for each item of stock and a record for each date it

would

> > then be possible to use the MAX sql function. I assume this would be

faster

> > (being implemented in assembler) than the method using Delphi which I

have

> > to use above.
> >
> > To get a column for each item of stock I should have to enter a new

column

> > as each item of stock is entered in the stock list. Hence my requirement

to

> > create a column in B as a result of entering a record in A !!
> >
> > OK it was just a thought.
> >
> > I have tried to do the first method using stored procs. But it seems to

be

> > slower then using an external cursor to find the max. Is it the case

that

> > stored procs do not use indexes ??
> >
> > Anyway thanks for your thoughts. I am struggling !!
>
> You could use triggers to update a table of counts.
>
> --
> Raymond Kennington
> Programming Solutions
> W2W Team B
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>