Improving Optim Delete Performance By Looking Up Multiple Keys at Once

Optim delete performance can be enhanced through many different methods (turning off "compare whole row", locking tables, disabling triggers and constraints, ensuring the primary key is indexed, etc).  By far one of the biggest bangs for your Optim buck is asking the database a question one time (can you please find and delete n rows) but having n be a long list of primary key values, rather than just one value.  There are two ways to accomplish this multi-key lookup - Array Deletes and Forced Key Lookup.

Array Deletes

Array Delete processing (building an array of Primary Keys from the table being deleted and using that array as the WHERE clause for your delete statement) is a great technique for increasing the efficiency of your Optim deletes.  However, there are some caveats to be aware of when working with Array Delete processing in Optim.  The biggest is that Array Delete functionality only exists for DB2 LUW and Oracle!

• DB2 LUW - Optim will attempt to use Array Delete processing automatically with no intervention from the user, providing the following are all true:

1. The table (in both the source file and the database) has a unique primary key
2. Compare Row Contents is not selected
3. File Attachments are not being deleted

• Oracle - Optim will attempt to use Array Delete processing after the user turns on "Use Oracle Array Delete" in the Oracle submenu on the Database tab in Product Options, along with confirming that all of the same rules for DB2 LUW exist (Primary Keys, no Compare Row Contents, no File Attachments)

Oracle Settings inside Product Options

NB: If your site audits Delete processing, be aware that Array Delete may report rows as being successfully deleted that do not exist in the database and, therefore, were not actually deleted by the process.  (from Optim's help "About Delete Requests")

Forced Key Lookup

For SQL Server, Informix, and Sybase you use the other method for sending multiple key values - Forced Key Lookup Limits.  In a delete request, select the "Edit Strategy" choice on the Tools menu from the Delete Request Editor.  From the resulting Table Access Strategy screen, the Optim developer can tell Optim (on a table by table basis or for all using the Set All shortcut) how to lookup values in the table being deleted (Default, Force Optim to use a Key Lookup, or Force Optim to do a table scan).  For the Force Key Lookup option, the Optim developer can tell Optim how many key values to look up in each request to the database (up to 100) - going from the default of 1 to the maximum of 100 means 99 less database requests (and therefore 99 less chunks of transaction overhead) while still selecting 100 rows for deletion.  As with Array Delete processing, there are special requirements:

1. An index on the primary key is defined for the table
2. Compare Row Contents is not selected
3. There are no Row Level Archive Actions defined for the Delete Process (e.g. Before Delete of Row)
4. The table does not have any child tables

As we said at the beginning, there are myriad methods to improve Optim's delete processing performance but asking for multiple records at once is a great way to cut down on network traffic and database overhead ultimately increasing performance.  Why spend the time getting 1 cookie on 5 separate trips when you can just get 5 cookies in one trip and then not have to get off the couch again?  Of course, it still takes as long to eat each cookie (delete each row) but at least you have only had to incur the overhead of getting the cookies (asking for and finding the rows) once!

Cheers,

Matthew Simons

Practice Lead, Information Lifecycle Management