TD Trigger takes much longer than executing the individual statements 1

[fatzlewinski]

We are puzzled as to why a trigger set FOR EACH STATEMENT takes longer than executing the individual SQLs by hand.

Our trigger reacts after a table receives inserts. The trigger is set FOR EACH STATEMENT. An insert select statement is fired from an applicaion but the statement seems to block up the whole DB. We exectued the same statement with the trigger turned off and it was finished in seconds.

We tried again and monitored with PMON and saw that the insert statement with trigger would take around 3 hours!

We tried to reproduce this error by executing the individual Trigger's SQLs one by one, by hand in Queryman. We were finished in minutes! We can see from tests that the jobs are quicker when the target table is empty than when it has rows. This came as no surprise because of indexes etc.

Any help appreciated (platform v2r5 on unix)

 

[tdatgod]

Open an incident with the Teradata GLobal support center.

Also Triggers were enhanced in teradata v2r5.1 to better support ROW LEVEL triggers as opposed to STATEMENT level, so you might want to consider upgrading.

 

[fatzlewinski]

An incident will be opened. We found a possible bug not with the trigger but with the TD Optimizer. We ran lots of scripts and checked the explains. When the target table was empty the execution plan was different to when the target was populated. (No statistics). The change in plans always occured at the point of a join condition. The left value required a cast from integer to varchar. We removed this and put the cast on the right value eg. varchar to integer. Voila, the explains were consistent!