#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0

    Truncate table via stored procedure


    Hi All,
    I need to execute fast delete of rows (1.000.000 rows) of tables. Simple
    Code:
    delete from <table>
    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Why can you not use TRUNCATE?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by clivew
    Why can you not use TRUNCATE?
    Because TRUNCATE does not exists in Firebird 2.5. There is another version with TRUNCATE statement?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    6
    Try copy the records needed for one table TMP, drop the table and re-create it, and, copy the records tmp
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by emailx45
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    6

    Thumbs up


    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
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    Good idea. How to do this? Sorry, I'm a n00b. Could you give me an example? Thanks
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    6
    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
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    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.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    6

    Lightbulb


    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


IMN logo majestic logo threadwatch logo seochat tools logo