Performance issue with ODBC stage

pxraja · Post by **pxraja** » Sat Oct 04, 2008 3:25 am

Hi All,

I am using custom query as the input to ODBC Stage and transforming to target ODBC after adding 2 more columns, one having defaulted to zero and other having sequence no. using KeyMgtGetNextValue(1)..

I.FIELD2 and I.FIELD15 are the primary columns in target

the custom query appears like...
SELECT I.FIELD1 ,
I.FIELD2,
P.FIELD3,
PS.FIELD4,
I.FIELD15,
I.FIELD6,
PC.FIELD7,
P_CLI.FIELD8
FROM TBL1 I ,
TBL2 A,
TBL3 PC,
TBL4 AR,
TBL5 P_CLI,
TBL6 P,
TBL7 PS
WHERE I.FIELD3 =A.FIELD2
AND PC.FIELD3 =A.FIELD3
AND AR.FIELD4=I.FIELD5
AND AR.FIELD7=118
AND P_CLI.FIELD8=AR.FIELD5
AND P.FIELD8=P_CLI.FIELD8
AND PS.FIELD9 =I.FIELD9
AND I.FIELD15 > SYSDATE - 10

transaction handling: rows per transaction: 0
parameter array size = 1

if i change this the job gets aborted. indicating unique constraint violated..

update logic is "insert rows without clearing"

the issue is the job is taking long time to run say approximately 40 mins.

the same custom query executed in 25 secs. in oracle 10g but while using in job. the rows transferred at 20 row/sec

also I am getting warnings like
SQLSTATE=23000, DBMS.CODE=1
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00001: unique constraint (RELODS.SYS_C0060909) violated

since the record has duplicate entries..

records count is just below 100,000

how to improve the performance? I want to reduce the run time any suggestions please?

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Sat Oct 04, 2008 3:33 am

If you write custom queries you're pretty much on your own. Why aren't you using generated query?

A SELECT statement can never generate UNIQUE constraint violations.

I suspect you're not managing your keys very well either. How many jobs call KeyMgtGetNextValue with an argument of 1?

An excellent way to reduce the reported run time is to set the system clock back while the job is running.

(... and when pxraja forks out for a premium membership that will be visible

)

pxraja · Post by **pxraja** » Sat Oct 04, 2008 3:49 am

ray.wurlod wrote:If you write custom queries you're pretty much on your own. Why aren't you using generated query?

A SELECT statement can never generate UNIQUE constraint violations.

I suspect you're not managi ...

Hi Ray,

Unique constraint violation is given when writing to the target ODBC.

I need to join 7 tables to get the desired output. Any suggestions on how to do this to improve the performance.

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Sat Oct 04, 2008 4:32 am

Had you had a premium membership you could have read the entirety of my previous reply. I proposed some ideas in that post.

How long does this query take to return all rows (note: NOT to return the first row) using some other client, such as TOAD? Why do you think DataStage would be any different? You probably need to tune the query itself or the database being queried.

Are all the joins supported by appropriate indexes? Take a look at the execution plan that Oracle intends to use, noting in particular any use of "table scan" - these are always relatively costly. Your custom query is not doing anything that could not be done in generated SQL, but you would generated identical SQL. Talk with your DBA about hints that you might be able to incorporate in the SQL.