Search code examples
databasequery-optimizationfirebirddelete-row

How to optimize Firebird bulk delete with execute block

I have Firebird table with 60 milions rows and i need delete ca. half of table.

Table rows has gps position of cars, timestamp of record and other data. Table has primary key IdVehicle+TimeStamp and one foreign key (into Vehicle table). There is no other key or index or trigger. One vehicle has 100 000 - 500 000 records.

I need delete older data, eg. from all vehicles delete data older than 1 March 2015. I tried different ways and actually use my fastest comes with 'execute block' (use primary key). First I read for one vehicle records older then 1.3.2015. Then I am going through the individual records and prepare sql execute a block and then perform it into firebird for every 50 entries.

EXECUTE BLOCK AS BEGIN 
DELETE FROM RIDE_POS WHERE IdVehicle = 1547 and date = '4.5.2015 8:56:47'
DELETE FROM RIDE_POS WHERE IdVehicle = 1547 and date = '4.5.2015 8:56:59'
DELETE FROM RIDE_POS WHERE IdVehicle = 1547 and date = '4.5.2015 8:57:17'
...... a total of 50 line
END

Thus delete 1 million lines per 800 seconds (about 1 record for 1 ms).

Is there another quicker way to delete records?

Additionally, this way I can delete only a few million lines, and then I have to restart firebird, otherwise starts to slow down and jam (on the test server there is no other database / application). From early records cleared quickly and gradually takes longer and longer time.

For orientation, how quickly you erasing records routinely in large tables (not completely erase the table, but only a part of the record).

Solution

  • Finally, I found where the problem was. The main problem was that I am using the classic Winforms application (or IBExpert) and that causing jams and slowing query. I used to execute block and erases the data portions, which has solved the problem of jams, but it was slow.

    The solution was to create a simple console application and run query from it. I left primary key and erases through it (no adding or deleting indexes) and the speed of deleting the records was some 65 per milisecond (1 million rows per 16 second).

    When I tried to delete primary and add index on datetime column, than erasing speed up just little about 5-10%.