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' joje@...
<mailto:joje@...> [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]