Query taking much time...

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
mallireddibalaji
Participant
Posts: 24
Joined: Wed Mar 19, 2008 1:13 am

Query taking much time...

Post by mallireddibalaji »

Hi Gurus,

My source database is Oracle and one of the source query executed in 1 min in TOAD(Database tool) and that same query i putted in source oracle stage (job level) then for the extraction of data is taking nearly 50 mins.


I am getting what may be the problem for this,

can any one provide the needful information on this



Thanks in Advance...
M.BALAJI
Govindarajan
Participant
Posts: 24
Joined: Mon Jul 12, 2004 10:16 am

Re: Query taking much time...

Post by Govindarajan »

In Toad, the system does not wait for fetching all rows. It bring only few rows to the answer window and while scrolling down the remaining rows are fetched. If you have millions of rows you may get the resultset with in seconds or few minutes, but it doesn't mean that all rows are fetched. So check the time consuming at TOAD for all rows and compare the same with DATASTAGE. On certain occasion if the job is PX, it may wait for minutes to get the available node for fetching the data.
nagarjuna
Premium Member
Premium Member
Posts: 533
Joined: Fri Jun 27, 2008 9:11 pm
Location: Chicago

Post by nagarjuna »

Which stage are you using ??

If you are using an API stage it will process the records sequentially .How many nodes are you using in configuration file ??
Nag
mallireddibalaji
Participant
Posts: 24
Joined: Wed Mar 19, 2008 1:13 am

Post by mallireddibalaji »

Hi All,


Thanks for you'r inputs.
M.BALAJI
njvijay
Premium Member
Premium Member
Posts: 1
Joined: Mon Jan 28, 2008 4:08 am

Post by njvijay »

mallireddibalaji wrote:Hi All,


Thanks for you'r inputs.
Did your problem resolved or not? Please let us know what did you do to improve performance
mallireddibalaji
Participant
Posts: 24
Joined: Wed Mar 19, 2008 1:13 am

Post by mallireddibalaji »

HI,

In query level, i used index hints and parallel hints, then that query fetching records fastely
M.BALAJI
Post Reply