19) GEN, CA GEN, CoolGen testing related best practice

During stress testing, very active tables and its associated children undergo deadlocks and timeouts. (Note: dsg_sequence_no is the primary index which has running sequence numbers as its value. The primary index was on the descending sequence to avoid a sort when getting the maximum dsg_sequence_no)

Best Practice:  

The following three design changes to the application:

1.     Create a primary index with dsg_sequence_no in ascending sequence

2.     Define the subpage parameter as 1 rather than 16

3.     Introduce a dummy column at the end of the table as X(250). Drop the primary index and recreate it with dsg_sequence_no and the dummy column.

The advantage of these suggestions are:

1.     Ascending sequence will avoid unnecessary reordering of the index whenever new records are created.

2.     Redefinition of Subpage to 1 will avoid subpage splitting altogether during creation

3.     Making the key size to be approximately 250 bytes ensures only less number of entries are available in each page. This will lessen the number of deadlocks/timeouts. Further all the 3 changes require no change to the application