performance Isuue

mohandl · Post by **mohandl** » Thu Mar 20, 2008 3:10 am

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

ArndW · Post by **ArndW** » Thu Mar 20, 2008 3:19 am

The first step is always to figure out which component is slowing the overall process down, i.e. which one is the "bottleneck".

mohandl · Post by **mohandl** » Thu Mar 20, 2008 3:30 am

While loading from Hash file to Sql server table it is taking 28 rows/sec

hamzaqk · Post by **hamzaqk** » Thu Mar 20, 2008 3:55 am

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 ?

mohandl · Post by **mohandl** » Thu Mar 20, 2008 4:40 am

This is the final stage in job and hash file having 5 laks records

ArndW · Post by **ArndW** » Thu Mar 20, 2008 4:55 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 20, 2008 7:54 am

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.