Truncate table via stored procedure

Hi All,
I need to execute fast delete of rows (1.000.000 rows) of tables. Simple took too long time to execute, maybe because Firebird checks some constraint.

Is there any way to do this? Maybe by using some statement that block checking constraint in a stored procedure?

Thanks in advance

Why can you not use TRUNCATE?

Because TRUNCATE does not exists in Firebird 2.5. There is another version with TRUNCATE statement?

Try copy the records needed for one table TMP, drop the table and re-create it, and, copy the records tmp

Thanks for the reply but it seems too complicated because I need to do this programmatically, also these tables has views, stored procedures: practically these are the base of my db but I need to clean.

Try DEACTIVE the INDEX on the TABLE!

With de index, all stay more slow!

After, delete, RE-ACTIVE the INDEX

note 1: PAY ATTENCION in PK, FK links for cascate action

Note 2: One BACKUP and Restore, help to free old records deleted and re-create de indexes

Good idea. How to do this? Sorry, I'm a n00b. Could you give me an example? Thanks

How to activate all indexes?
--------------------------------
http://www.firebirdfaq.org/faq274/

Run the following query:

SET TERM !! ;

EXECUTE BLOCK AS
DECLARE VARIABLE stmt VARCHAR(1000);
BEGIN
for select 'ALTER INDEX '||rdb$index_name ||' ACTIVE;'
from rdb$indices
where (rdb$system_flag is null or rdb$system_flag = 0)
order by rdb$foreign_key nulls first
into :stmt
do EXECUTE STATEMENT :stmt;
END!!

SET TERM ; !!

The ORDER BY is needed to make sure primary and unique keys are activated before foreign keys. The reason for this is that a foreign key cannot function unless there is a matching primary or unique key for the columns it references.

If you run Firebird 1.x which doesn't have EXECUTE BLOCK, you can run the following query:

select 'ALTER INDEX '||rdb$index_name ||' ACTIVE;'
from rdb$indices
where (rdb$system_flag is null or rdb$system_flag = 0)
and rdb$foreign_key is null
union all
select 'ALTER INDEX '||rdb$index_name ||' ACTIVE;'
from rdb$indices
where (rdb$system_flag is null or rdb$system_flag = 0)
and rdb$foreign_key is not null

then copy/paste the results as a new query and run them. If you want to do it from ISQL, there's detailed explanation in FAQ #1.

The query is separated into two parts for Firebird 1.0 compatibility, because Firebird 1.0 does not supports NULLS FIRST clause.

----------------------
Generate statements with SQL and use isql to execute


Examples:

-- execute arbitrary procedure without EXECUTE STATEMENT


SET HEADING OFF;
OUT tmp.sql;
select 'EXECUTE PROCEDURE '||proc_name||';' from your_table;
OUT;
INPUT tmp.sql;
COMMIT;


-- grant on all tables to some_user


SET HEADING OFF;
OUT tmp.sql;
select 'GRANT ALL ON '||rdb$relation_name||' TO some_user;'
from rdb$relations
where rdb$system_flag is null or rdb$system_flag = 0;
OUT;
INPUT tmp.sql;
COMMIT;


To grant to all procedures, use a similar approach, just query the RDB$PROCEDURES system table.

Of course, newer Firebird versions have EXECUTE STATEMENT which is more elegant way

Even if the indexes are inactive Firebird continues to work with them for the moment when you activate them again. I think that the only way to speed up this delete is to drop the indexes/foreign keys/uniques and recreate them back after you delete the records.
The bad thing about this approach is that you have to know their names, types (unique, foreign key etc), fields and you have to change your code after every change in the table - when you add or delete some indexes you have to change the code. But with the help of a temporary table and the RDB tables you can avoid that.
Create a table that stores information about the indexes - names of the indexes, their types + some additional data, fetch that data, drop the indexes, delete the records and after that use the data stored in the temporary table to recreate the indexes.

Look, you dont need make new tables for store the names of index, etc...

Look the TABLES SYSTEM in FIrebird ( RDB$....), normaly, they are HIDEN, but, you can access it.

Use the IBExpert to help.

REMEMBER: your task is big! SO, use one TRANSACTION where dont allow others users access the tables in question!

When you do one BACKUP and RESTORE, the records deleted, indexs, etc... are re-make it.

http://www.firebirdfaq.org/

Look at for you understand how to use Transactions in Devrace.com - Parts: 1, 2, 3 and 4

http://www.devrace.com/en/fibplus/articles/3286.php
http://www.devrace.com/en/fibplus/articles/3292.php
http://www.devrace.com/en/fibplus/articles/3305.php
http://www.devrace.com/en/fibplus/articles/3520.php