RE: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins

['Joje' j...@codework-solutions.com [firebird-support]]

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] 
Sent: 18 April 2017 20: 16
To: 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
  [firebird-support]"

>
Do:  >
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]

RE: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins

['Joje' j...@codework-solutions.com [firebird-support]]

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] 
Sent: 18 April 2017 20:16
To: 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 
  [firebird-support]" 
 >
Do:  
>
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 

RE: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins

['Joje' j...@codework-solutions.com [firebird-support]]

Hi Sean,

 

I will look into it and will revert-back with additional details. 

 

Thanks.

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 18 April 2017 20:13
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Deletion of rows from multiple tables takes
a lot of time around 30mins

 

  



> 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 ?

Let's start with:

- Is the server also a Windows domain controller?
- Have you compared performance of customer/your storage using Crystal Disk
Mark?
- What FB engine (Classic, SuperServer) are you running?
- What FB version are you running?
- If SuperServer what is the db page cache size?
- What is the db page size?

> Another thing I noticed was that FBServer was consuming 25% CPU usage
> during deletion.

That would not be surprising.

Sean

RE: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> 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 ?

Let's start with:

- Is the server also a Windows domain controller?
- Have you compared performance of customer/your storage using Crystal Disk 
Mark?
- What FB engine (Classic, SuperServer) are you running?
- What FB version are you running?
- If SuperServer what is the db page cache size?
- What is the db page size?


> Another thing I noticed was that FBServer was consuming 25% CPU usage
> during deletion.

That would not be surprising.


Sean

RE: [firebird-support] Deletion of rows from multiple tables takes a lot of time around 30mins

['Joje' j...@codework-solutions.com [firebird-support]]

Hi martin, 

 

Thanks for help but firebird database is in Windows server 2008 OS.  

 

 

 

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 18 April 2017 16:54
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Deletion of rows from multiple tables takes a
lot of time around 30mins

 

  

Hello Joje,

 

please check the file system. We had a similar issue with btrfs on OpenSuse
several months ago. After switching to ext4 everything went fine. Maybe it
was a problem with the file system's journaling option.

 

Regards

Martin