PROBLEM
Audit triggers are killing the performance of my bulk update query, by inserting an old row and a new row on every update.
In this trigger the insert of old rows, for some reason, takes much more time than inserting the new rows.
TABLE
The audit table has a cluster index, 3 non cluster indexes and it got somethig like 35 Million records.
Cluster Index
GROUPID , USERID, IDENDITYCOLUMN
FACT
Old rows can be inserted anywhere in the Clustered index
New rows will be inserted in the bottom of the Clustered index
INVESTIGATION
What I tested was that Cache can improve performance by a lot in this operation, But I didn't figured out what exactly needs to be cached.
ASSUMPTIONS
I'm Assuming that clustered index is the most relevant index in an operation of insert.
I'm Assuming that by doing a query to the right pages of the clusted index, the audit performance can be greatly improved
ACTION
I have created a query to load all the audit rows related to the rows I'm going to insert on the operational table. By doing That, performance have improved by little bit more then 2 fold, but was not good enough.
WHY NOT GOOD ENOUGH?
I have made another test which had much greater performance, but I didn't figured out what exactly I needed to cache.
HOW WAS THAT TEST?
T01 - BACK AND FORTH TEST
I grabbed 1000 rows from operational table and updated them back and forth to see if I got a astonishing cache performance from audit table.
A) I have updated GROUPID of 1000 rows to value X (it took a while)
B) I have updated GROUPID of the same 1000 rows to value Y (it took a while)
C) I have updated GROUPID of the same 1000 rows to value X (astonishing cache performance)
D) I have updated GROUPID of the same 1000 rows to value Y (astonishing cache performance)
T02 - CHECK THE OBJECTS CACHED ON AUDIT TABLE, WHEN UPDATING OPERATIONAL TABLE
I then cleaned the audit cache,indexes cache and data cache, and performed T01 - A) again.
I turns out that Both Cluster index pages and Datapages where loaded, and in approximatly the same amount, and a residual amount of pages were loaded in other indexes.
T03 - CHECK OBJECTS CACHED ON AUDIT TABLE, WHEN RUNNING MY ARTIFICIAL CACHE LOAD QUERY
I then cleaned the audit cache and ran my query. It have only loaded approximately half of the pages compared with test T02.
WHAT LOGIC DID I APPLIED TO MY ARTIFICIAL CACHE LOAD QUERY?
I assumed if I query all the rows in audit table where GROUPID AND USERID exists in 1000 rows to update (in operational table), I would load to cache all the clustered index and data pages needed to have great performance from audit trigger.
Cluster Index
GROUPID , USERID, IDENDITYCOLUMN
However this didn't turned out true, as I had half of the pages loaded compared with test T02.
QUESTION What can I do to have the performance of test T01 - C) or D) in the first time? I can only think in pre cache the data/index pages but I'm not able to find out what exactly is missing.
If you guys have other suggestions to improve audit table triggers it's also valuable.
DATABASE
SYBASE 15.7
NOTE: This is a table that belongs to a specific product solution, which means that I cannot alter it as I wish. I have some constraints.
