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



--
'Probable impossibilities are to be preferred to improbable possibilities.'
Aristotle
""Ann W. Harrison"" <aharrison@...> wrote in message
news:5.1.0.14.2.20021001104248.053e8928@......

> At 12:34 PM 10/1/2002 +0100, Dave Bullar wrote:
> >I am using ibobjects with firebird.
> >I want to add a column to table B when a new record is added to Table A.

Via

> >a trigger and a stored proc.
>
> Generally that's a sign of carrying a workaround from
> database x to Firebird. Perhaps you could explain what
> you're trying to achieve...
>
> >I cannot do this using SQL syntax ('alter table B add Fred smallint')
> >because that is not available in stored proc. But I suppose I can alter

the

> >appropriate RDB$ files by stored proc.
>
> Yes you can, but you can't use the result. Metadata changes are not
> instantiated until the transaction commits. You can't commit from a
> stored procedure or trigger.
>
> In fact, all the limitations of commit/ddl in stored procedures and
> triggers is deliberate and architectural. Stored procedures and
> triggers affect data, they do not affect the operating environment.
> So, basically, what you're trying to do goes against the grain of
> the database.
>
>
>
> Regards,
>
> Ann
> www.ibphoenix.com
> We have answers.
>
>
>
>
> 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/
>
>
>