| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Insert queries on table having triggers on it , Using ESQL. |
« View previous topic :: View next topic » |
| Author |
Message
|
| santhoshMB |
 Posted: Sun Mar 11, 2012 10:00 pm Post subject: Insert queries on table having triggers on it , Using ESQL. |
 |
|
Newbie
Joined: 08 Mar 2012
Posts: 1
|
I have 2 tables(Master and Child) which have primary and foreign key relationship. A trigger is applied on the Master table which inserts data into some Back up table. Also before the delete is performed on the Master and Child table the foreighn key constraint is removed and then a delete is performed for both the tables and then a commit is done.This delete is performed as the first step to clear the dB.
When I am passing small input which insert 100-200 rows in the table the complete operation is done in 1 min. But when I pass a bulk input which inserts some 80,00 rows it takes 2 hours.
When the triggers is removed from the Master table same input when it is passed it inserts 80,000 records in 5 mins.
What can be the problem and how can I rectify it.
Thanks, |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Sun Mar 11, 2012 10:40 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
IMHO, this question may well get a better response from a DB Designer/Developer.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Mar 12, 2012 4:38 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| smdavies99 wrote: |
| IMHO, this question may well get a better response from a DB Designer/Developer. |
This sounds a lot like a database tuning / design issue rather than anythig WMB is doing.
You should cross post in a forum associated with your db software.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Mon Mar 12, 2012 5:36 am Post subject: Re: Insert queries on table having triggers on it , Using ES |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Delete/Insert smaller portions of your data (e.g. one parent row + all child rows). The performance-degradation likely comes from the "huge transactional context" that you create. Try to have a couple of small(er) transactions.
Have a look if you db-Schema has (or has not) "on delete cascade"... look how it works and if it can help you in splitting deletion into: Child-Table-rows first, then parent-table-row(s) ... well I am not sure ... but I think it works like "Delete parent rows will also trigger deletion of child rows"
What does the trigger do (I guess it triggers something performance intense) 
Your transactional context is too big ! Do more commits from time to time
Removing the foreign key(s)... look at "on delete cascade" instead: You should not change you db-design when you want to delete data.
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Mar 12, 2012 5:42 am Post subject: Re: Insert queries on table having triggers on it , Using ES |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqsiuser wrote: |
Delete/Insert smaller portions of your data (e.g. one parent row + all child rows). The performance-degradation likely comes from the "huge transactional context" that you create. Try to have a couple of small(er) transactions.
Have a look if you db-Schema has (or has not) "on delete cascade"... look how it works and if it can help you in splitting deletion into: Child-Table-rows first, then parent-table-row(s): E.g.: Delete the records in child table, then delete the rows in the parent table.
What does the trigger do (I guess it triggers something performance intense)
Your transactional context is too big ! Do more commits from time to time
Removing the foreign key(s)... look at "on delete cascade" instead: You should not change you db-design when you want to delete data. |
Some or all of which very good suggestions may require the assistance of a qualified db person unless the OP has the knowledge & access to achieve this. Which seems unlikely given they posted the question. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
