I am having two extract jobs which are having same design and options.
But I find a huge difference from performance point of view for both jobs.
Job design is as follows:
DB2database ------> Hashfile (Use account name has enabled)
Job1 SQL:
SELECT LTRIM(RTRIM(Column1)),LTRIM(RTRIM(Column2)),Column3,Column4,Column5,Column6
FROM #Schema#.Table1
WHERE START_DT <= Processing Date
AND END_DT >= Processing Date
Result for Job1 is as follows:
Number of rows extracted: 180076
CPU seconds used:185.250
seconds elapsed:224.328
Job2 SQL:
SELECT Column1,LTRIM(RTRIM(Column2)),Column3
FROM #Schema#.Table2
WHERE START_DT <= Processing Date
AND END_DT >= Processing Date
Result for Job1 is as follows:
Number of rows extracted: 171420
CPU seconds used:723.300
seconds elapsed:746.108.
My concern here is we have mentioned all options same for both the jobs. But still job2 is taking more time to finish.
We don't have any before job sub-routine/after job sub routine for Job2.
Infact the number of records and columns extracted are less in Job2 when we compare with Job1.
What could be the reason why the second job is taking more time than the first one.
I have run the job three times today and the result is always same.
Can you give me some pointers to check why the second job taking more time.
Job performance
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 147
- Joined: Sat Apr 30, 2005 1:23 am
- Location: Bangalore,India
-
- Participant
- Posts: 147
- Joined: Sat Apr 30, 2005 1:23 am
- Location: Bangalore,India
Table1 and Table2 are different in your DataBase. Different numbers of records and perhaps you have different internal table setups / indices as well.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It would be very unusual for both jobs to take exactly the same amount of time. There are so many factors that can cause variation.
Is it the same hashed file? Loading an empty hashed file is quicker than loading additional records into a populated hashed file. Are you using write cache? Perhaps in one job and not in the other?
Is it the same hashed file? Loading an empty hashed file is quicker than loading additional records into a populated hashed file. Are you using write cache? Perhaps in one job and not in the other?
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.
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
As mentioned, it many not only within Datastage, you you may need to focus on Database side as well. It also depends on the two table. Are the both same. Are they both indexed in similar columns. Are they both analyzed recently. Are the query from each table taking same time.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 147
- Joined: Sat Apr 30, 2005 1:23 am
- Location: Bangalore,India