Reader Feedback and Insights: Identity Columns and Performance

Hey, Ted:

I looked forward to using identity columns to replace and simplify the code we use to generate surrogate keys. In our application, when assigning surrogate keys, performance is critical: The application may need to generate keys for thousands or millions of new entities in a batch. To speed things up, we can parallel this task, but this requires mechanisms to ensure each parallel task assigns a unique surrogate key value each time. To simplify the logic, I wanted to implement identity columns to replace our own code.

IBM did a great job of hiding any references to the VALUES IDENTITY_VAL_LOCAL() clause, which is crucial to their usefulness, but I did eventually stumble across this. I played around and found that everything works as it should, but then came the big test. I created a dummy (SQLRPGLE) program to insert 100,000 new rows into a table using Identity columns, retrieving the value assigned using the VALUES clause. I also did the same thing using our existing code. The result: The identity column option was more than four times slower! This may not be a problem when inserting a few rows here and there, but in our application this could turn a three-hour batch run into a five-hour run or longer. (Obviously, other stuff is happening, so the entire job will not quadruple in elapsed time.) My point is this: Evaluate all of these new enhancements carefully from all aspects before you commit to them. Sure, the code is much simpler, but no one is going to thank you for that when the batch window isn't big enough anymore.

--Alan

Sponsored By INFORM DECISIONS

ELIMINATE THE COSTS OF PRE-PRINTED FORMS, LABOR AND POSTAGE WITH inFORM Decisions iDocs™ 'Suite' iSeries based e-Forms, e-Checks, e-Mail, e-FAX, and Document Retrieval from the Web are available as individual modules or as a complete e-Document processing 'Suite'. Click to Download the Complete Suite or Individual Modules today www.inFORMDecisions.com or call (800) 858-5544