DSXchange

Hello all

I have searched the forum, but couldnt find an answer which solves my problem.

I have two test jobs like

OCI---XFM----File (server job) & OCI---XFM----Dataset (parallel job)
Running on a 2 node processor. Pulling 2 million records from oracle.
Server job ran at 8000 rows/sec and parallel at 12000 rows/sec.

In the next phase, i am feeding file and datasets to oracle table. JObs like

File----xfm---OCI(server) & Dataset --- XFM ---- OCI (Parallel).

Now server job is loading the table at 3000 rows/sec and parallel at 1000 rows/sec.

parallel job is performing slower than server job.
Table has no constraints, No Indexes. I have truncated the table before loading the table.
All the DS JObs are on the same server and accessign same tables.

How can i make sure parallel jobs performs better than Server. Am i missing any details in Parallel Job? Please advice

OCI is not a parallel stage, do you mean OE? What settings are you using for the Oracle side, both jobs? Particularly wondering what the PX 'Method' is.

chulett wrote:OCI is not a parallel stage, do you mean OE? What settings are you using for the Oracle side, both jobs? Particularly wondering what the PX 'Method' is.

Its OE.
On Server side i am using "Insert rows without clearing"
On Paralle Sie i am using write method "Upsert".In the updatesql clause i have written the INsert statement. (Jobs do plain inserts only).
I cannot use Write method as Load as inserts will be out of my control adn there are some not null constraints on the columns.

reachmexyz wrote:

chulett wrote:OCI is not a parallel stage, do you mean OE? What settings are you using for the Oracle side, both jobs? Particularly wondering what the PX 'Method' is.

Its OE.
On Server side i am using "Insert rows without clearing"
On Paralle Sie i am using write method "Upsert".In the updatesql clause i have written the INsert statement. (Jobs do plain inserts only).
I cannot use Write method as Load as inserts will be out of my control adn there are some not null constraints on the columns.

Please advice

Please be patient. Advice will come.

try by selecting 'insert arry size' option and provide value as 1000

I think this will help you deffinetly.

bollinenik wrote:try by selecting 'insert arry size' option and provide value as 1000

I think this will help you deffinetly.

But i am using "update only" as the write method. In the update sql, i have written the insert sql statement. As i am using "update only" as the write method, i cannot see "insert array size" option.

Why would you do an upsert if you have truncated the table? Do a load append.

If your data has potential duplicates, deal with them first, don't expect the Oracle inserts to handle them.