The ETL application loaded around 3000 rows in 14 seconds in a Development
database while it took 2 hours to load in a UAT database.

UAT db is partitioned.
Dev db is not partitioned.

the application looks for existing rows in the table...if they already
exist then it updates otherwise inserts them.

The table is pretty large, around 6.5 million rows.
Due to RI, it is looking up into parent tables which possibly reside of
different nodes.

This issue has increased the batch window from 2 hrs to 4 hrs and this is
certainly not acceptable.
How can I get rid of this performance issue?

Cheers,
San.