Surrogate Key generation for multi-instance job

[Anjan Roy]

Hi -

In our project we are using a routine to generate the surrogate key. The routine basically stores the counter for every table in a hash file. For every new incoming row, we make a call to the routine to get the new counter. It seems to have been working fine so far.

We have encountered huge performance issues when we run a job in multiple instances. We have a job that runs in 8 instances. I understand that there are locking issues with the hash file that keeps the counter.

I would like to know more about the best practices around generating the surrogate key for a multi-instance job.

-Thanks in advance.
Anjan

[kcbland]

Read this post, I put logic for a routine that doesn't have the overhead issues you're talking about.

viewtopic.php?t=85366&highlight=assignskeyparallel

[Anjan Roy]

Thanks Ken for the suggestion and the code.

I guess we would need to carefully decide on the increment parameter as the number of rows to be processed would not be the same every night. Also, we have a 10 character limit on the field in the database.