same problem
Moderators: chulett, rschirm, roy
same problem
Hey guys we had the same problem over here. The job ran for 14 hours but not yer yeilded nothing (usually it takes just 2 hours) can any one throw some light on it.
...Skill and confidence are an unconquered army
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Sorry guys for the confusion..actually I am new to this forum...i was confused with post newtopic/reply.
We have a job which actullay runs at 2500 rows per second....all of a shudden the row count has shuddenly dropped phenominally..now it is running at 9 rows per second which is not acceptable? statistics are updated after every run. What could be the posssible reason?
cam some one help me in this regard.
Thanks for the reply
Attili
We have a job which actullay runs at 2500 rows per second....all of a shudden the row count has shuddenly dropped phenominally..now it is running at 9 rows per second which is not acceptable? statistics are updated after every run. What could be the posssible reason?
cam some one help me in this regard.
Thanks for the reply
Attili
...Skill and confidence are an unconquered army
Ok... at least now we have a clue what this is about.
However, that's not nearly enough information for anyone to help you. Post as many details as you can: job design, data volumes, source and target, everything you can think of. Perhaps then someone can help.
However, that's not nearly enough information for anyone to help you. Post as many details as you can: job design, data volumes, source and target, everything you can think of. Perhaps then someone can help.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi Craig:
The jobs is designed for loading a peoplesoft table( Oracle DB). The desings is..client will send us a FLAT file and we have few lookups to compare data in the flat file with that of the lookup(CRC logic is been implemented to avoid duplicate values) then load it to the final table.
thats it...a simple design.
I think this info is enough.
Thanks and regards
Attili
The jobs is designed for loading a peoplesoft table( Oracle DB). The desings is..client will send us a FLAT file and we have few lookups to compare data in the flat file with that of the lookup(CRC logic is been implemented to avoid duplicate values) then load it to the final table.
thats it...a simple design.
I think this info is enough.
Thanks and regards
Attili
...Skill and confidence are an unconquered army
No, still not really enough... but we'll keep digging. And this:
Which part do you think has slowed down? Are your lookups done with hashed files or some other mechanism? Are you using the OCI stage to write to Oracle? What are your Array Size and Transaction Size settings? What update action are you using? Has your target table grown substantially since it used to run faster? Are your updates using an index? See, lots of little tidbits to know to be able to help you.
If you haven't tried this, make a copy of your job and hack parts off to see where the slowdown is. For example, pop off the target OCI stage and replace it with a Sequential File stage. If it speeds back up, then you know you have a database issue. Still slow? Lookups.
This concerns me, as CRC32 is not a mechanism to 'avoid duplicates' but for CDD or Change Data Detection. Be that as it may...attili wrote:(CRC logic is been implemented to avoid duplicate values)
Which part do you think has slowed down? Are your lookups done with hashed files or some other mechanism? Are you using the OCI stage to write to Oracle? What are your Array Size and Transaction Size settings? What update action are you using? Has your target table grown substantially since it used to run faster? Are your updates using an index? See, lots of little tidbits to know to be able to help you.
If you haven't tried this, make a copy of your job and hack parts off to see where the slowdown is. For example, pop off the target OCI stage and replace it with a Sequential File stage. If it speeds back up, then you know you have a database issue. Still slow? Lookups.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What has changed?attili wrote:We have a job which actullay runs at 2500 rows per second....all of a shudden the row count has shuddenly dropped phenominally..now it is running at 9 rows per second which is not acceptable? statistics are updated after every run. What could be the posssible reason?
"Nothing" is not the correct answer.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I know right, thats Ray's signature question. And i just love his initial reply, "Nothing is not the correct answer", really cool.
Coming back to the OP query, check if the table you are loading changed. Check with the DBA if he put any triggers on it. If a trigger fires on every insert, that tends to slow down the inserts. And if the record size is really huge, then you can expect a drastic change as you have seen. Please have a chat with your DBA.
Coming back to the OP query, check if the table you are loading changed. Check with the DBA if he put any triggers on it. If a trigger fires on every insert, that tends to slow down the inserts. And if the record size is really huge, then you can expect a drastic change as you have seen. Please have a chat with your DBA.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Sorry for the confusion in the Topic Heading...as I am new to forums ...I tried posting a reply and ended up in posting a new topic..and I assure this will not happen again.
Comming to the discussion...thanks alot to every in sahring your valuble suggesions.
Regards
Attili
Comming to the discussion...thanks alot to every in sahring your valuble suggesions.
Regards
Attili
...Skill and confidence are an unconquered army