Hi All,
At last found the root cause for this issue basically it was database design issue where DBA used delete triggers in one of the tables let’s say ‘TableA’ that deletes it related rows from another table ‘TableB’. Another thing is are both are transaction tables containing large number of rows. TableA had 4,073 rows that where to be deleted, for each row deleted in TableA delete trigger was fired to TableB. This logic was ok until I found that foreign key ID used for linking ‘TableA’ with ‘TableB’ is varchar column. Then I tested the time duration taken for fetching results on TableB 1. Queried on integer column of TableB -> Got result in 1ms 2. Queried on varchar column of TableB -> Got result in 2secs. For testing I removed delete trigger from TableA, called delete stored procedure, got result in 16ms as compared to 30-40mins. That’s a dramatic improvement. Since, this DB is already live I am planning to remove this trigger and create stored procedures that will delete from TableB. Earlier, the DB that I tested this same didn’t had any rows in TableA. Hence, deletion was working fast. Thanks for your help. With regards, Joje T. George From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 19 April 2017 10:18 To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins Hi Karol, I will look into how the delete query plan works on customer environment and will revert back. Thanks From: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> [mailto:firebird-support@yahoogroups.com] Sent: 18 April 2017 20: 16 To: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> Subject: Odp: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins Hi, For me 5 minutes looks also very long. Look how delete query plan looks like Regards, Karol Bieniaszewski ----- Reply message ----- Od: "'Joje' j...@codework-solutions.com <mailto:j...@codework-solutions.com> [firebird-support]" <firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> > Do: <firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> > Temat: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins Data: wt., kwi 18, 2017 08:17 Hello, Today one of our customer complained that removing clients from application is taking a lot of time around 30-40mins. So I looked into their database whose DB size is around 340MB and found that our DBA has a called deletion triggers in main table. Triggers will delete all the client information from related tables that 6 tables. Also, I checked the child tables from any additional triggers but there were none. Now, when I copied this database to my development environment deletion works fast takes around 5 mins. I also rechecked with their older databases also whose size is around 1GB. Found no slowness during deletion. The question arises why this deletion process is taking so much of time at customer environment ? /o> Another thing I noticed was that FBServer was consuming 25% CPU usage during deletion. Thanks in advance. With Regards, Joje [Non-text portions of this message have been removed]
