Page 1 of 1
job performance
Posted: Thu Oct 12, 2006 3:45 pm
by samsuf2002
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]
Posted: Thu Oct 12, 2006 4:01 pm
by kris007
How did you divide the input file into 3millionrows/run? Did you use any constraint in the Transformer?
Posted: Thu Oct 12, 2006 4:55 pm
by ray.wurlod
The larger the SQL Server table, the longer updates take. Use the bulk loader to append new rows. The problem is not in DataStage.
Posted: Fri Oct 13, 2006 8:22 am
by samsuf2002
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
Posted: Fri Oct 13, 2006 8:26 am
by ArndW
Can you partition your SQL server table to reduce the slowdowns as the table grows?
Posted: Fri Oct 13, 2006 8:45 am
by chulett
Can you partition SQL Server tables?
Posted: Fri Oct 13, 2006 9:23 am
by ArndW
Craig - I wasn't sure until I checked it on Google, and MS marketing blurb said that you can; so it must be true.
Posted: Fri Oct 13, 2006 10:20 am
by kduke
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.
Posted: Fri Oct 13, 2006 10:59 am
by samsuf2002
Can any one tell how exactly we can partition ,are we talking about partitioning in SQLserver stage like hash.... etc. help me understand
Posted: Fri Oct 13, 2006 11:23 am
by kduke
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.