Hemant.Sapre@... wrote:

>
> We are facing some issues with the release of database pages after
> delete all records in the table.
>

Dropping the table is much more efficient than deleting all the
records in it. Even more efficient, generally, is finding a way
to use selectable stored procedures and other Firebird capabilities
to avoid the need for temporary tables. That is, of course,
incompatible with writing applications that run on multiple
databases.

Dropping at table requires that the transaction dropping it have
exclusive access to the table, and that there have been no statements
executed against the table. So, you create the table, do your work,
disconnect, reconnect, and then drop the table. However, every time
you create a table, you use one of the 32,767 available table ids.
Since table ids can not be reused, you'll need to backup and restore
the database periodically.

An alternative, for applications that use temporary tables of the
same format - tables that have the same fields in different
transactions is to create a permanent table that holds the
temporary records. Put an identifier on the records - e.g. a
field that is automatically filled with the value of
CURRENT_TRANSACTION - and use a view to access the records of
significance to a particular transaction. Something like this

CREATE TABLE temps (trans_id integer, <other fields>);
set term ^;
CREATE TRIGGER SET_TEMPS_TRANS FOR temps BEFORE INSERT
AS BEGIN
NEW.trans_id = CURRENT_TRANSACTION;
END^
SET term ;^
CREATE VIEW MY_TEMPS <other fields>
AS SELECT (<other fields>) FROM temps
WHERE trans_id = CURRENT_TRANSACTION;


If you create indexes on the temps table, be sure to include
the trans_id fields.


Regards,


Ann