Hi,
I have a job in which i am doing a look up with 7-8 tables(ODBC stage) all the ODBC stage has a user defined query on its own.when try running this job it is taking hell lot time like an hour.but the source table has only 9000 records.I treid running the query in the database it take only 2 seconds.I tried setting the option of Repeated Read Increasing the array size in the target but it's of no use.
can any body help me out in tuning this job.
thanks,
RK
Performance Of theJOB
Moderators: chulett, rschirm, roy
Let's see... 9,000 source records... 2 seconds per query... 18,000 seconds... 300 minutes... 5 hours.
Why would you think a 7-8 table join lookup, accessed directly from a database with the ODBC stage, fired once for every row coming thru the job would ever be performant? Why not build a hashed file reference lookup from your two second query and use that?
![Shocked :shock:](./images/smilies/icon_eek.gif)
Why would you think a 7-8 table join lookup, accessed directly from a database with the ODBC stage, fired once for every row coming thru the job would ever be performant? Why not build a hashed file reference lookup from your two second query and use that?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 20
- Joined: Mon Dec 05, 2005 8:07 pm
- Contact:
You can't compare an intra-instance join query with a pl/sql script using cursors. A DS job with 8 OCI lookups is equivalent to a streaming query with 8 cursor lookups. It's not parallel, doesn't cache repeated lookup values, and doesn't use any of the technologies DS Server has built in it for performance.
Tuning OCI reference queries won't help.
Tuning OCI reference queries won't help.
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
Try to replace the database stages into sequential file. If you see the diference, pre populate the databse into a hash file and do a lookup. May be the query hit by the stages to the table for each records in a sparse lookup may lead to slow down of you job.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'