Slow performance.
Moderators: chulett, rschirm, roy
Slow performance.
Hi,
In my job i used source and target stage as Dyanamic RDBMS.
I am using the query which is having UNION.
Initally the job is fetching 200 rows/sec and slowly it get worst to 2 row/sec.
Is this issues due to some hardware where the datastage is installed or Problem with the query itself.
Will any one give your input on the same.
In my job i used source and target stage as Dyanamic RDBMS.
I am using the query which is having UNION.
Initally the job is fetching 200 rows/sec and slowly it get worst to 2 row/sec.
Is this issues due to some hardware where the datastage is installed or Problem with the query itself.
Will any one give your input on the same.
Raji
Nothing is impossible because impossible itself
says I'm Possible
Nothing is impossible because impossible itself
says I'm Possible
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
As Craig put it, its gotta be a database write issue. Just to confirm it, replace the target db stage with a sequential file and check the fetch performance. If that is ok, then check if the identifying key is indexed for target updates, and for pure inserts if there are too many constraints, try disabling them to perform the inserts.
Last edited by gateleys on Mon Nov 19, 2007 9:39 am, edited 1 time in total.
gateleys
Put @FALSE in the output link constraint. When you run the job, you'll see the performance of the query and output rate to the DataStage process. It will probably be illuminating.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Hi All,
Thanks for the help.
The update method used is 'INSERT OR UPDATE'.
The table is indexed for the some of the columns and not to all the columns.
We tried using the sequential file which is very fast and we put the target table in which performance is down Eventhough there is no constraint for the inserting.
We run the runstats for the tables also. Eventhough it is very slow.
Please advise
Thanks for the help.
The update method used is 'INSERT OR UPDATE'.
The table is indexed for the some of the columns and not to all the columns.
We tried using the sequential file which is very fast and we put the target table in which performance is down Eventhough there is no constraint for the inserting.
We run the runstats for the tables also. Eventhough it is very slow.
Please advise
Raji
Nothing is impossible because impossible itself
says I'm Possible
Nothing is impossible because impossible itself
says I'm Possible
Hi All,
Thanks for the help.
The update method used is 'INSERT OR UPDATE'.
The table is indexed for the some of the columns and not to all the columns.
We tried using the sequential file which is very fast and we put the target table in which performance is down Eventhough there is no constraint for the inserting.
We run the runstats for the tables also. Then also it is very slow.
Please advise
Thanks for the help.
The update method used is 'INSERT OR UPDATE'.
The table is indexed for the some of the columns and not to all the columns.
We tried using the sequential file which is very fast and we put the target table in which performance is down Eventhough there is no constraint for the inserting.
We run the runstats for the tables also. Then also it is very slow.
Please advise
Raji
Nothing is impossible because impossible itself
says I'm Possible
Nothing is impossible because impossible itself
says I'm Possible
Hi All,
Thanks for the help.
The update method used is 'INSERT OR UPDATE'.
The table is indexed for the some of the columns and not to all the columns.
We tried using the sequential file which is very fast and we put the target table in which performance is down Eventhough there is no constraint for the inserting.
We run the runstats for the tables also. Then also it is very slow.
Please advise
Thanks for the help.
The update method used is 'INSERT OR UPDATE'.
The table is indexed for the some of the columns and not to all the columns.
We tried using the sequential file which is very fast and we put the target table in which performance is down Eventhough there is no constraint for the inserting.
We run the runstats for the tables also. Then also it is very slow.
Please advise
Raji
Nothing is impossible because impossible itself
says I'm Possible
Nothing is impossible because impossible itself
says I'm Possible
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
A better design is to segregate the inserts from the updates, and run them as separate input links (insert only and update only) into the target stage.
The "double-barrelled" update strategy will get slower over time; it has to try the insert then, if there's a failure, determine whether or not it was because of a primary key uniqueness constraint violation and, if so, to submit an update request.
The "double-barrelled" update strategy will get slower over time; it has to try the insert then, if there's a failure, determine whether or not it was because of a primary key uniqueness constraint violation and, if so, to submit an update request.
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.
'Some' of the columns can be ok as long as the columns you are using in your update statement are on the leading edge of the index - i.e. are the first columns in a compound index. Otherwise, it cannot be used and we're back to full table scans.raji wrote:The table is indexed for the some of the columns and not to all the columns.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers