| Insert / Update Performance due to Triggers (merged 2) [message #519182] |
Wed, 10 August 2011 04:14  |
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
|
Member |
|
|
Hi,
I am looking at an existing utility which inserts data into configuration tables. The utility is fairly basic, you simply add the UPDATE / INSERT / DELETE sql commands to a .sql file, set up a few params in a .sh script in order to tell it which Database / Schema to run against and away it goes, doing some logging, etc on the way.
Most of the time this is fine. However there is one table that causes big performance problems. This large table holds rating data and it has two large triggers on it. It also gets updated quite a bit with new rating tariffs.
The triggers check that many fields are not null or are certain values... but they also check that dates of the rates do no overlap, etc. So, in short, they do a lot of work. I can see that these are the main performance obstacle. I have no ability to alter or disable these triggers, this is a core table supplied by the vendor and as such I cannot manipulate it.
So looking at the things I can change, what am I left with?... only the way I load the data..
I can consider using SQLloader in order to handle INSERTS or using the APPEND hint in order to perform a direct path insert rather than having individual INSERT statements.
I can try to ensure that my data is sorted along the same lines as the index on the table in order to ensure that I am updating the index nodes in as streamlined way as possible.
But after this, I am running out of idea's. Anyone know of any way I can improve performance still more, or even circumnavigate the drag of the triggers?
Thanks in advance,
Mike
|
|
|
|
|
|
| Re: Insert / Update Performance due to Triggers (merged 2) [message #519190 is a reply to message #519187] |
Wed, 10 August 2011 04:56  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 10 August 2011 10:44Quote:The triggers check that many fields are not null or are certain values
This should not be done in trigger but in declarative constraints.
OP did say they have no control over the triggers.
@mjm22 - I think you need to persuade the vendors to fix this. If the triggers are eating up most of the time nothing else is going to make much difference.
|
|
|
|
|
|
| Re: Insert / Update Performance due to Triggers (merged 2) [message #519226 is a reply to message #519211] |
Wed, 10 August 2011 08:04 |
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
|
Member |
|
|
Thanks guys, it IS bad design (and even worst, the table is replicated across multiple databases but not using Oracle Replication across DB links, each table instance on each DB must be updated individually). I suspect that the design is as a result of the history of the product, which didn't start out on Oracle.
No chance of getting the Vendors to do anything. All I can do is do some minor tweaks really as the triggers are the main problem. Just wondered if I had missed anything, you have pretty much confirmed I hadn't.
Thanks.
|
|
|
|
| Re: Insert / Update Performance due to Triggers (merged 2) [message #520823 is a reply to message #519182] |
Wed, 24 August 2011 20:41 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Last I knew, these were common limitations you must accept if you want to use triggers:
no parallel query
no bulk load
limited scalability
I personally am a fan of triggers because they do exactly the kinds of things you are talking about. If your thinking is correct, that this is a part of the product's evolution, then consider this: these triggers allowed the vendor to port their product to Oracle and thus to give you the option to use it. All in all this is a good thing isn't it? Indeed one can successfully defend the idea that this is an excellent example of how to use triggers affectively.
I realize that this does not help you at the moment. I simply point out that without triggers, the alternative would most likely have been, your company would not have had access to the product to use in the first place. So overall both the vendor and your company have benefited greatfully by the use of triggers.
Unfortunately, you are correct, a system with triggers is inherently limited to the amount of data it can process and there is little (at least that I know of) which can be done to change that.
One other thougt. This is a common issue. I have seen many third party specialty applications that worked great for a few years and then seemed to slow dramatically. If you decide to change anything, even somthing as simple as the ordering or inserts in a parameter file, you need to contact the vendor and discuss it with them. Otherwise you risk invalidating any support agreements you have and loose possible "remedies" that you might legally be entitled to if the system has a bug in it regardless of if the bug was related to your change or not.
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
