Home » RDBMS Server » Performance Tuning » Insert / Update Performance due to Triggers (merged 2) (Oracle 9i)
Insert / Update Performance due to Triggers (merged 2) [message #519182] Wed, 10 August 2011 04:14 Go to next message
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 #519187 is a reply to message #519182] Wed, 10 August 2011 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The triggers check that many fields are not null or are certain values

This should not be done in trigger but in declarative constraints.

Quote:
I can try to ensure that my data is sorted along the same lines as the index on the tab

If there are more than one index how do you achieve that?

Regards
Michel
Re: Insert / Update Performance due to Triggers (merged 2) [message #519190 is a reply to message #519187] Wed, 10 August 2011 04:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Wed, 10 August 2011 10:44
Quote:
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 #519211 is a reply to message #519190] Wed, 10 August 2011 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
OP did say they have no control over the triggers.

The comment was mostly not for OP but for futur readers.
I mean: "readers, take care, do not follow this it is a bad design".

And as you saw and said there is very little to do for OP's problem if he has no control on the (application) design.

Regards
Michel
Re: Insert / Update Performance due to Triggers (merged 2) [message #519226 is a reply to message #519211] Wed, 10 August 2011 08:04 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: how expensive is unique versus primary key in large table
Next Topic: performance doubt
Goto Forum:
  


Current Time: Thu Mar 28 21:18:27 CDT 2024