Hi all,
I am receiving the 20 million source records.Using one to one mapping i am loading these source records directly into target.But its taking nearly 45 minutes to load.
How can i increase the performance in this case?
Pls do the needful.
Performance tuning for one to one jobs
Moderators: chulett, rschirm, roy
not enough information
Hi,
what is your target? Oracle, Informix, SqlServer...? are you using Plug in or ODBC?
in any case, one of the things you should check is the number of rows per transaction (I.E after how many rows the DS will send a commit request).
Good luck,
Keren
what is your target? Oracle, Informix, SqlServer...? are you using Plug in or ODBC?
in any case, one of the things you should check is the number of rows per transaction (I.E after how many rows the DS will send a commit request).
Good luck,
Keren
</a>Re: not enough information
My Source & Targets are:Oracle stage.I am using the transaction size as zero.If i change this to 100 0r 1000 what would be the impact to performance?
Thanks in advance
Thanks in advance
kerensho wrote:Hi,
what is your target? Oracle, Informix, SqlServer...? are you using Plug in or ODBC?
in any case, one of the things you should check is the number of rows per transaction (I.E after how many rows the DS will send a commit request).
Good luck,
Keren
We can't answer that, as there are too many factors. But why don't you try it in your system and get a definitive answer?
Oracle -> Oracle. Hmmm, if they are in the same instance/schema then it might be fastest to stay within Oracle and do the copy.
You can make the job multi-instance and add a parameter that limits the SELECT of the source to a subset of the total data, then call several instances in parallel, each getting a portion of the total work. This creates a parallel load which can be much faster.
If you are loading to Oracle you can use the bulk load capabilities of DataStage; but most likely you will be constrained by the speed of the read in that case. You should read up on the Oracle stage in the documentation, specifically the array size.
Oracle -> Oracle. Hmmm, if they are in the same instance/schema then it might be fastest to stay within Oracle and do the copy.
You can make the job multi-instance and add a parameter that limits the SELECT of the source to a subset of the total data, then call several instances in parallel, each getting a portion of the total work. This creates a parallel load which can be much faster.
If you are loading to Oracle you can use the bulk load capabilities of DataStage; but most likely you will be constrained by the speed of the read in that case. You should read up on the Oracle stage in the documentation, specifically the array size.
Number of rows
Hi,
Like Arnd said, there are too many factors to answer you. but just to make sure you fully understand transaction size: the number you put there is how many rows DS will write before doing a commit. in other words, all those rows will be written to Oracle Buffers in case it will need to Rollback. when you put "0" you tell DS to write everything in one transaction - in case of 20M rows, you are probably filling those buffers - you will see that the number of rows per seconds in DS statistics is going down as it progressing.
in short, you probably don't want to put 0 on such a big insert
Keren
Like Arnd said, there are too many factors to answer you. but just to make sure you fully understand transaction size: the number you put there is how many rows DS will write before doing a commit. in other words, all those rows will be written to Oracle Buffers in case it will need to Rollback. when you put "0" you tell DS to write everything in one transaction - in case of 20M rows, you are probably filling those buffers - you will see that the number of rows per seconds in DS statistics is going down as it progressing.
in short, you probably don't want to put 0 on such a big insert
Keren