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