Performance Of theJOB

[g_rkrish]

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

[chulett]

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?

[kris_daredevil]

if you can post the queries we can think of writing the query in diff way so that it can processed quickly, if possible please post the design also.

[kcbland]

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.

[kumar_s]

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.

[gateleys]

Hashed files, Hashed files, Hashed files and Hashed files. Use them and .

gateleys