KeyMgtGetNextValue Performance Issue

[kommven]

We are facing a huge performance deviation when KeyMgtGetNextValue used.

Do anyone have any recommendation for remediation steps?

Stats:

3000 rows/sec without KeyMgtGetNextValue
150 rows/sec with KeyMgtGetNextValue

K-

[shamshad]

If the only use of that function is to get latest key value, before your job, run another job that gets max(PRIMARY_KEY) from Table and dump it in a file: Example: File with 2 columns

KeyColumn KeyValue
X 123456

123456 is the MAX value for that primary key. 'X' is a hardcoded column that will be the Key for the above file. Use the file as hash file "KeyColumn" being the key for hash file. In your actual job do a lookup using 'X' to the above file and get 123456 and then add + 1 and keep going tilll your job completes.

[chulett]

Said technique is only valid if your process is guaranteed to be the only process creating new records in the target while it runs.

We use a slightly modifed version of the Concurrent flavor of that routine and have no problems getting it it process multiple 1000 rows per second, so not really sure why it would be so slow for you - especially the non locking version.

[kommven]

There is a situation.

I'm creating line numbers for each new batch.


e.g.

Code: Select all

batch, linenumber
1201,1
1201,2
1201,3
1201,4
1202,1
1202,2
1202,3
1203,1
1203,2

I find KeyMgtGetNextValue as appropiate function

Code: Select all

KeyMgtGetNextValue(Link1.batch)

Chulett, Can you send me the modified version and I'll give a try and post my finding's.

Thanks in advance

K-

[raj158347]

If the only use of that function is to get latest key value, before your job, run another job that gets max(PRIMARY_KEY) from Table and dump it in a file: Example: File with 2 columns

KeyColumn KeyValue
X 123456

123456 is the MAX value for that primary key. 'X' is a hardcoded column that will be the Key for the above file. Use the file as hash file "KeyColumn" being the key for hash file. In your actual job do a lookup using 'X' to the above file and get 123456 and then add + 1 and keep going tilll your job completes.

Is there any problem using hash file for generate the line number?
I hope that will give better performance

Please let me know the same batch no will come in different cycle?

[chulett]

I'm creating line numbers for each new batch.
I find KeyMgtGetNextValue as appropiate function.

Details always help. Actually, that's a totally inappropriate use of the function, no wonder it's so slow. All you need is a couple of stage variables, one to detect the 'group change' when the batch number changes and the other to increment the line number. When the batch number changes, set line number to 1 else add 1 to it. Simple and quick.

Chulett, Can you send me the modified version and I'll give a try and post my finding's.

Nope. Besides the fact that you don't need it, my changes are specific to my environment and are not significant in this context.