job performance

samsuf2002 · Post by **samsuf2002** » Thu Oct 12, 2006 3:45 pm

Hi all , i am running a parallel job as
[seq----transformer----sqlserver]

i have divided the input file data as 3 million / run (total rows 12 million in original file) though each file has same meta data , every time i run the performance get reduce like
1st runs statistics is 5000 rows/sec
2nd run--------------1000 rows/ sec
3rd run --------------500 rows/sec
4th run --------------250 rows/sec

i dnt knw y the performance is getting reduce for the successive runs can any one plz let me knw why this happens and how can we increase the performance.

Thanks in advance
[/code]

kris007 · Post by **kris007** » Thu Oct 12, 2006 4:01 pm

How did you divide the input file into 3millionrows/run? Did you use any constraint in the Transformer?

ray.wurlod · Post by **ray.wurlod** » Thu Oct 12, 2006 4:55 pm

The larger the SQL Server table, the longer updates take. Use the bulk loader to append new rows. The problem is not in DataStage.

samsuf2002 · Post by **samsuf2002** » Fri Oct 13, 2006 8:22 am

Kris i am using sample stage to split the file in a seperate job.

Thanks Ray for ur reply since i am using SQL server i dont think we have any bulk load stage for SQL server and i am doing initial load so i am loading the data in empty table can u plz suggest some more.

thanks

ArndW · Post by **ArndW** » Fri Oct 13, 2006 8:26 am

Can you partition your SQL server table to reduce the slowdowns as the table grows?

chulett · Post by **chulett** » Fri Oct 13, 2006 8:45 am

Can you partition SQL Server tables?

ArndW · Post by **ArndW** » Fri Oct 13, 2006 9:23 am

Craig - I wasn't sure until I checked it on Google, and MS marketing blurb said that you can; so it must be true.

kduke · Post by **kduke** » Fri Oct 13, 2006 10:20 am

Partitioning has changed a lot in 2005. It is starting to look more like Oracle and other databases. Before that is was ugly. It is sort ugly now in the way it picks a partition. Not real flexible.

samsuf2002 · Post by **samsuf2002** » Fri Oct 13, 2006 10:59 am

Can any one tell how exactly we can partition ,are we talking about partitioning in SQLserver stage like hash.... etc. help me understand

kduke · Post by **kduke** » Fri Oct 13, 2006 11:23 am

They are talking about SQL Server partitioning where you break one table up into to many tables but they still act as one. You usually want to break a table by either a reporting field or a date field or both. Some people want to partition by state or country for reporting purposes. Another way would be to have a partition for each month based on sales date or some date field. ETL can usually load into one partition therefore the load times increase because the one partition table is smaller than one huge table. It also helps in archiving because you can just remove one partition.