Page 1 of 1
performance Isuue
Posted: Thu Mar 20, 2008 3:10 am
by mohandl
Hi all
I have different sources like SQL server table ,.CSV files and my target is SQL server.
Stage which i am using is ODBC stage.
While loading taget table is taking 28 rows/per second (i have to load 5 laks recods) it taking arround 5 hr to load.
Please suggest me Is any way is there to improve the performance.
Regards,
Ramu
Posted: Thu Mar 20, 2008 3:19 am
by ArndW
The first step is always to figure out which component is slowing the overall process down, i.e. which one is the "bottleneck".
Posted: Thu Mar 20, 2008 3:30 am
by mohandl
While loading from Hash file to Sql server table it is taking 28 rows/sec
Posted: Thu Mar 20, 2008 3:55 am
by hamzaqk
why are you loading data from the hashed file in the first place? and how many records are there in the hashed file ? how big is the overflow file ?
Posted: Thu Mar 20, 2008 4:40 am
by mohandl
This is the final stage in job and hash file having 5 laks records
Posted: Thu Mar 20, 2008 4:55 am
by ArndW
mohandl wrote:While loading from Hash file to Sql server table it is taking 28 rows/sec
That doesn't help narrow down the bottleneck. Replace the final hashed file stage with a sequential file that is located on /dev/null and re-run. Is the speed still around 28/rows per second? If yes, then you now know that the hashed file isn't slowing your job down.
Posted: Thu Mar 20, 2008 7:54 am
by ray.wurlod
Describe fully how you are loading SQL Server. Are you using insert-only, update-only, or one of the combinations. Are you using parameter arrays? How many rows are you sending per transaction? All these settings will affect how efficiently DataStage (ODBC stage) can communicate data into a SQL Server table.
It is no less important, however, to eliminate other possible bottlenecks from your consideration. So please describe your job design fully - what stage types you are using throughout, and how they are connected.