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-
KeyMgtGetNextValue Performance Issue
Moderators: chulett, rschirm, roy
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.
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.
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.![Confused :?](./images/smilies/icon_confused.gif)
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.
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
There is a situation.
I'm creating line numbers for each new batch.
e.g.
I find KeyMgtGetNextValue as appropiate function
Chulett, Can you send me the modified version and I'll give a try and post my finding's.
Thanks in advance
K-
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)
Thanks in advance
K-
Is there any problem using hash file for generate the line number?shamshad wrote: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.
I hope that will give better performance
Please let me know the same batch no will come in different cycle?
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.kommven wrote:I'm creating line numbers for each new batch.
I find KeyMgtGetNextValue as appropiate function.
Nope. Besides the fact that you don't need it, my changes are specific to my environment and are not significant in this context.kommven wrote:Chulett, Can you send me the modified version and I'll give a try and post my finding's.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers