Problem after Deleting huge no. of rows in table
Hello guys, i have problem with my database, it takes time to insert into my table containing 5,300,000 of rows, so idecided to delete by "DELETE FROM MYTABLE" ,then the problem arise,i can't open my table and i can't even backup by database because when gbak tries to write the data of mytable( table that i delete all the rows) it halts. Any help, or guide to tune-up my database, and how to do deleting all the rows of my table.
and how i can optimize my "insert" in huge table
Looks like you have corrupted your database somehow.
As you seem willing to remove all 5,300,000 rows, I suggest you use a DROP TABLE command and then re-create it rather than a DELETE FROM TABLE without a WHERE
Deleting 5,300,000 rows without a COMMIT until the end may be asking a lot.
I do not know why inserting would be a problem if your PK is reasonable unless there are
a lot of indexes and FKs to maintain.
My English is not so good to explain exactly what Firebird does with deleted records but it does some manipulation over them on first try to "read" them.
If you delete a record Firebird just mark it as old version or something. After that when it reads the data from the table and it found a record that is deleted and no longer used by any transaction Firebird perform sweep / mark the record as free space. Something like garbage collection. So when you delete a table and execute SELECT COUNT(*) FROM table after that it's very slow operation the first time because of that background garbage collection. Or when you do backup with garbage collection option - Firebird just clear the table from old records. And perhaps this is what slows you. 5M records are 5M records.
The only fast way to insert many records is to drop the indexes for that table, insert records and recreate the indexes for the table. Bu this is not an option in multi-user environment.
This is usable only if you close all other connections to the database, do the insert of the records and start the database again for the other users.
And you must DROP the indexes, not just disabling them, because even they are disabled Firebird do some background work with them when you manipulate the data in the table.
Last edited by mIRCata; May 26th, 2011 at 03:48 AM.