Database Triggers VS Procedures performance for specific column updates in HANA.

[Former Member]

hi all,

wanted to know that which method between triggers and procedures will be more suitable for below requirement so please share your views:

Requirement: To capture the specific column update or insert in a table(main table) into another table(log table).

Information to be captured is old value ,new value,username ,date and time of update or insert.

Important Note:The table is going to be huge table may have approx 10 to 20 million records after one year.

[former_member185132 · Accepted solution]

Hi,

Interesting question. I tried this in my system for insert statement. The Insert was on average about 25-30% faster using procedures instead of triggers

However, for update this was reversed. Triggers were dramatically (more than 70%) faster than procedures. It makes sense, because when a trigger is fired on an update, it knows the new values and the old values, so it can easily write them to a log. But when you use a proc to update a table, it only knows the new values. It must fetch the old values from the table, which means an additional select has to be fired. And that is an additional overhead which affects performance.

Regards,

Suhas

[Former Member · Accepted solution]

Hi Suhas,

thanks for your reply.

your observations are really helpful.

i would like you to also know that this performance comparison is because of my requirement which is as below:

1) update or insert on a table (say table 1) for few fields needs to be captured in another table (say table 2) along with info like old value,new value changed by etc. Also this should happen automatically(if using procedure method  then it should not be called manually).

2) the table is huge table may have approx 10 to 20 million records after one year.

so i wanted to know if this can be achieved  by procedures( procedure should not be called by trigger). if yes then which method will be better procedure or trigger?

thanks,

akash

[Former Member]

Hi all,

i wanted to use cursor inside the trigger but i am getting syntax error. i am not able to remove it.error is "AP DBTech JDBC: [257] (at 92): sql syntax error: incorrect syntax near "declare": line 3 col 1 (at pos 92) ".need urgent help.code is below:

CREATE TRIGGER "RGAJULA"."TRIGGER1" AFTER UPDATE ON "RGAJULA"."ZOTC_SETTLE2" FOR EACH ROW

declare CURSOR MYCUR FOR

SELECT "FIELDNAME" FROM "RGAJULA"."ZOTC_CHGLOG_OPTN" WHERE TABNAME = 'ZOTC_SETTLE';

BEGIN

OPEN MYCUR;

declare v_field varchar (30);

FOR cur_row as MYCUR DO

FETCH MYCUR INTO V_FIELD;

IF :new.V_FIELD <> :old.V_FIELD

THEN insert

into "RGAJULA"."ZOTC_CDPOS" (MANDT,

  ZSETTLEDOC,

  ZSETTLEITM,

  CHANGENR,

  FNAME,

  CHNGIND,

  VALUE_NEW,

  VALUE_OLD ) VALUES(:new.MANDT,

  :new.ZSETTLEDOC,

  :new.ZSETTLEITM,

  :V_ID,

  :V_FIELD,

  'I',

  :new.V_FIELD,

  ' ' ) ;

END IF ;

END FOR;

CLOSE MYCUR;

END;

[former_member185132]

The DECLARE CURSOR statement should come after the BEGIN statement.

[Former Member]

if i give BEGIN statement before the DECLARE CURSOR statement then error comes at OPEN CURSOR statement.

[Former Member]

Hi

As triggers are created in catalog and not in any package, so how can we transport it to different systems( dev.,quality and production) ?

do we have any other option than to create it manually in every system?

thanks,

Akash

[lbreddemann]

I am not aware of any option to directly transport and activate triggers.

You may however put the code into a text file and include it as a resource in the transport.

However, you have to activate it manually on the target systems.

Not quiet sure though why you don't just simply use the AUDIT feature to track your data changes.

[pfefferf]

Hello Akash,

we have/had the same issues regarding the transport of catalog objects. In our case virtual tables and not triggers, but the problem is the same.

We "solved" it in the following way to reduce/avoid the manual effort on the target systems:

1. The creation of the catalog objects (in our case virtual tables) is implemented in a stored procedure (hdbprocedure) which can be transported. The implementation makes, before the object is created, a check if the object already exists or changes were done. Only in case it does not exist or changes were done the object is created/recreated.
2. All our stored procedured creating catalog objects are called within a "central" stored procedure.
3. This "central" stored procedure is called on the target systems by an XSJOB (xscron). The definition of the job is also transported. On the target systems the job is scheduled to run every hour. With that (beside the scheduling of the xsjob) no manual activity is necessary anymore on the target systems, because the catalog objects are created by the scheduled stored procedure execution.

Best Regards,

Florian

[Former Member]

Thanks for your valuable reply..

i want to know 1 thing that how to change the schema names of the procedure as the schema name will b different in each system?

thanks,

akash

[pfefferf]

Hello Akash,

in my case I had not the requirement, because the virtual table were created in a defined schema which was the same on all systems.

But a different schema is also not a problem. Just add a parameter to your procedure which represents the schema. In the Job scheduler you can define the parameter value. Cause the job scheduling has to be done on every system, you can define the specific schema name in the system specific scheduling.

Best Regards,

Florian

[Former Member]

Hi Florian,

if you could send me some documents or related codes that can help me in my requirement then it will be very helpful for me.

thanks,

Akash

[pfefferf]

Hello Akash,

sorry for the late answer.

I created a little blog post for the approach I described before.

Maybe you can find some useful information in it.

Best Regards,

Florian

[Former Member]

Hi Florian Florian Pfeffer / ,

need your help here. my below query is returning 2 results. can you tell me what is wrong here.

for your info:

--the 2nd field is the new entry in the table.

--also if i add 1 more new entry then my select is returning 3 values with bottom 2 values are the newly inserted ones.

--also on changing the offset value the  top 1 field changes and other remains the same.

--1 entry in select comes only when the offset is 0 or equal to (N-1).

After adding 1 more entry.

[Former Member]

-- need suggestion.

thanks,

Akash

[lbreddemann]

Alright then...

how about you implement test versions of both approaches for your scenario and measure the result?

Everyone can come up with plenty of awesome ideas why the one or the other approach might be performing better.

Talking about "better" you haven't even defined what the comparison should be based upon.

INSERT/UPDATE statement runtime?

Space consumption?

Multi-transaction handling?

Query-performance?

You might also look into using AUDITING here...

[Former Member]

Hi Lars,

thanks for your reply.

i am still in process of creating procedure for this so will be definitely measuring the results.

also as the table on which i am going to use the trigger may have approx 10 to 20 million records after one year, so i feel comparison between them should be mainly based on :

1)INSERT/UPDATE statement runtime -- As multiple programs will be updating or inserting records in that table.

2)Multi-transaction handling

correct me if wrong.

thanks,

Akash

[former_member226419]

Hi ,

I think its already discussed, You can achieve with the help of triggers.

I will try to replicate in my own system with large no of records.

Can you please tell me how your base table looks like?

Br

Sumeet